查询性能提升 [英] Query Performance improvement

问题描述

以下是我的查询

Below is my query

SELECT Month(StartUtc),Year(EndUtc),Count(ID1),Count(ID2),Count(ID3)
FROM Employee WHERE Creditnumber='95A60378-0ECC-4D23-804F-71BB2BADED08'
 AND AssemblyId='B3468D54-EC5A-4C49-9DB0-F2B9651E78A2' 
AND StartUtc Between '2010-01-01' AND '2010-12-31'
Group By Month(StartUtc),Year(StartUtc)

表Employee上存在非数字索引，用于Creditnumber，AssemblyId，StartUtc
StartUtc是datetime数据类型，所有ID都是BigInt数据类型，
和Creditnumber，AssemblyId是Uniqueidentifier数据类型.
所有设置都与性能调整有关，但查询仍需要9分钟才能返回结果.
BTW雇员包含25K行.
我也尝试在查询中使用强制索引，但性能没有改善
请提出任何进一步提高性能的方法.

There are non clustor index present on table Employee for Creditnumber,AssemblyId,StartUtc
StartUtc is of datetime datatype , All are the IDs are of BigInt datatype ,
and Creditnumber,AssemblyId are Uniqueidentifier datatype.
All is set with respect to performance tunning but still the query take 9 min to return result.
BTW Employee contains 25K rows.
I have tried force index also on the query but no improvement in performance
Please suggest me any way to improve performance further.

推荐答案

我将首先尝试找出导致速度如此缓慢的原因:
将查询隔离在单独的应用程序中，因此对它没有其他影响.
按原样计时查询.如果与您的应用版本明显不同，请找出原因. 将查询切到骨头.

I would start by trying to find out what is causing it to be so slow:
Isolate the query in a separate application, so there are no other effects on it.
Time the query as is. If it is significantly different from teh your-app version, find out why.
Cut the query to the bone.

SELECT MONTH(StartUtc) FROM Employee WHERE Creditnumber=''95A60378-0ECC-4D23-804F-71BB2BADED08''

计时吧.
如果速度很快，请继续添加查询的位，直到速度变慢为止-然后您就可以提高最后添加的位的性能.

抱歉，但是您需要找出问题所在，然后才能解决！

Time it.
If it is fast, keep adding bits of the query back until it is slow - then you can work on improving performance of the last bit you added.

Sorry, but you need to find out where your problem is, before you can fix it!

打开SQL Management Studio，选择新建查询"，然后将您的SQL语句粘贴到窗户.

现在选择查询->显示估计的执行计划.

显示您的查询计划.请注意此处-这些是返回数据的步骤.找出对整个查询执行成本高的步骤.

例如.花费98％-您知道这是您查询中所有时间都花的地方！

你能在这里看到什么吗?使用索引是否正确，还是看到任何表扫描?

Open SQL Management studio, choose ''New Query'' and paste in your SQL statement to the window.

Now choose Query -> display estimated execution plan.

Your query plan is displayed. Pay attention here - these are the steps to it returning your data. Look out for steps with a high % of cost to the overall query execution.

E.g. cost 98% - you know this is where all the time in your query is being spent!

Can you see anything in here? Is it correctly using your index, or do you see any Table Scans?

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