• Lucky9 (6/12/2013)


    I have a stored procedure to do the performance tuning, so below are the list of questions could someone please reply

    below is the sample format of query inside the stored procedure

    select column1,

    column2,

    (Select ProductDesc from Product Where ProductID = rt.productid), --subquery 1

    (select ProductnaME FROM PRODUCT WHERE PRODUCTID = RT.PRODUCTID) -- subquery2

    FROM

    tABLE1 T1 INNER JOIN TABLE2 T2 ON T1.COL = T2.COL

    so in the product table we have 2 million records..so what is the best way to replace the product sub query

    COlumns in the join condition are the datatype of varchar, so does this decrease the performance

    query is very big...it has like 100 columns and when i select only one column the query is fast but when i select 100 columns the query speed is slow...so what is the best way to speed up the process.

    so with the above problem what is the best way to rewrite the sql query

    You have obfuscated your actual query to a point that we can't really see what is happening. Your subqueries refer to a table with an alias of RT but that is not in your query. From the extremely vague and detail free description I would think that a left (or maybe inner) join to Product would be the best approach. That would let you get both ProductDesc and ProductName in a single pass. Without any more details that is my best shot in the dark.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/