Tuning SQL Select

Question

I don't know too much about optimized SQL Selects and my query is very slow. Maybe you have some hints that makes my query faster.

SQL Query

SELECT DISTINCT CLI.FANTASIA AS Cliente
            ,   DBSMP.VEICULO_PLACA AS Placa
            ,   DBSMP.DTINICIOPREV AS 'Data Inicio Previsto'
            ,   DBSMP.DTFIMPREV AS 'Data Fim Previsto'
            ,   DBSMP.DTINICIOREAL AS 'Data Incio Real'
            ,   DBSMP.DTFIMREAL AS 'Data Fim Real'
            ,   DBSMP.CIDADE_DES AS 'Cidade Destino'
            ,   DBSMP.CIDADE_ORI AS 'Cidade Origem'
            ,   TRA.FANTASIA AS Transportador
FROM DBSMP_WORK WORK
INNER JOIN DBSMP ON WORK.ID_SMP = DBSMP.ID_SMP
INNER JOIN DBCLIENTE CLI ON DBSMP.ID_CLIENTE = CLI.ID_CLIENTE
LEFT JOIN DBCLIENTE TRA ON DBSMP.ID_TRANSPORTADOR = CLI.ID_CLIENTE
WHERE WORK.[status] IN ('F')
    AND DBSMP.ID_CLIENTE IN (85, 107, 137, 139, 510, 658, 659, 661, 702)
    AND TRA.RAZAO = 'Google'
    AND DBSMP.DTINICIOPREV BETWEEN '01/01/1900' AND '02/09/2013'

Then, my question is: How can I make abovequery faster?

This query must to run in an instance of SQL Server.

Thanks in advance.

Answer 1

Just a few thoughts:

• try not to use DISTINCT, instead restrict your data appropriate.
• try not to use IN, for example IN('F') can be ='F'
• read about indices and create them for the columns which you are querying/joining
• read how to create and read execution plans to find the bottleneck

Answer 2

Make sure you have indices on the columns ID_SMP,ID_CLIENTE,ID_TRANSPORTATOR in their various tables. This will make the joining operations much faster. And then, to finish it off you can try indexing the columns STATUS, RAZAO and DTINICIOPREV in the WHEREclause too.

Answer 3

First, you do not need the final LEFT JOIN. You have the condition TRA.RAZAO = 'Google' in the WHERE clause. This effective turns the LEFT JOIN into an inner join.

The most important thing, though, is the join condition:

LEFT JOIN DBCLIENTE TRA ON DBSMP.ID_TRANSPORTADOR = CLI.ID_CLIENTE

In other words, your query does not make sense. You are joining in a table with no reference to the table. I am not sure what the right fix is, because you don't give enough information. My best guess is that this is what you mean for the from clause:

FROM DBSMP_WORK WORK 
INNER JOIN DBSMP ON WORK.ID_SMP = DBSMP.ID_SMP
INNER JOIN DBCLIENTE CLI ON DBSMP.ID_CLIENTE = CLI.ID_CLIENTE
LEFT JOIN DBCLIENTE TRA ON TRA.ID_TRANSPORTADOR = CLI.ID_CLIENTE

Answer 4

your M$ tooling should have a query analyzer/expain tool that shows you the joins and expressions and their "cost" - use this in an iterative process when improving your statement.

add indizes to columns that take part in a join clause or the where clause

try to remove all selected column and replace the with "1" (-> SELECT 1 FROM ..) - this will show you, if the data set is too big

try to remove the DISTINCT as it performs an expensive group by value

Answer 5

Try this one -

SELECT DISTINCT CLI.FANTASIA AS Cliente
            ,   DBSMP.VEICULO_PLACA AS Placa
            ,   DBSMP.DTINICIOPREV AS [Data Inicio Previsto]
            ,   DBSMP.DTFIMPREV AS [Data Fim Previsto]
            ,   DBSMP.DTINICIOREAL AS [Data Incio Real]
            ,   DBSMP.DTFIMREAL AS [Data Fim Real]
            ,   DBSMP.CIDADE_DES AS [Cidade Destino]
            ,   DBSMP.CIDADE_ORI AS [Cidade Origem]
            ,   TRA.FANTASIA AS Transportador
FROM (
    SELECT *
    FROM DBSMP
    WHERE DBSMP.DTINICIOPREV BETWEEN '19000101' AND '20130902'
        AND DBSMP.ID_CLIENTE IN (85, 107, 137, 139, 510, 658, 659, 661, 702)
) DBSMP
JOIN DBCLIENTE CLI ON DBSMP.ID_CLIENTE = CLI.ID_CLIENTE
JOIN DBCLIENTE TRA ON DBSMP.ID_TRANSPORTADOR = TRA.ID_CLIENTE -- or TRA.ID_TRANSPORTADOR = CLI.ID_CLIENTE
WHERE TRA.RAZAO = 'Google'
    AND EXISTS(
        SELECT 1 
        FROM DBSMP_WORK WORK
        WHERE WORK.ID_SMP = DBSMP.ID_SMP
            AND WORK.[status] = 'F'
    )