Large Data Extract - Improvements?

[tim999]

Hi everyone,

I've been working on a large data extract and was wandering if anyone could take a look at the following code and offer any reasons why it takes such a long time compared to when i had it seperated out in steps with GO statements in a scheduled job.

It needs to:

1) delete all data on local tables
2) import all data from external source back into local tables
3) rebuild indexes on local tables

Apparently GO statements are not supposed to be used so i've gone for the transaction approach (makes sense also - either it all happens or none of it happens):

Begin Transaction
 
truncate table arista_caclient
truncate table arista_camatgrp
truncate table arista_camatter
truncate table arista_cabilhis
truncate table arista_cablaloc
truncate table arista_cafintrn
truncate table arista_cadescrp
truncate table arista_catimtrn
truncate table arista_cafeextn
truncate table arista_fmsaddr
truncate table arista_cabilloi
truncate table arista_caferate
 
INSERT INTO arista_caclient SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caclient WHERE cl_datopn>=''01/01/1900'' OR cl_datopn is null')
INSERT INTO arista_camatgrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatgrp WHERE (mg_datcls>=''01/01/1900'' OR mg_datcls is null) AND (mg_datopn>=''01/01/1900'' OR mg_datopn is null)')
INSERT INTO arista_camatter SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatter WHERE (mt_estcmp>=''01/01/1900'' OR mt_estcmp is null)')
INSERT INTO arista_cabilhis SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cabilhis WHERE (bh_bildat>=''01/01/1900'' OR bh_bildat is null) AND (bh_laspay>=''01/01/1900'' OR bh_laspay is null) AND (bh_rundat>=''01/01/1900'' OR bh_rundat is null) AND (bh_remdat>=''01/01/1900'' OR bh_remdat is null)')
INSERT INTO arista_cablaloc SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cablaloc')
INSERT INTO arista_cafintrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafintrn WHERE (tr_trdate>=''01/01/1900'' OR tr_trdate is null)')
INSERT INTO arista_cadescrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cadescrp')
INSERT INTO arista_catimtrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM catimtrn WHERE (tt_trndat>=''01/01/1900'' OR tt_trndat is null)')
INSERT INTO arista_cafeextn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafeextn')
INSERT INTO arista_fmsaddr SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM fmsaddr')
INSERT INTO arista_cabilloi SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cabilloi')
INSERT INTO arista_caferate SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caferate')
 
DBCC DBreindex (arista_caclient)
DBCC DBreindex (arista_camatgrp)
DBCC DBreindex (arista_camatter)
DBCC DBreindex (arista_cablaloc)
DBCC DBreindex (arista_catimtrn)
DBCC DBreindex (arista_cafintrn)
DBCC DBreindex (arista_cadescrp)
DBCC DBreindex (arista_cafeextn)
DBCC DBreindex (arista_fmsaddr)
DBCC DBreindex (arista_cabilloi)
DBCC DBreindex (arista_caferate)
 
If @@error <> 0
    Rollback Transaction
Commit Transaction
 

Ideally i would like to insert a line into a seperate table if it succeeds or a line into a table if it fails. Would it be best to place that INSERT command before the Rollback and Commit statements?

Is this structured correctly?

Many thanks!

[iburyak]

Try something like this for each table:

Begin Tran
INSERT INTO arista_caclient SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caclient WHERE cl_datopn>=''01/01/1900'' OR cl_datopn is null')
If @@error <> 0
  BEGIN
    Rollback Tran
    Insert into some table (status, date) values (‘Failed insert into arista_caclient’, getdate())
  END
ELSE
  BEGIN
    Commit Tran
    Insert into some table (status, date) values (‘success inserting into arista_caclient’, getdate())
 
  END
 
Begin Tran
INSERT INTO arista_camatgrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatgrp WHERE (mg_datcls>=''01/01/1900'' OR mg_datcls is null) AND (mg_datopn>=''01/01/1900'' OR mg_datopn is null)')
If @@error <> 0
  BEGIN
    Rollback Tran
    Insert into some table (status, date) values (‘Failed insert into arista_camatgrp’, getdate())
END
Else 
  BEGIN
    Commit Tran
    Insert into some table (status, date) values (‘success inserting into arista_camatgrp’, getdate())
 
  END

You had a problem because you didn’t commit after each table and if something happened you get nothing. Also your transaction log gets overloaded with uncommitted data which slows down your process.


Good Luck.