Re: extra index scans on update

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Delbert Glass (delbert_at_noincoming.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 16:01:09 -0600

Perhaps datatype of colx and x are different.

(Let me guess, n is about 50 too.)

Bye,
Delbert Glass

"Scott" <anonymous@discussions.microsoft.com> wrote in message
news:5751C499-0B63-4E67-AE8A-5893A68F0EC0@microsoft.com...
> When I initiate a simple update on a single row in a table, I am
generating dozens of index scans for reasons I don't understand. The form of
the query is
>
> update table1 set col0=0, col1=1, col2=2, col3=3,..,coln=n where colx=x
>
> colx is a clustered primary key. Only a single row is updated for each
command.
>
> When I try to analyze what is going on in SQL Profiler I see:
> SQL:BatchStarting
> SCAN:Started
> SCAN:Stopped
> ... (about 50 scans occur)
> SCAN:Started
> SCAN:Stopped
> SP:CacheInsert
> SQL:StmtStarting
> SP:CacheHit
> SQLTransaction
> Excecution Plan
> ...From this point on it appears that the update occurs without any
superfluous actions
>
> If I run the same command again, it works much faster because there are
many fewer scans. The only thing I can think of is that SQL Server is
unsuccessfully scanning the first 50 times because the row is not in the
cache. When I run the same command again, the row is in the cache and things
work quickly.
>
> Another strange behavior is that updates occur slowly on a table with 6000
rows while the updates occur quickly on a table with 300,000 rows. Both
updates are similar in that I am using a primary key to select a single row
to update.
>
> Thanks for your help.

Relevant Pages

  • Reversing Dates on First Insert in DataEnvironment and VB6
    ... I am using a Command to INSERT a single row INTO an Access table. ... Date/Time fields are causing me grief by reversing the Day and Month - but ... Dim dtEnd As Date ...
    (microsoft.public.vstudio.development)
  • Re: Command button on a continuous form
    ... I have a subform that lists all the records linked to the main form's ... But the problem is that all the command buttons are enabled (on ... single row, ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the Visual Basic command to select an entire row in Excel.
    ... Where Range is a cell address on the row you want to select. ... > single row in an Excel spreadsheet it selects all the rows in the ... Is there another command that will select a single row?? ...
    (microsoft.public.excel.programming)
  • Unix script question (basic)
    ... if we were to put this into a loop to read every single row in the file and ... if so what can we have for 'command'? ... cat looks at a field at a time ...
    (comp.unix.questions)