Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


urgent help with stored procedure performance tuning


urgent help with stored procedure performance tuning

Author
Message
Lucky9
Lucky9
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 297
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16574 Visits: 17018
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36143 Visits: 18747
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Neeraj Prasad Sharma
Neeraj Prasad Sharma
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 675

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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19026
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
Exploring Recursive CTEs by Example Dwain Camps
C.K.Shaiju
C.K.Shaiju
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 707

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
Neeraj Prasad Sharma
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 675
Thanks for notifying,
but please tell us have you got the solution?

Neeraj Prasad Sharma
Sql Server Tutorials
stergiazotali
stergiazotali
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 58
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
MadAdmin
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 1915
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
Gail Shaw's Performance Blog
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search