[AccessD] SQL Server Speed testing - Simple update to null.

I would guess that the first run after changing top(N) is taking longer because the SQL server is having to asses just what are those top  N. On the subsequent runs with the same value of N the system is using a cached result set.

Just a guess, like I said.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, September 30, 2009 9:37 AM
To: SQLServerCentral.com; Access Developers discussion and problem solving
Subject: [AccessD] SQL Server Speed testing - Simple update to null.

Well, I am still looking at this.

Last night I purchased PerfectDisk Server and defragged all of my disks.  They were not terribly fragmented but that is out of the way.

Yesterday afternoon late I was finally able to detach the two databases I was working on and rename the log files so as to force SS to build a new one.  Remember that for whatever reason the log files were huge (roughly 35 gigs) and I couldn't shrink them.

BTW I verified last night that I am working in Simple Mode (logging).  All of my work on this server is now backed up regularly and the databases are not updated except when I send tables out for address validation.

This AM I got my log files back out on a dedicated drive (array - set of spindles) specifically for logging.  All of my databases share this log volume but this is not a transaction processing kind of system.

So this morning I started testing.  Updating the AddValid field back to a NULL value.  What I have discovered is interesting, at least to me.

Using a SELECT TOP(N) WHERE AddrValid IS NOT NULL I am updating AddrValid back to null.  This is just testing how long it takes to do a simple update of a single field.

500K		10 Seconds
500K		4 seconds
1 million	20 seconds
1 million	8 seconds
2 million	30 seconds
2 million	16 seconds
4 million	55 seconds
4 million	32 seconds

The first pass takes a lot longer than the second and subsequent passes.  I understand compiling and all but whatever is going on is not just a few milliseconds longer, but rather many seconds longer, and the amount increases almost linearly, i.e. it is not a fixed overhead of say 2 or three seconds.

Notice also that each time I change the N in the TOP(N) view, I have to go through the longer cycle. 
  All of these readings were taken sequentially, in the space of the time it takes to change the N, store the view and rerun the stored proc.

Even so the times are not unreasonable.  Basically a million records in 8 seconds in every "second pass" case.  So it certainly appears that 125K records / second is the "real" maximum speed given my current hardware and settings.  The unsettling part is that the first pass appears to take roughly 50% longer for large recordsets, and more than 100% longer for small data sets.

BTW after all of this testing, my log file is 3.164 gigs.  When I go through the shrink files dialog and look at shrinking the log file for this database it says the file is 3.089 gigs, 68% available free space (2.116 gigs).  I am still confused as to what is in the log file that it cannot shrink it back to .5 meg as it used to do.  Is the log file acting as some sort of "backup" to the main database?  Storing all the changes made or something?  I thought that Simple mode did not do that.

Anyway, all of my records AddrValid are set back to null.  Time to test the times for setting them to the codes I use.

--
John W. Colby
www.ColbyConsulting.com