Can you groups modifications, so you don't get that many transactions? SQL
Server will issue a physical write for each transaction.
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.
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.