Início / dba / Perguntas / 17747
Accepted
Cristian Lupascu
Asked: 2012-05-12 07:09:27 +0800 CST

Desempenho do TSQL - JOIN no valor BETWEEN min e max

  • 772

Tenho duas tabelas nas quais armazeno:

  • um intervalo de IP - tabela de pesquisa de país
  • uma lista de solicitações provenientes de diferentes IPs

Os IPs foram armazenados como bigints para melhorar o desempenho da pesquisa.

Esta é a estrutura da tabela:

create table [dbo].[ip2country](
    [begin_ip] [varchar](15) NOT NULL,
    [end_ip] [varchar](15) NOT NULL,
    [begin_num] [bigint] NOT NULL,
    [end_num] [bigint] NOT NULL,
    [IDCountry] [int] NULL,
    constraint [PK_ip2country] PRIMARY KEY CLUSTERED 
    (
        [begin_num] ASC,
        [end_num] ASC
    )
)

create table Request(
    Id int identity primary key, 
    [Date] datetime, 
    IP bigint, 
    CategoryId int
)

Desejo obter o detalhamento da solicitação por país, para isso realizo a seguinte consulta:

select 
    ic.IDCountry,
    count(r.Id) as CountryCount
from Request r
left join ip2country ic 
  on r.IP between ic.begin_num and ic.end_num
where r.CategoryId = 1
group by ic.IDCountry

Tenho muitos registros nas tabelas: cerca de 200.000 em IP2Countrye alguns milhões em Request, então a consulta demora um pouco.

Observando o plano de execução, a parte mais cara é uma busca de índice clusterizado no índice PK_IP2Country, que é executado várias vezes (o número de linhas na solicitação).

Além disso, algo que me parece um pouco estranho é a left join ip2country ic on r.IP between ic.begin_num and ic.end_numparte (não sei se existe uma maneira melhor de realizar a pesquisa).

A estrutura da tabela, alguns dados de exemplo e consulta estão disponíveis no SQLFiddle: http://www.sqlfiddle.com/#!3/a463e/3 (infelizmente acho que não consigo inserir muitos registros para reproduzir o problema, mas isso espero que dê uma ideia).

Eu (obviamente) não sou um especialista em desempenho/otimizações de SQL, então minha pergunta é: há alguma maneira óbvia de melhorar o desempenho dessa estrutura/consulta que estou perdendo?

sql-server-2008 t-sql

3 respostas

  1. Best Answer

    Você precisa de um índice adicional. No seu exemplo do Fiddle, adicionei:

    CREATE UNIQUE INDEX ix_IP ON Request(CategoryID, IP)

    Que cobre você para a tabela de solicitação e obtém uma busca de índice em vez de uma varredura de índice clusterizado.

    Veja como isso melhora e me avise. Acho que vai ajudar um pouco, já que a varredura nesse índice não é barata.

    • 3

  2. Há sempre a abordagem de força bruta: você pode explodir seu mapa de IP. Junte-se a uma tabela de números em seu mapa existente para criar um registro por endereço IP. São apenas 267 mil registros com base nos dados do Fiddle, sem nenhum problema.

    CREATE TABLE IPLookup
  (
  IP  BIGINT PRIMARY KEY,
  CountryID  INT
  )
INSERT INTO IPLookup (IP, CountryID)
  SELECT
    N.Number, Existing.IDCountry
  FROM
    ip2country AS Existing
    INNER JOIN Numbers AS N ON N.Number BETWEEN Existing.begin_num AND Existing.end_num

    Isso tornaria as buscas mais simples e, com sorte, mais rápidas. Isso só faz sentido se você fizer relativamente poucas atualizações no ip2country, é claro.

    Espero que alguém tenha uma solução melhor!

    • 2

  3. Tente isto:

    SELECT ic.IDCountry,
        COUNT(r.Id) AS CountryCount
FROM Request r
INNER JOIN (SELECT begin_num+NUMS.N [IP], IDCountry 
            FROM ip2country
            CROSS JOIN (SELECT TOP(SELECT ABS(MAX(end_num-begin_num)) FROM ip2country) ROW_NUMBER() OVER(ORDER BY sc.name)-1 [N]
                        FROM sys.columns sc) NUMS
            WHERE begin_num+NUMS.N <= end_num) ic
ON r.IP = ic.IP
WHERE r.CategoryId = 1
GROUP BY ic.IDCountry
    • 0

relate perguntas