How to get fast performance from stored proc?

How to get fast performance from stored proc?

Post by Alan Williamso » Sun, 06 Feb 2000 04:00:00



I have a few stored procedures that I run that take days to complete.
They process data to create summary tables for a reporting web site.
When they run, they make about 140,000,000 transactions.  I was
wondering if anyone has ideas on how to get the best performance.
 
 
 

How to get fast performance from stored proc?

Post by Tibor Karasz » Tue, 08 Feb 2000 04:00:00


Alan,

Can you groups modifications, so you don't get that many transactions? SQL
Server will issue a physical write for each transaction.

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


Quote:> I have a few stored procedures that I run that take days to complete.
> They process data to create summary tables for a reporting web site.
> When they run, they make about 140,000,000 transactions.  I was
> wondering if anyone has ideas on how to get the best performance.


 
 
 

1. Stored Proc - SLOW, Query Analyzer - FAST

In Query Analyzer, if I run a stored procedure called spGetReport, it takes 36 seconds.  If I copy the SQL code from the stored procedure and just execute it in another window in QA, it takes 2 seconds.  This behavior is replicated on test and production servers and the times remain the same over multiple runs.  Each returns exactly the same data.

I see in the QA execution statistics that the sp uses millions of logical reads to get the data vs. a very few logical reads in the non-sp query.  Why would this be and how can I make the stored proc run at nearly the same speed as the straight code.  I cannot get an execution plan to display for either due to the use of a temp table in the query.  This is SQL 7 so cannot use memory table.  Nevertheless, why would a stored proc do so many more logical reads than the straight SQL code?  I have recompiled it, got all the statistics up to date and reindexed the DB.  

Thanks for any help.

Dave K.

2. Assignment of hostprocesses

3. Stored Proc / View (Slow and Fast depending on how ran)

4. normalized database

5. Stored proc fast in query analyser but slow via SQLServer Agent

6. displaying required layout

7. sql 6.5 faster than 2000???? Stored proc woes

8. Dataquest Article; The First 6 Mo. Under CA

9. faster to insert with stored proc - or execute sql statement from connection

10. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

11. creating a stored proc from inside another stored proc

12. Stored Proc Calling Another Stored Proc

13. newbie trying to execute a stored proc from within a stored proc