Huge Db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Huge Db

Author

Topic

reenz
Starting Member

29 Posts

Posted - 2006-03-22 : 09:19:50

I have a huge database table checkpoint_hold with no unique id/ key (don't ask me y..but it was created donkey years ago. Bad db design but we cant scrape it hence we are moving it to ms sql and hoping to clean it up ever nite). To give it a unique id, i need to match a particular field of it to another huge table ship_detail to get its id.

checkpoint_hold table also have a field which holds multiple concatenated data which i am interested in. However, they are in different format and hence i used different if statements to grab what i want and pump them into different tables. I wrote a stored procedure txt_split using cursors just to get data out from that field.

I have a main stored proc , using cursor, to match the id with ship_detail and then call the stored proc txt_split for data extraction from that field and finally end by inserting the current finished processing row into the final checkpoints table. If no id can be matched, that row is dropped into another table. Time is crucial factor here and i would like the best response timeif possible. Is there any way to implement commit the transaction say after 4000 records are checked?

Currently it is checking and inserting like 1000 records per minute. However, i have like 3mil records in the table (that will take days!) which is to be updated every midnight and i would like it to finish in like 2 hrs. Any better suggestions? Could it be because of me using cursor? But i cant think of any better ways to do it.. =(

I have another stored proc for another huge table to match its id with ship_detail as well. No other fields manipulation. And it's taking similar timing!! I only have 4 hrs to complete id matching for 3 tables and data field manipulation and yet each table is taking days!! Help needed desperately!!!! =( =(

My stored proc is as follow

CREATE PROCEDURE [dbo].[sp_convertCheckpoint] AS

SET NOCOUNT ON

DECLARE @gen_key int, @gen_awb numeric, @gen_id smallint, @gen_date datetime,
@gen_time varchar(20), @gen_isogmt smallint, @gen_stn char(3), @gen_sub char(3),
@gen_cnum char(4), @gen_rte char(6), @gen_cycl char(2), @gen_ckpt varchar(100),
@gen_from char(10), @gen_svsn smallint, @gen_cvsn smallint, @gen_dtmadd int,
@gen_dtmfl int

DECLARE read_cursor CURSOR FOR
SELECT gen_key, gen_awb, gen_id, gen_date, gen_time, gen_isogmt, gen_stn, gen_sub,
gen_cnum, gen_rte, gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl
FROM dbo.checkpoint_hold

OPEN read_cursor

FETCH NEXT FROM read_cursor
INTO @gen_key, @gen_awb, @gen_id, @gen_date, @gen_time, @gen_isogmt, @gen_stn,
@gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,@gen_from, @gen_svsn,
@gen_cvsn, @gen_dtmadd, @gen_dtmfl

WHILE @@FETCH_STATUS = 0
BEGIN

IF (@gen_id = '42' AND SUBSTRING(@gen_ckpt,7,2)='DF')
BEGIN
PRINT 'do 42'
INSERT INTO [dbo].[checkpoint] (gen_key, gen_awb, gen_id, gen_date,
gen_time, gen_datetime, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte,
gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl, flight_no, flight_date, mawb)
VALUES(@gen_key, @gen_awb, @gen_id, @gen_date, @gen_time,
@gen_date + ' ' + CONVERT(DATETIME,@gen_time,108), @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,@gen_from, @gen_svsn,
@gen_cvsn, @gen_dtmadd, @gen_dtmfl, SUBSTRING(@gen_ckpt, 18, 7),
SUBSTRING(@gen_ckpt, 26,4), SUBSTRING(@gen_ckpt, 31, 11))
END
ELSE
BEGIN
IF (@gen_id = '18' OR @gen_id='40')
BEGIN
PRINT 'do 18-40'
INSERT INTO [dbo].[checkpoint] (gen_key, gen_awb, gen_id, gen_date,
gen_time, gen_datetime, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte,
gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl, del_date, del_time, signatory)
VALUES(@gen_key, @gen_awb, @gen_id,
@gen_date, @gen_time, @gen_date + ' ' + CONVERT(DATETIME,@gen_time,108), @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl,
@gen_date, @gen_time, LEFT(@gen_ckpt,12))
END

ELSE
BEGIN
PRINT 'reen'
INSERT INTO [dbo].[checkpoint] (gen_key, gen_awb, gen_id, gen_date,
gen_time, gen_datetime, gen_isogmt, gen_stn, gen_sub, gen_cnum, gen_rte,
gen_cycl, gen_ckpt, gen_from, gen_svsn, gen_cvsn, gen_dtmadd,
gen_dtmfl)
VALUES(@gen_key, @gen_awb, @gen_id,
@gen_date, @gen_time,@gen_date + ' ' + CONVERT(DATETIME,@gen_time,108), @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl)
END
END

FETCH NEXT FROM read_cursor
INTO @gen_key, @gen_awb, @gen_id, @gen_date, @gen_time, @gen_isogmt,
@gen_stn, @gen_sub, @gen_cnum, @gen_rte, @gen_cycl, @gen_ckpt,
@gen_from, @gen_svsn, @gen_cvsn, @gen_dtmadd, @gen_dtmfl

END

CLOSE read_cursor
DEALLOCATE read_cursor
GO

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-22 : 09:32:55

That is one ugly design.

If you want to process smaller batches, then add a TOP 4000 clause to your code and place the logic in a loop until it completes.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-22 : 12:40:44

Try to move away from cursors...you should be able to re-code your insert statements with "CASE" statements and take advantage of SET-BASED processing, which is where SQL really comes into it's own w.r.t performance.

ADD in SET ROWCOUNT values inside a BEGIN/END transaction set and wrap that inside a WHILE loop should minimsie your transaction ,logging and allow some element of re-startability (if that's a legal word)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources