|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
optmizing CursorsHi all
I m working on a stored procedure with 5 nested cursors working on 5 temporary tables . I tried some technics to reduce the response time but i didnt succed I tested these methods : - Replacing cursor by a while loop - calling form the cursor a User defined fuction -replacing tempprary tables by @table (the sql data type table) -using for some tables (Dbcc pintable )... Do you have other technics or advices to optimise my long sp thank The best way to optimize cursors is not to use them at all. Are you
absolutely certain that a set-based approach is not possible in your specific case? Perhaps you should post DDL, sample data and expected results, or at least elaborate on your requirements - specifically the part that implies the use of FIVE nested cursors. ML --- http://milambda.blogspot.com/ hch wrote:
> Hi all The number of situations where you need to use one cursor is pretty> I m working on a stored procedure with 5 nested cursors working on 5 > temporary tables . I tried some technics to reduce the response time but i > didnt succed rare, let alone 5 nested ones. It sounds like the procedure needs a complete rewrite to use set-based operations, however given that it sounds like a complex procedure that won't be a small job. Just as an example, a system I worked on had a customer billing routine written as a cursor, so it billed one customer at a time. The thing took all weekend to run! It was re-written without cursors and now takes about 20mins. hch wrote:
> Hi all The obvious alternative you didn't mention is to use set-based SQL> I m working on a stored procedure with 5 nested cursors working on 5 > temporary tables . I tried some technics to reduce the response time but i > didnt succed > I tested these methods : > - Replacing cursor by a while loop > - calling form the cursor a User defined fuction > -replacing tempprary tables by @table (the sql data type table) > -using for some tables (Dbcc pintable )... > > Do you have other technics or advices to optimise my long sp > thank instead of cursors, temp tables and WHILE loops. It seems highly likely that you can avoid using 5 nested cursors. If you can avoid cursors and WHILE loops then you should do so. To help you out, please explain what you want achieve. DDL, sample data and expected results is the best way to specify your problem. http://www.aspfaq.com/etiquette.asp?id=5006 -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- I agree with the others remarks that SET-based processing is the way to go
if possible. Note that WHILE loops are only pseudo-cursors. If you must use cursors for some reason, use FAST_FORWARD/READ_ONLY where applicable. > -using for some tables (Dbcc pintable )... I have never encountered a situation where DBCC PINTABLE helped performance. Normal SQL Server caching strategies use buffer cache where it is most needed. DBCC PINTABLE functionality was removed from SQL 2005. -- Show quoteHope this helps. Dan Guzman SQL Server MVP "hch" <h**@discussions.microsoft.com> wrote in message news:2BA4F1E0-9503-4C67-8198-CA659682E866@microsoft.com... > Hi all > I m working on a stored procedure with 5 nested cursors working on 5 > temporary tables . I tried some technics to reduce the response time but i > didnt succed > I tested these methods : > - Replacing cursor by a while loop > - calling form the cursor a User defined fuction > -replacing tempprary tables by @table (the sql data type table) > -using for some tables (Dbcc pintable )... > > Do you have other technics or advices to optimise my long sp > thank > Adding to the other comments, something is truly wrong with how the problem
is conceptualized and the solution is designed for five nested cursors to be used. I haven't used even one cursor in the past two years. There is almost always a set based operation that is far more efficient than using a cursor. If you post a descriptive scenario of the problem, with a clear presentation of the desired results, you are quite likely to get some of the best help available in coming up with a better analysis of possible solutions. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "hch" <h**@discussions.microsoft.com> wrote in message news:2BA4F1E0-9503-4C67-8198-CA659682E866@microsoft.com... > Hi all > I m working on a stored procedure with 5 nested cursors working on 5 > temporary tables . I tried some technics to reduce the response time but i > didnt succed > I tested these methods : > - Replacing cursor by a while loop > - calling form the cursor a User defined fuction > -replacing tempprary tables by @table (the sql data type table) > -using for some tables (Dbcc pintable )... > > Do you have other technics or advices to optimise my long sp > thank >  |
|||||||||||||||||||||||
Other interesting topics