SQL Query Optimization

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 13-Sep-11 2:27am

Raj.rcr

Updated 13-Sep-11 2:30am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dylan Morley · Answer 1 · 2011-09-13T02:42:00

Solution 1

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?

Posted 13-Sep-11 2:42am

Dylan Morley

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.