Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

urgent help with stored procedure performance tuning Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 12:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 16, 2014 9:35 PM
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

Post #1462781
Posted Wednesday, June 12, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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)
Post #1462786
Posted Wednesday, June 12, 2013 1:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1462793
Posted Tuesday, June 25, 2013 12:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524

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
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
Post #1466989
Posted Tuesday, June 25, 2013 1:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1467011
Posted Wednesday, July 31, 2013 11:54 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 11:47 PM
Points: 198, Visits: 679

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
Post #1479790
Posted Tuesday, September 3, 2013 1:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
Thanks for notifying,
but please tell us have you got the solution?


Neeraj Prasad Sharma
Sql Server Tutorials
Post #1490780
Posted Wednesday, September 18, 2013 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 30, 2013 2:38 PM
Points: 15, Visits: 57
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?
Post #1495983
Posted Wednesday, September 18, 2013 1:42 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:35 AM
Points: 56, Visits: 363
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.
Post #1496106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse