Re: Update time
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/17/04
- Next message: SQL Apprentice: "Re: how to change existing tables from primary to secondary filegroups?"
- Previous message: Dan Guzman: "Re: Print synchronous?"
- In reply to: DWalker: "Re: Update time"
- Next in thread: DWalker: "Re: Update time"
- Reply: DWalker: "Re: Update time"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 17 Nov 2004 17:57:40 -0600
Because you are not doing the same thing in each case, right?
Update Positions
Set Sec_class = null
<>
Update Positions
Set Sec_class = null
Where Position_Date = '20040101'
Update Positions
Set Sec_class = null
Where Position_Date = '20040102'
...
Update Positions
Set Sec_class = null
Where Position_Date = <last date>
Because you are not within a transaction. If setting Sec_class = null fails
for '20040401' then you are not going to rollback anything other than those
rows on '20040401'. Not ones previous. So thes modifications can be
flushed to the hard disk permanently and not be worried about again. On the
other hand, SQL Server must prepare to rollback if any row fails. This
overhead, plus the increasing lock count cause things to start to slow down
exponentially as you hit the hardware limitations of your computer. If you
wrap your updates in:
BEGIN TRANSACTION
...
COMMIT TRANSACTION
You may get the same sort of performance. I hope this makes sense because
it is really a very important concept. Every single statement, or every
single group of statements in a transaction have to occur together, and they
have to be isolated from everyone else, so the bigger the load, the harder
it is to do this.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net 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 may be ignored :) "DWalker" <None> wrote in message news:%2353OD8OzEHA.1300@TK2MSFTNGP14.phx.gbl... > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in > news:OMfQjDOzEHA.3416@TK2MSFTNGP09.phx.gbl: > >> SQL Server can, but can the hardware? I had SQL Server on one of my >> home computers that had 128 MB of RAM and one single disk drive. I >> could put a 10 gig database on there, and update every row in a 50 >> million row table, but how long should this take? >> >> Uri's suggestion basically was a very common suggestion to balance >> inadequate resources versus larger needs. >> > > A followup -- yes, the hardware can, if it's done a batch at a time. > The odd thing is that SQL server picks a bad way to do it if it's not > batched. I don't think the size of the log file is the problem; it > seems rather like SQL is picking a bad execution plan to update the > whole table at once. If *I* can figure out that updating a small batch > at a time is faster, why can't the fancy Query Optimizer figure out the > same thing, and do batching behind the scenes? > > David Walker
- Next message: SQL Apprentice: "Re: how to change existing tables from primary to secondary filegroups?"
- Previous message: Dan Guzman: "Re: Print synchronous?"
- In reply to: DWalker: "Re: Update time"
- Next in thread: DWalker: "Re: Update time"
- Reply: DWalker: "Re: Update time"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
