1. Este site usa cookies. Ao continuar a usar este site está a concordar com o nosso uso de cookies. Saber Mais.

Optimizando o CMD BETWEEN no SQL

Discussão em 'Programação' iniciada por fLaSh_CF, 11 de Outubro de 2008. (Respostas: 5; Visualizações: 762)

  1. fLaSh_CF

    fLaSh_CF Banido

    Boas;

    Estou com uma duvida em relação ao comando BETWEEN..
    Preciso de obter o Tops Contries de uma BD em SQLite..
    Fiz o query da seguinte forma e functiona.. (CODE)
    O problema é que está um pouco lento, precisava de optimizar o query..

    Já experimentei desta forma, mas não funciona.. (a condição..)
    Se alguem poder ajudar ..
    Código:
    SELECT DISTINCT country, COUNT(country) AS TOTAL 
    FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id 
    WHERE (1486149509 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (2537819614 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (701684425 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (2130706433 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1495497275 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1437975106 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (3575017176 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1356762882 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1310809035 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1436948856 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1265441955 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1440990995 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1404512164 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1413143436 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1217187988 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1491831804 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (3362243165 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1364484315 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1351251084 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1446856464 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1414245739 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1332754719 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1334197131 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1333236289 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1393808330 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1295438787 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1502508649 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1318873170 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1545949891 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1314528380 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1474280303 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (2439230330 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1314174166 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1333070193 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (3273849874 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1589488396 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (3582727755 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1415218381 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1478288548 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1364503911 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1414262933 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1332905661 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1333011343 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1330877193 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1435199056 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1472464343 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1333070716 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1460131478 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1549004602 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (2197024396 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1144987038 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1385839702 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (3653734094 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1332938430 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1332931591 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1449725108 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    OR (1385922226 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
    GROUP BY country 
    ORDER BY TOTAL 
    
    Assim funciona, mas demora cerca de 1 minuto o query ..

    A ideia é apresentar os resultados da seguinte forma:
    Notas:
    *A base de dados tem cerca de 3,2 milhões de IP ranges, eu penso que se o query for bem optimizado fica muito mais rapido..
    *Um query unico demora cerca de 0.5~2 segundos..
    Ex:
    Código:
    SELECT SELECT country, region, city, postal_code, latitude, longitude, dma_code, area_code 
    FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id 
    WHERE 1401213123 BETWEEN blocks.start_ip_num AND blocks.end_ip_num
    LIMIT 1
    Table Schema (2 tabelas):
    Compr.
     
    Última edição: 11 de Outubro de 2008
  2. KiKas

    KiKas Power Member

    podes começar por tirar o distinct (SELECT DISTINCT country, COUNT(country) AS TOTAL ) visto estares a agrupar por país não tem sentido estares a colocar essa instrução.

    depois coloca um indice no star_ip_num e no end_ip_num.
     
  3. reise

    reise Power Member

    EU não percebo de SQLite mas em Oracle provavelmente ficaria mais rápido usando partições por blocos de ips com indices locais. Se der experimenta.
    Outra coisa que reparei é que não tens indices nas tabelas. O SQLite não suporta ou esqueceste-te?
     
  4. fLaSh_CF

    fLaSh_CF Banido

    Obrigado por os comentarios..

    Retirei o DISTINCT mas mesmo assim o tempo de execução continua o mesmo (40 ~ 60 segundos)..
    Quanto ao indice , podes ser mais concreto, que tipo de indice (ordenar) ?
    Criei um indice de ordenação mas também não reparei em nenhuma melhoria ..

    SQLite suporta indexes, tiggers e view tal como qualquer outra linguagem SQL :)

    Continuo com o mesmo problema, mais alguma sugestão ?
     
  5. ld50

    ld50 Power Member

    Às vezes há coisas que são melhor feitas fora do SQL.

    1º Primeiro faria a junção:
    Código:
    SELECT country, start_ip, end_ip
    FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id;
    
    para ter a lista:

    Código:
    País           Start_ip End_ip
    Portual          1           10
    Portugal       11           20
    Italia           20           30
    
    2º Depois com uma linguagem de programação que estás a usar iteras o resultado.

    Código:
    
    for i in lista_de_ips
    loop
        for j in resultado_da_query
        loop
        
            if i between j.start_ip and j.end_ip
            then
                countries[j.country] ++;
                break;
            end if;
        end loop;
    end loop;
    
    3º O problema do passo 2 é que tens uma complexidade linear, se no passo 1 a lista já vier ordenada por start_ip, já consgues uma complexidade O(log n). Podes começar a avaliar pelo meio da lista de resultado, se o ip for menor que o start_ip procuras no meio do meio, etc

    estilo uma procura binaria


    O único problema é que no passo 1 estás a puxar tudo para memória :D
     
    Última edição: 15 de Outubro de 2008
  6. fLaSh_CF

    fLaSh_CF Banido

    hum .. obrigado ld50

    Vou tentar da forma como diz .. a ver se resulta ..
     

Partilhar esta Página