查询调优 [英] query tuning

问题描述

我有一个类似这样的查询:

I have a query which is like this:

SELECT distinct 
A.col1,
A.col2,
B.col3,
B.col4,
C.col5,
Left(C.col5, 8) AS GLAccount,
D.col6,
E.col7,
D.col8 as LineItemTotal
FROM dbo.TABLE1 A LEFT OUTER JOIN dbo.TABLE2 B
ON A.col1=B.col1
LEFT OUTER JOIN dbo.TABLE3 C
ON A.col1 = C.col1 LEFT OUTER JOIN TABLE4 D
ON B.col2 = D.col2 LEFT OUTER JOIN TABLE5 E
ON D.col3=E.col3
ORDER BY A.col2 ASC

这将检索大约600万行的appx ...大约需要7分钟....我必须对其进行优化，我尝试将其中一半放入视图中，然后使用视图，但这只能减少执行时间， 3分半钟.....有人可以给我解决方案吗?

This retrives appx aabout 60 lakh rows...and takes about 7 mins....I have to optimize it, I tried taking half of them into the view and then using the view, but that could only bring down the execution time to 3 and a half mins.....could someone please give me a solution for this?

推荐答案

嗨;

尝试与Cte一起使用，对于恢复批量记录很有用；

检查一下；

作者；
A.Faijurrahuman

Hi ;

Try to With Cte that one is useful for reterving the bulk records;

to check out this ;

By;
A.Faijurrahuman

您是否对这些表进行了规范化，例如非空列，默认约束等.我想是的.只需考虑选择所有列而不是特定列，我相信这将需要更多时间.然后确定您是否真的需要显示所有记录.

Do you normalized those tables like not null columns, default constraints, etc. i guess so. Just think selecting all columns instead of particular, i''m sure it will take more time. Then decide do you need really to display all the records.

这篇关于查询调优的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！