|
|
You're in the
sql server programming
group:best way to tune a stored procedure?
sql server programming:
>> I have a big 200 line stored procedure that pulls (select ...) data from 10 joined tables. <<T-SQL is not mean to be an application development language. The old rules of thumb were (1) five or fdewer tables (2) less than one page (30-40 lines) of T-SQL (3) Never communicate directly with the user (no PRINT, no user inputs). Shooting the author of this kind of thing will probably improve total thruput of the entire appplication greatly. [quoted text, click to view] >> I don't really know the best steps for tuning one specific procedure and the tables it uses. <<You probably need to re-write it from scratch rather than tune it. You can get execution plans and all that kind of stuff, but the size of it implies lots of temp tables, horrible IF-THEN-ELSEs that can be replaced with CASE expressions; WHILE with cursors; etc. A thing this big, in my experience, is a direct port of a COBOL program into T-SQL, almost line for line. The working files become temp tables, and file processing becomes cursors. --CELKO-- =========================== Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. *** Sent via Devdex http://www.devdex.com ***
I have a big 200 line stored procedure that pulls (select ...) data from 10
joined tables. Results are slow, often taking about 4-10 seconds for 1000 rows of output. The SQL Server hardware is new and fast. What are the steps I should follow to tune this stored procedure and/or the applicable tables? I've run profile before but I've forgotten how to use it and I don't really know the best steps for tuning one specific procedure and the tables it uses. Thanks for any suggestions/steps.
[quoted text, click to view]
> I have a big 200 line stored procedure that pulls (select ...) data from 10 > joined tables. Results are slow, often taking about 4-10 seconds for 1000 > rows of output. The SQL Server hardware is new and fast. First, I am not suggesting in any way that SQL Server has "rushmore technology", but I have two suggestions: 1. Strongly consider indexing any column that fits one of these conditions: Primary Key Foreign Key Used frequently in a JOIN, WHERE or ORDER BY 2. Read up on the "Rushmore Technology" that Microsoft Access has. I have observed that applying the same good sense to query's in SQL Server has paid off. Further, strategize the statements in your SQL. For example, if you know that you are about to do a 12 table join in your from clause, try to phrase the joins so that the order of the joins is from the most specific to the least specific so that when the underlying engine processes them, it can eliminate the most rows first. For Example, one of the things I have written at work is a complex algorithm that decides which set of records to export to a partner system. This set of records is dependent on a "ColocKey". Another process builds a list of ColocKeys in a table, and then my code JOINs that with like 17 other tables to build a huge export file. The first step in my export process is to select from that table. When I JOIN other tables, the record base is limited by the ColocKey items. As other tables are also joined, I use parens to make sure the joins happen in the order I want. Before I was there, this process ran overnight to process roughly 100,000 records. After I fixed it like this, it processes the same amount of data in less than 30 seconds. -- Peace & happy computing, Mike Labosh, MCSD "SELECT * FROM Users WHERE Clue > 0"
Hi. Thanks for the reply. I did that, and found one table scan that had a
cost of 23%. So I added an index on the applicable "where" field in the table. But now my result time is the same, but with a new "bookmark loop cost 43%" entry in the big plan diagram. What can I do about that? Or maybe I should have selected better index options (I didn't choose any of the checkboxes in the GUI interface for creating the index on this field). [quoted text, click to view] "Hari" <hari_prasad_k@hotmail.com> wrote in message news:eH26aaNUEHA.3420@TK2MSFTNGP12.phx.gbl... > Hi, > > Since you have tune only one procedure it is not required to have Profiler. > You can use the "Execution plan" available inQuery Analyzer to tune the > Query. > > How to select "Show Execution plan" > > 1. Open Query Analyzer > 2. In the Menu, Go to Query option and select "Show Execution Plan" option. > > > After the above 2 steps execute the stored procedure. Main thing you have to > look is any join causing "TABLE SCAN", If any join is doing a TABLE SCAN > then idenfy the table creating the bottle neck and create the necessory > indexes based on the joins inside where condition. > This will increase the performance of your procedure. > > > -- > Thanks > Hari > MCDBA > "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message > news:NkMyc.43003$wO4.27100@twister.socal.rr.com... > > I have a big 200 line stored procedure that pulls (select ...) data from > 10 > > joined tables. Results are slow, often taking about 4-10 seconds for > 1000 > > rows of output. The SQL Server hardware is new and fast. What are the > > steps I should follow to tune this stored procedure and/or the applicable > > tables? I've run profile before but I've forgotten how to use it and I > > don't really know the best steps for tuning one specific procedure and the > > tables it uses. Thanks for any suggestions/steps. > > > > > >
The nonclustered index you added on a single column will help identify
the table rows to return, but it won't provide all the needed column values for each row; that requires a bookmark lookup against the table by using the clustered index keys (bookmarks) provided by the nonclustered index. If the number of columns selected in the query is relatively small with few total bytes, it might be reasonable to add them as additional columns to the nonclustered index you added. If that's not practical and the procedure is still slower than you expect, there are probably other improvements to make, but it's hard to guess without seeing at least an outline of what the query and related tables look like. Steve Kass Drew University [quoted text, click to view] AFN wrote:
>Hi. Thanks for the reply. I did that, and found one table scan that had a >cost of 23%. So I added an index on the applicable "where" field in the >table. But now my result time is the same, but with a new "bookmark loop >cost 43%" entry in the big plan diagram. What can I do about that? Or >maybe I should have selected better index options (I didn't choose any of >the checkboxes in the GUI interface for creating the index on this field). > > > >"Hari" <hari_prasad_k@hotmail.com> wrote in message >news:eH26aaNUEHA.3420@TK2MSFTNGP12.phx.gbl... > > >>Hi, >> >>Since you have tune only one procedure it is not required to have >> >> >Profiler. > > >>You can use the "Execution plan" available inQuery Analyzer to tune the >>Query. >> >>How to select "Show Execution plan" >> >>1. Open Query Analyzer >>2. In the Menu, Go to Query option and select "Show Execution Plan" >> >> >option. > > >>After the above 2 steps execute the stored procedure. Main thing you have >> >> >to > > >>look is any join causing "TABLE SCAN", If any join is doing a TABLE SCAN >>then idenfy the table creating the bottle neck and create the necessory >>indexes based on the joins inside where condition. >>This will increase the performance of your procedure. >> >> >>-- >>Thanks >>Hari >>MCDBA >>"AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message >>news:NkMyc.43003$wO4.27100@twister.socal.rr.com... >> >> >>>I have a big 200 line stored procedure that pulls (select ...) data from >>> >>> >>10 >> >> >>>joined tables. Results are slow, often taking about 4-10 seconds for >>> >>> >>1000 >> >> >>>rows of output. The SQL Server hardware is new and fast. What are the >>>steps I should follow to tune this stored procedure and/or the >>> >>> >applicable > > >>>tables? I've run profile before but I've forgotten how to use it and >>> >>> >I > > >>>don't really know the best steps for tuning one specific procedure and >>> >>> >the > > >>>tables it uses. Thanks for any suggestions/steps. >>> >>> >>> >>> >> >> > > > >
Hi. Point well taken, but my sp doesn't really fall into that category of
development except for some math. There's lots of comments and case statements, but most of the length is because of the number of tables and because of case statements. Thanks for your reply. [quoted text, click to view] "Joe Celko" <jcelko212@earthlink.net> wrote in message news:%23fKxzKOUEHA.3828@TK2MSFTNGP09.phx.gbl... > >> I have a big 200 line stored procedure that pulls (select ...) data > from 10 joined tables. << > > T-SQL is not mean to be an application development language. The old > rules of thumb were (1) five or fdewer tables (2) less than one page > (30-40 lines) of T-SQL (3) Never communicate directly with the user (no > PRINT, no user inputs). > > Shooting the author of this kind of thing will probably improve total > thruput of the entire appplication greatly. > > >> I don't really know the best steps for tuning one specific procedure > and the tables it uses. << > > You probably need to re-write it from scratch rather than tune it. > > You can get execution plans and all that kind of stuff, but the size of > it implies lots of temp tables, horrible IF-THEN-ELSEs that can be > replaced with CASE expressions; WHILE with cursors; etc. A thing this > big, in my experience, is a direct port of a COBOL program into T-SQL, > almost line for line. The working files become temp tables, and file > processing becomes cursors. > > --CELKO-- > =========================== > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. > > *** Sent via Devdex http://www.devdex.com *** > Don't just participate in USENET...get rewarded for it!
Hi. I can't post it here because of work issues but this particular part
that costs 43% is just where Table1.Number = @NumberPassedIn Table1 has hundreds of thousands of rows. Is there any way to now improve upon that Bookmark Loopup Cost? Again, thank you for your reply. [quoted text, click to view] "Hari" <hari_prasad_k@hotmail.com> wrote in message news:OUYq$LOUEHA.3988@tk2msftngp13.phx.gbl... > Hi, > > "The Bookmark Lookup logical and physical operator uses a bookmark to look > up the corresponding row in the table or clustered index." > > The performace issue can be also because of poor Select statement. Can you > post procedure contents. > > > -- > Thanks > Hari > MCDBA > "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message > news:u9Nyc.43071$wO4.38951@twister.socal.rr.com... > > Hi. Thanks for the reply. I did that, and found one table scan that had > a > > cost of 23%. So I added an index on the applicable "where" field in the > > table. But now my result time is the same, but with a new "bookmark loop > > cost 43%" entry in the big plan diagram. What can I do about that? Or > > maybe I should have selected better index options (I didn't choose any of > > the checkboxes in the GUI interface for creating the index on this field). > > > > > > > > "Hari" <hari_prasad_k@hotmail.com> wrote in message > > news:eH26aaNUEHA.3420@TK2MSFTNGP12.phx.gbl... > > > Hi, > > > > > > Since you have tune only one procedure it is not required to have > > Profiler. > > > You can use the "Execution plan" available inQuery Analyzer to tune the > > > Query. > > > > > > How to select "Show Execution plan" > > > > > > 1. Open Query Analyzer > > > 2. In the Menu, Go to Query option and select "Show Execution Plan" > > option. > > > > > > > > > After the above 2 steps execute the stored procedure. Main thing you > have > > to > > > look is any join causing "TABLE SCAN", If any join is doing a TABLE SCAN > > > then idenfy the table creating the bottle neck and create the necessory > > > indexes based on the joins inside where condition. > > > This will increase the performance of your procedure. > > > > > > > > > -- > > > Thanks > > > Hari > > > MCDBA > > > "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message > > > news:NkMyc.43003$wO4.27100@twister.socal.rr.com... > > > > I have a big 200 line stored procedure that pulls (select ...) data > from > > > 10 > > > > joined tables. Results are slow, often taking about 4-10 seconds for > > > 1000 > > > > rows of output. The SQL Server hardware is new and fast. What are > the > > > > steps I should follow to tune this stored procedure and/or the > > applicable > > > > tables? I've run profile before but I've forgotten how to use it > and > > I > > > > don't really know the best steps for tuning one specific procedure and > > the > > > > tables it uses. Thanks for any suggestions/steps. > > > > > > > > > > > > > > > > > > > >
Hi,
Since you have tune only one procedure it is not required to have Profiler. You can use the "Execution plan" available inQuery Analyzer to tune the Query. How to select "Show Execution plan" 1. Open Query Analyzer 2. In the Menu, Go to Query option and select "Show Execution Plan" option. After the above 2 steps execute the stored procedure. Main thing you have to look is any join causing "TABLE SCAN", If any join is doing a TABLE SCAN then idenfy the table creating the bottle neck and create the necessory indexes based on the joins inside where condition. This will increase the performance of your procedure. -- Thanks Hari MCDBA [quoted text, click to view] "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message news:NkMyc.43003$wO4.27100@twister.socal.rr.com... > I have a big 200 line stored procedure that pulls (select ...) data from 10 > joined tables. Results are slow, often taking about 4-10 seconds for 1000 > rows of output. The SQL Server hardware is new and fast. What are the > steps I should follow to tune this stored procedure and/or the applicable > tables? I've run profile before but I've forgotten how to use it and I > don't really know the best steps for tuning one specific procedure and the > tables it uses. Thanks for any suggestions/steps. > >
Hi,
"The Bookmark Lookup logical and physical operator uses a bookmark to look up the corresponding row in the table or clustered index." The performace issue can be also because of poor Select statement. Can you post procedure contents. -- Thanks Hari MCDBA [quoted text, click to view] "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message news:u9Nyc.43071$wO4.38951@twister.socal.rr.com... > Hi. Thanks for the reply. I did that, and found one table scan that had a > cost of 23%. So I added an index on the applicable "where" field in the > table. But now my result time is the same, but with a new "bookmark loop > cost 43%" entry in the big plan diagram. What can I do about that? Or > maybe I should have selected better index options (I didn't choose any of > the checkboxes in the GUI interface for creating the index on this field). > > > > "Hari" <hari_prasad_k@hotmail.com> wrote in message > news:eH26aaNUEHA.3420@TK2MSFTNGP12.phx.gbl... > > Hi, > > > > Since you have tune only one procedure it is not required to have > Profiler. > > You can use the "Execution plan" available inQuery Analyzer to tune the > > Query. > > > > How to select "Show Execution plan" > > > > 1. Open Query Analyzer > > 2. In the Menu, Go to Query option and select "Show Execution Plan" > option. > > > > > > After the above 2 steps execute the stored procedure. Main thing you have > to > > look is any join causing "TABLE SCAN", If any join is doing a TABLE SCAN > > then idenfy the table creating the bottle neck and create the necessory > > indexes based on the joins inside where condition. > > This will increase the performance of your procedure. > > > > > > -- > > Thanks > > Hari > > MCDBA > > "AFN" <DELETEnewsgroupCAPSaccount@yahoo.com> wrote in message > > news:NkMyc.43003$wO4.27100@twister.socal.rr.com... > > > I have a big 200 line stored procedure that pulls (select ...) data from > > 10 > > > joined tables. Results are slow, often taking about 4-10 seconds for > > 1000 > > > rows of output. The SQL Server hardware is new and fast. What are the > > > steps I should follow to tune this stored procedure and/or the > applicable > > > tables? I've run profile before but I've forgotten how to use it and > I > > > don't really know the best steps for tuning one specific procedure and > the > > > tables it uses. Thanks for any suggestions/steps. > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |