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

David McAfee davidmcafee at gmail.com
Tue Sep 29 17:01:35 CDT 2009


Sorry, peeking my head in during my jury duty breaks.

I was just wondering. I would ASSume that the where some field is NULL.

and John, you arent saying that the field is equla to Null, are you?

it should be WHERE somefield IS NULL

On Tue, Sep 29, 2009 at 1:47 PM, Max Wanadoo <max.wanadoo at gmail.com> wrote:
> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>




More information about the AccessD mailing list