jwcolby
jwcolby at colbyconsulting.com
Tue Sep 29 15:59:58 CDT 2009
Update tblSomeTable Set SomeField = NULL; John W. Colby www.ColbyConsulting.com David McAfee wrote: > 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 >> >