可以使用数千个数据更快地运行查询（SQL） [英] Possible to run a query faster with thousands of data (SQL)

问题描述

我有5个表来获取我的数据。目前，它运行8-10分钟。它可以加快一点吗？见下面我的代码。不完全是我的表的名称，但我应用相同的逻辑。我得到了正确的结果，我只需要让它更快，如果可能的话。

请询问您是否需要更多信息。

I have 5 tables to get my data. Currently, it runs at 8-10 minutes. Can it process a bit faster? See below my code. Not exactly the names of my tables but I apply the same logic. I got the correct results, I just need to make it faster, if possible.
Please ask if you need more information.

select 
B.PD_idno,
B.Fullname,
C.datetime_process,
C.detail_1, 
C.detail_2,

--/get the quantity in tbl_DT2 if exists, else, just get the one in tbl_Process/--
(case when (C.PD_idno = D.PD_idno)
	then E.DT_quantity
	else C.process_quantity
end) as Quantity

from tbl_InOut A
inner join tbl_PersonDetail B on A.PD_idno = B.PD_idno
inner join tbl_Process C on B.PD_idno = C.PD_idno 
--/get only process based on time_in and time_out/--
and C.datetime_process between A.time_in and A.time_out

left join tbl_DT1 D on C.detail_1 = D.detail_1 
and C.detail_2 = D.detail_2
left join tbl_DT2 E on D.DT_code = E.DT_code
and D.datetime_process = E.datetime_process

--/get process items between current date and past 7 days/--
where C.datetime_process between CONVERT(date, GETDATE() - 7)  and CONVERT(date, GETDATE() )	

什么我试过了：



尝试了正常的提示：使用这些提示加速SQL Server数据库查询 - TechRepublic [ ^ ]

和性能调优SQL查询| SQL教程 - 模式分析 [ ^ ]

推荐答案

你必须注意你的连接，你有一个三向连接。

PD_idno是相同的在前三个表中，所以更改连接，以便tbl_Process只连接到tbl_InOut。

像这样：

You have to pay attention to your joins, you have a three way join.
The PD_idno is the same in the first three tables, so change the joins so that tbl_Process only joins to tbl_InOut.
Like this:

SELECT  B.PD_idno
       ,B.Fullname
       ,C.datetime_process
       ,C.detail_1
       ,C.detail_2
       /*get the quantity in tbl_DT2 if exists, else, just get the one in tbl_Process*/
       ,CASE 
            WHEN (C.PD_idno = D.PD_idno) THEN E.DT_quantity
            ELSE C.process_quantity
        END AS Quantity
FROM    tbl_PersonDetail B
JOIN    tbl_InOut A ON A.PD_idno = B.PD_idno
JOIN    tbl_Process C 
    ON  a.PD_idno = C.PD_idno
    /*get only process based on time_in and time_out*/
    AND C.datetime_process BETWEEN A.time_in AND A.time_out
LEFT JOIN tbl_DT1 D 
    ON  C.detail_1 = D.detail_1
    AND C.detail_2 = D.detail_2
LEFT JOIN tbl_DT2 E
    ON  D.DT_code = E.DT_code
    AND D.datetime_process = E.datetime_process
/*get process items between current date and past 7 days*/
WHERE   C.datetime_process BETWEEN CONVERT(DATE, GETDATE() - 7) AND CONVERT(DATE, GETDATE())

还要确保你有关于tbl_Process的索引（PD_idno，datetime_process）

Also make sure you have an index on tbl_Process (PD_idno,datetime_process)

这篇关于可以使用数千个数据更快地运行查询（SQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！