Re: Optimizing massive update to large table
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 10/16/04
- Next message: Mary Chipman: "Re: Nulls MSAccess and SQL Server"
- Previous message: David Portas: "Re: charindex in user-defined function"
- In reply to: Sean Shanny: "Re: Optimizing massive update to large table"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 16 Oct 2004 10:28:25 -0400
Sean,
As Mike pointed out the logs still matter a great deal even though you are
in Simple mode. Every transaction still gets written to the log it just
doesn't keep it around the same way. By placing the log file on it's own
array you are more likely to increase write performance. To be honest even
750K an hour seems slow to me<g>.
-- Andrew J. Kelly SQL MVP "Sean Shanny" <shannyconsulting@earthlink.net> wrote in message news:BD9617CB.1BBAB%shannyconsulting@earthlink.net... > Andrew, > > Thank you very much, that is doing the trick. > > For your info an Apple X-Raid is a low cost, $15K for 3.5TB, IDE fibre > channel based raid system. We are connected via 2 fibre channels directly > to the box. Unfortunately we cannot combine the 14 drives at the hardware > level hence the 2 7 drive raid 5 sets that are then striped at the OS > level > giving us raid 50. > > Our db, which is a warehouse, is running in simple mode so I don't think, > but could be wrong, that the transaction logs are a big bottle neck. If > we > need to we can put a fibre switch in and throw another X-Raid system in > the > mix to allow faster performance for log writing. We should probably move > indexes to another disk subsystem as well. To be honest we are extremely > pleased with the performance of SQL Server so far. Reports that took > 20-25 > minutes to run on the old system, exact same hardware, now take 6 minutes, > only changes in the code dealt with SQL issues like date and string > functions which are specific to DB vendors. > > I am have a test system at home, same raid box but less drives and I am > getting about 750k updates an hour which I can deal with. :-) > > Thanks again. > > --sean > > On 10/15/04 10:59 PM, in article eLR7gwysEHA.1272@TK2MSFTNGP12.phx.gbl, > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote: > >> I have no idea what an Apple X-Raid is but striping two Raid 5's at hte >> OS >> level does not give me a warm fuzzy feeling<g>. Where is the log file? >> For >> optimal performance for larges writes like this you really need it on >> it's >> own RAID 1 array. It would help to see the actual DDL for the table >> including indexes. Are you saying there is no clustered index not even >> on >> the PK? Your batches are still probably too high for peak performance >> with >> that configuration. Try batches of 10K and if that works move up and see >> how it goes. Your update statement as is will always update the full >> amount >> of rows so your batch commit size is useless. You are probably going to >> have to use a subselect with TOP or maybe even temp tables. If you had a >> clustered index on the PK it might be easier but here are some things to >> try. >> >> Make sure that you have a clustered index on wh_tmp_fixed_locations on >> the >> ID column. >> >> CREATE TABLE #temp ([PKID] INT NOT NULL, [location_key] INT, >> [content_key] >> INT, CONSTRAINT ID_PK PRIMARY KEY ([PKID]) ) >> >> WHILE 1 = 1 >> BEGIN >> TRUNCATE TABLE #temp >> >> INSERT INTO #Temp ([PKID], location_key, content_key ) >> SELECT TOP 10000 b.ID, b.location_key, b.content_key >> FROM wh_tmp_fixed_locations >> ORDER BY b.ID >> >> IF @@ROWCOUNT = 0 >> BREAK >> >> Update pv >> Set location_key = s.location_key, >> content_key = s.content_key >> From f_pageviews pv >> Join #temp AS s on (pv.id = s.[PKID]) >> >> DELETE FROM wh_tmp_fixed_locations WHERE id IN ( SELECT [PKID] FROM >> #temp) >> END >> >> As always test before trying on production. > > -- > >
- Next message: Mary Chipman: "Re: Nulls MSAccess and SQL Server"
- Previous message: David Portas: "Re: charindex in user-defined function"
- In reply to: Sean Shanny: "Re: Optimizing massive update to large table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
