Paging logic is slow in SQL Server script

[GoogleCodeExporter]

My Database already has 194,374 exceptions logged and bringing up the error
log is really slow. I took a look and noticed that the code copies every
row into a temp table.

There are more efficient methods of paging than this. I'll attach two
options. 1 for SQL 2000 and one screaming fast option for SQL 2005.

We might want a SQL 2005 provider so we can take advantage of the huge
speed improvements.

Original issue reported on code.google.com by haac...@gmail.com on 5 Sep 2007 at 6:16

Attachments:

• elmah-sql2000-option.txt
• elmah-sql2005-option.txt

[GoogleCodeExporter]

Thanks for your proposals, Phil. I suppose you've already done some performance 
tests between the current and newer scripts for SQL 2000? If so, it would be 
nice to 
get some numbers. I always knew that the indicies were not well defined for the 
queries so I imagine that a big part of the problem is also that there are too 
many 
table scans going on. In fact, I imagine that your modified version will also 
fall 
prey to table scans (digging into the actual query plans did confirm that).

Original comment by azizatif on 5 Sep 2007 at 8:36

• Changed title: Paging logic is slow in SQL Server script

[GoogleCodeExporter]

I tested the new sp on SQL Server 2005 with 15000 records and it works pretty 
fast.

Original comment by simone.b...@gmail.com on 6 Sep 2007 at 12:41

[GoogleCodeExporter]

The SQL 2005 script is actually very very fast. The SQL 2000 script I proposed 
is
only slightly faster. I didn't see any table scans, just clustered index scans.

Original comment by haac...@gmail.com on 6 Sep 2007 at 3:22

[GoogleCodeExporter]

There should be no clustered index. The only index (PK_ELMAH_Error) that's 
defined 
in the DDL script to date is for the ErrorId as the primary key. Since this is 
usually assigned a randomly generated GUID, it doesn't make sense to be 
clustered. 
The very original version of the script from the initial GDN import defined 
PK_ELMAH_Error as clustered, but that was really an oversight. Perhaps you have 
the 
database running on the older version?

Original comment by azizatif on 6 Sep 2007 at 6:05

[GoogleCodeExporter]

Would it make sense to create an index on the Application field, since most of 
the
lookups are done on that field?

Original comment by simone.b...@gmail.com on 6 Sep 2007 at 7:39

[GoogleCodeExporter]

Fixed in revision 144. I applied to changes. First I hand-merged in the SQL 
script 
for 2000 provided in the original issue comment (thanks Phil). This gave a 
slight 
improvement over a test database that I created with 200,000 entries in the 
ELMAH_Error table. The improvement was around 1 second, going from 3 (using the 
original script) down to around 2. I also then added an index over the columns 
used 
in the WHERE and ORDER BY clauses to get rid of table scans and this 
dramatically 
speeded up the paging query, going from around 2 second to now approximately 
0.03 
seconds (66+ times faster). :)

Original comment by azizatif on 12 Sep 2007 at 9:59

• Changed state: Fixed