Re: Question about performance of an update statement
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 05/11/04
- Next message: Amol Kasbekar: "About SSPI"
- Previous message: AJACode: "Cross Tab In SQL 2K ??"
- In reply to: Uri Dimant: "Re: Question about performance of an update statement"
- Next in thread: Learner: "Re: Question about performance of an update statement"
- Reply: Learner: "Re: Question about performance of an update statement"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 11 May 2004 09:40:41 -0400
Indexes both help and hurt updates. The following method is not 100%
perfect, but it is a good way to think and to test your UPDATE (or DELETE)
queries.
First take your FROM clause down:
FROM table1
WHERE table1.value = 100
Add
SELECT table1.* (if you have a complex from, then put the table from the
UPDATE clause)
and run this query. The output are the rows that will be updated. If this
query uses an index, or indexes, then these are the helpful indexes because
they help you to find the set to be operated on.
All other indexes may be harmful to your update performance in some manner
as if you modify the data they may have to be rearranged or added to. This
may be of varying amounts of cost, depending on the index page(s) that will
be affected. Worst case all indexes will have completely full pages and
will require page splits. This is unlikely on an ongoing basis. Best case,
the new value is just changed directly on the pages if it is the same size,
or it will fit on the same page.
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Uri Dimant" <urid@iscar.co.il> wrote in message news:uJjnUa1NEHA.2972@TK2MSFTNGP10.phx.gbl... > Learner > How big is your table which you are going to update? > What is an indexes defined on the table? > > > The rule of thumb keep your transaction short in small batches. > Yes , indexes may affect performance during the update ,especially if you > update a primary key (with CLI)of the table. > I'd go with scripting out all indexes before perfoming an update and > applying them after that. > > > > > "Learner" <wantnospam@email.com> wrote in message > news:MPG.1b0abaf47e6bdbac989724@msnews.microsoft.com... > > Hi, > > > > I have a simple update statement but it seems to be taking forever in > > Query Analyzer. My questions are: > > > > 1) Shoudl there be any performance difference if I execute the above > > update statement from Query Analyzer or via a DTS (i.e. by executing a > > 'SQL TASK') > > > > 2) How is the execution time effected if I have defined indexes? I think > > this would slow things down but would request detailed feedback from the > > experienced users > > > > -- > > Thanks. > >
- Next message: Amol Kasbekar: "About SSPI"
- Previous message: AJACode: "Cross Tab In SQL 2K ??"
- In reply to: Uri Dimant: "Re: Question about performance of an update statement"
- Next in thread: Learner: "Re: Question about performance of an update statement"
- Reply: Learner: "Re: Question about performance of an update statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
