[AccessD] SQL Server speed issues - was server locking up.

Are you updating like this

UPDATE tblSomeTable SET SomeField ='' WHERE SomeField IS NULL?

or

UPDATE tblSomeTable SET SomeField ='' WHERE ISNULL(SomeField,'') = ''



On Tue, Sep 29, 2009 at 9:29 AM, jwcolby <jwcolby at colbyconsulting.com> wrote:
> Well, it appears that I have issues with SQL Server, above and beyond the "locking up" issue
>
> When I rebooted the server this morning (it never did finish this update process last night), my
> database being updated was NOT corrupted, indicating no writes were happening to it when I rebooted.
>  Furthermore it did not take off and try and do "gotta finish log file stuff" so it appears that it
> wasn't even doing that stuff any more.  Strange in itself.
>
> I have spent a confusing morning trying to discover what exactly is going on.  The first thing that
> is going on is that just updating a single field To NULL in 21 million records is taking 24 minutes.
>  That is a million records / minute which is not stellar IMHO.  The field is not indexed so it is
> not an "updating the index" kind of problem.
>
> I can tell you that I fed the "update" stored procedure a "Top() one million" kind of query and it
> took only 17 seconds to update one million records, that same field.  If you do the math, 17 seconds
> / million times 21 million records is only about 6 minutes to update the field for every record.  So
> why does it take 24 minutes to just do a simple "set that field in every record to null"?
>
> This just makes no sense to me, but I am not a SQL Server kind of guy.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>