Click here to Skip to main content
15,124,187 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I am using the following query which returns 12158 records:

SQL
SELECT A.[Product Number (SKU)],A.[Serial Numbers],A.[Sell Through Quantity],A.[Seller - Company Name],C.[Partner Locn ID],
A.[Eclipse ID],A.[MC Eligibility],A.[Seller - Company ID],A.[Trans Date],A.[Trans Doc ID],
B.[Programe ID],B.[Promo Type],B.[Rebate Value]
FROM dbo.FinalOutput A LEFT JOIN dbo.PromoMaster B
ON (B.SKU <> 'ALL' And A.[Product Number (SKU)] = B.SKU Or B.SKU = 'ALL' And A.[Product Line ID] = B.PL)
LEFT JOIN dbo.PARTNER_MST C ON A.[Seller - Company ID] = C.[Partner Pro ID]
WHERE A.[Trans Date]>=B.[Start Date] And A.[Trans Date]<=B.[End Date] AND Charindex(A.[Seller - Company ID],B.[T1 ID])>0


Here, The main issue is its taking too much time(00:02:49 mins) to display the result.
Because of this delay, an error occurs in the application saying
"Query Timeout Expired"

I want to make this query work faster. Can any one please guide me on this issue..


Regards,
Raj
Posted
Updated 13-Sep-11 3:30am
v2
Comments
Herman<T>.Instance 13-Sep-11 8:36am
   
would this help?
SELECT A.[Product Number (SKU)],
A.[Serial Numbers],
A.[Sell Through Quantity],
A.[Seller - Company Name],
C.[Partner Locn ID],
A.[Eclipse ID],
A.[MC Eligibility],
A.[Seller - Company ID],
A.[Trans Date],
A.[Trans Doc ID],
B.[Programe ID],
B.[Promo Type],
B.[Rebate Value]
FROM dbo.FinalOutput A
LEFT JOIN dbo.PromoMaster B ON ((B.SKU <> 'ALL' And A.[Product Number (SKU)] = B.SKU) Or (B.SKU = 'ALL' And A.[Product Line ID] = B.PL))
LEFT JOIN dbo.PARTNER_MST C ON A.[Seller - Company ID] = C.[Partner Pro ID]
WHERE (A.[Trans Date] BETWEEN B.[Start Date] And B.[End Date] )
AND Charindex(A.[Seller - Company ID],B.[T1 ID])>0
Raj.rcr 13-Sep-11 8:58am
   
No, there is no change in the execution time..
Herman<T>.Instance 13-Sep-11 8:36am
   
you are using left joins, that can be costly.
which indexes are created?
Raj.rcr 13-Sep-11 8:59am
   
No indexes are created. I guess, I need to create the indexes to make it work faster. Can u please guide how do I do that?
Reiss 13-Sep-11 8:48am
   
I can see a few issues, but I really need the 3 table schemas as I suspect that most of the performance issues are going to be around the indexes you (don't) have - also when you run it with the execution plan option turned on where is the main bottle neck?

1 solution

Open SSMS
Put the SQL in a new query window
Run Query -> Display Estimated Execution plan.

* The query plan is displayed *

Identify where your query is consuming a high % of the workload

Are your tables indexed correctly? Tools -> Database Engine Tuning Advisor
Analyse your query workload to see if it could benefit from indexing

For example, you should definitely have an index on your 'SKU' fields, is this the case?
   
Comments
Mehdi Gholam 13-Sep-11 8:46am
   
My 5!
Raj.rcr 13-Sep-11 8:54am
   
Yes, I have not created the indexes for this view. But, how to optimize this?
Raj.rcr 13-Sep-11 8:55am
   
Here only the FinalOutput is a View
Dylan Morley 13-Sep-11 10:27am
   
As mentioned, the 'Estimated Execution plan' and 'Database Engine Tuning Advisor' will help you identify where your database will benefit from indexes.

The tables are the basis of all your data access, so if you start building layers of views on top of them and they are not correctly indexed, you will see performance issues.

Go back to basics, go into your raw tables and look at the table structure. In general they should all have a clustered index, the physical order in which the data is ordered.

They will usually always benefit from a few extra indexes,

* Put an index on the SKU field for dbo.PromoMaster and whatever table view FinalOutput is querying. You are joining on this field, and it is part of the WHERE clause, so an index will help

* Consider indexes on [Trans Date] \ [Start Date] fields, they are part of the WHERE

Basically, where you are joining or where you are filtering (in a WHERE clause), you can help SQL server find the data faster by indexing.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900