Click here to Skip to main content
14,669,117 members
Rate this:
Please Sign up or sign in to vote.
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)


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.
Posted
Comments
hitesh_tech 27-Jun-11 11:18am
   
How many records are in employee table approximately?
Dylan Morley 27-Jun-11 11:21am
   
He mentions that - 25K rows

Rate this:
Please Sign up or sign in to vote.

Solution 1

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!
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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?
   
Comments
Dylan Morley 28-Jun-11 4:23am
   
Have tried all the above but it still taking that much of time to execute.
By the way when i ran the query and saw the execution plan i found its not using index instead of indexes it did table scan . I had applied Force indexes but still then it took that much of time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100