urgent help with stored procedure performance tuning

Question

urgent help with stored procedure performance tuning

Lucky9

Default port

Points: 1470
More actions
June 12, 2013 at 12:40 pm

#275030

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

We can help, but you need to do some work. Mock up tables. Give us CREATE TABLE statements for the columns needed in the query for all tables. Give us 5-10 rows of INSERT statements with sample data (please no real data). Then write the query and it should execute against your statements correctly.

If you get that far, post the info and someone will give you some options.

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 3

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

Please reply

Can you cut down your table ? archive the data or can you do table partition?

Are you using Indexing well in your tables?

Can you use Full Text search?

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

Yes it does so

Can you change your datatype to numeric type (tinyint , int bingint decimal?

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.

Query has 100 columns :w00t:

i mean really All 100 columns are required in one report.

less columns less overhead, more column more overhead.

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

Remove the SUbquery and join those tables.

Neeraj Prasad Sharma
Sql Server Tutorials

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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
...
so with the above problem what is the best way to rewrite the sql query

Can you post the Actual Execution Plans for the stored procedure? That's the quickest way for folks here to determine why your queries are slow.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 5

Neeraj Prasad Shama
BLOG : Sql Server Tutorials
Neeraj Prasad Sharma
Sql Server Tutorials

Hi Neeraj,

The link (Sql Server Tutorials) is not working for me

_____________________________________________
One ounce of practice is more important than tonnes of dreams

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 6

Thanks for notifying,

but please tell us have you got the solution?

Neeraj Prasad Sharma
Sql Server Tutorials

stergiazotali SSC-Addicted Points: 421 More actions · Answer 7

Is the rt.productid referring to column that is part of the main query? I guess what I am asking is if your sub query is a correlated one. How about removing sub queries and replace them with derived tables?

MadAdmin SSChampion Points: 11261 More actions · Answer 8

Looking at the example, it looks like it should run that subquery iteratively.

Looks like maybe this is a prime example for APPLY?

But an alias is being referenced (RT) which has not been specified so that query will not work.

Give us a query which will compile.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]