sql - 调整 SQL 选择

Question

我不太了解优化的 SQL Selects，而且我的查询速度很慢。也许您有一些提示可以加快我的查询速度。

SQL 查询

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'

那么，我的问题是:如何才能使上述查询更快？

此查询必须在 SQL Server 实例中运行。

提前致谢。

Answer 1

一些想法:

• 尽量不要使用DISTINCT，而是适本地限制您的数据。
• 尽量不要使用IN，例如IN('F')可以是='F'
• 阅读索引并为您正在查询/加入的列创建索引
• 阅读如何创建和阅读执行计划以找到瓶颈