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

David, what would be the difference. You have asked the question but it
would be nice to know why and if it does really matter, is this only for sql
server or would it also matter with Access sql?

I would tend to use the second construct but it depends what pops into my
mind when I am typing the statement.

Grateful for enlightenment.


Max


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: 29 September 2009 21:25
To: Access Developers discussion and problem solving
Subject: Re: [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
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com