RE: slow server response

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: mark h (h_at_discussions.microsoft.com)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 09:24:03 -0700

The general rule is to use cursors and temp tables as seldom as possible. They both cause the affects you are experiencing, poor performance. The reason is simple a cursor creates a copy of the table in memory, in your case a million records. It takes time to move that many records into memory. In addition,depending on the type of cursor you define there is a lot of over head in managing them. Try to use forward only if you must use a cursor.
If you come to SQL from a procedural background you're use to working record by record but in SQL you work in sets. Most uses of cursors and temp tables can be reprogrammed to use set therory.

"saif" wrote:

> Hi
>
> I have created a stored procedure which reads results from a table using
> cursor and then inserts those results in a temp table. When i run this
> stored procedure in query analyzer, it runs fine but takes too long
> returning results. This stored procedure extracts data from a table that has
> more than 1000000 records. Now confusion is that when i created this stored
> procedure and used it for first time, results were returned quickly but now
> its taking too long. Why is this happening? Is there any solution to this
> problem? Somebody told me to use indexes! Please give me some urls from
> where i can get help about indexes. I am new to sql programming. I shall be
> thankful.
>
> Regards
>
> Saif
>
>
>

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... A stored procedure logic will be exactly as fast as the algorithm you chose when you wrote it. ... The SQL inside the view will be as fast as the optimizer can make it depending on the statistics using 30 years of research. ... Anytime you use logic you take the HOW away from the RDBMS and you have exactly one choice to combine results: Nested loop join (aka nested cursors). ... Try a couple experiements forcing SQL Server to use different join implementations (nestedloop, merge, hash, ..) on decent sized tables, then think about whether you can afford nested loop. ...
    (comp.databases.ms-sqlserver)
  • Re: disable/enable all SQL Agent jobs in a non cursor approach
    ... always heard dreaded things about cursors and was just trying ... because you should be calling a stored procedure for ... > What is wrong with a cursor approach for this type of one-off, admin task? ... can one provide the SQL for it ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)