How to optimize following query ?

Question

0.00/5 (No votes)

See more:

I have a type in my procedure which takes 1sec to execute .. i have been told to optimize this query .. how can i do this..

Here are sp

------------------------

SQL

ELSE IF(@in_type = 5) --Used in Catalogcl class 

BEGIN

    SELECT d.in_catalog_id, a.in_product_id, a.vc_product_name, a.vc_product_image_large,

    (CASE a.bt_product_image_exist WHEN 1 THEN a.vc_product_image_small WHEN 0 THEN '/product_small_images/comingsoon1.gif' END) AS vc_product_image_small,

  dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) AS mn_sale_price,

    d.vc_catalog_name + ' ' + a.vc_product_name AS vc_product_full_name

    FROM dbo.product a

    INNER JOIN dbo.catalog_product b ON a.in_product_id = b.in_product_id

    INNER JOIN dbo.website_catalog c ON b.in_catalog_id = c.in_catalog_id

    INNER JOIN dbo.catalog d ON c.in_catalog_id = d.in_catalog_id

    WHERE a.bt_active = @bt_active AND b.bt_active = @bt_active AND c.bt_active = @bt_active AND d.bt_active = @bt_active

    AND c.in_website_id = @in_website_id AND d.in_catalog_sub_type_id = @in_catalog_sub_type_id

    AND (@bt_best_sellers = 0 OR b.bt_best_sellers = @bt_best_sellers)

    AND dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) > 0

    ORDER BY a.vc_product_name

END

Please suggest

Posted 24-Oct-13 10:21am

k@ran

Updated 24-Oct-13 10:22am

v2

Add a Solution

Comments

ZurdoDev 24-Oct-13 16:37pm

What part is slow?

PIEBALDconsult 24-Oct-13 17:15pm

Looks good to me, and one second is awesome. Don't mess with it.

5 solutions

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)

Amol_B · Answer 1 · 2013-10-24T20:04:00

Solution 2

Hi,
You are using function "catalog_fnc_get_sale_price" in your query , if you can expand the function , within the query it can surely in increse speed of query. AS for each row, function is being called in your case. you can verify it by using execution plan

Posted 24-Oct-13 20:04pm

Amol_B

thatraja · Answer 2 · 2013-10-24T21:21:00

Agree with others, the query looks good. You may look at other ways to improve things.
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

Mycroft Holmes · Answer 3 · 2013-10-24T20:18:00

As Amol_B suggested make sure that the function you are calling in both the the select and where clause is both valid and optomised - it is your biggest obvious penalty.

Next turn on actual execution plan in SSMMS (assumes sql server) and look for missing indexes in the result.

If there is nothing there then go back and ask your boss why he thinks it needs optomising.

Jörgen Andersson · Answer 4 · 2013-10-25T01:28:00

Find out if the function call is the culprit by only calling it once, using a subquery like this:

SQL

SELECT  in_catalog_id
       ,in_product_id
       ,vc_product_name
       ,vc_product_image_large
       ,vc_product_image_small
       ,mn_sale_price
       ,vc_product_full_name
FROM
    (
    SELECT  d.in_catalog_id
           ,a.in_product_id
           ,a.vc_product_name
           ,a.vc_product_image_large
           ,(CASE a.bt_product_image_exist WHEN 1 THEN a.vc_product_image_small WHEN 0 THEN '/product_small_images/comingsoon1.gif' END) AS vc_product_image_small
           ,dbo.catalog_fnc_get_sale_price(5, d.in_catalog_id, 0, a.in_product_id, @in_website_id) AS mn_sale_price
           ,d.vc_catalog_name + ' ' + a.vc_product_name AS vc_product_full_name
    FROM dbo.product a
    INNER JOIN dbo.catalog_product b ON a.in_product_id = b.in_product_id
    INNER JOIN dbo.website_catalog c ON b.in_catalog_id = c.in_catalog_id
    INNER JOIN dbo.catalog d ON c.in_catalog_id = d.in_catalog_id
    WHERE   a.bt_active = @bt_active
    AND     b.bt_active = @bt_active
    AND     c.bt_active = @bt_active
    AND     d.bt_active = @bt_active
    AND     c.in_website_id = @in_website_id
    AND     d.in_catalog_sub_type_id = @in_catalog_sub_type_id
    AND     (@bt_best_sellers = 0 OR b.bt_best_sellers = @bt_best_sellers)
    )
WHERE   mn_sale_price > 0
ORDER BY vc_product_name

**Zoltán Zörgő** · Answer 5 · 2013-10-24T10:41:00

If query optimization would be that simple, just a reformulation of a statement string, it would be a fully automated process, it would be a funtion of any sql engine, thus would not be necessary.
One has to consider several things to optimize a query, like query plans, indexes, table sizes ad so on... But first of all, data semantics. None of these is available to us to help you.
Sorry, but you are on your own...

How to optimize following query ?

5 solutions

Solution 2

Solution 4

Solution 3

Solution 5

Solution 1

Add your solution here

Preview 0