470,596 Members | 1,528 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Large Data Extract - Improvements?

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):

Expand|Select|Wrap|Line Numbers
  1. Begin Transaction
  3. truncate table arista_caclient
  4. truncate table arista_camatgrp
  5. truncate table arista_camatter
  6. truncate table arista_cabilhis
  7. truncate table arista_cablaloc
  8. truncate table arista_cafintrn
  9. truncate table arista_cadescrp
  10. truncate table arista_catimtrn
  11. truncate table arista_cafeextn
  12. truncate table arista_fmsaddr
  13. truncate table arista_cabilloi
  14. truncate table arista_caferate
  16. INSERT INTO arista_caclient SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caclient WHERE cl_datopn>=''01/01/1900'' OR cl_datopn is null')
  17. 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)')
  18. INSERT INTO arista_camatter SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM camatter WHERE (mt_estcmp>=''01/01/1900'' OR mt_estcmp is null)')
  19. 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)')
  20. INSERT INTO arista_cablaloc SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cablaloc')
  21. INSERT INTO arista_cafintrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafintrn WHERE (tr_trdate>=''01/01/1900'' OR tr_trdate is null)')
  22. INSERT INTO arista_cadescrp SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cadescrp')
  23. INSERT INTO arista_catimtrn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM catimtrn WHERE (tt_trndat>=''01/01/1900'' OR tt_trndat is null)')
  24. INSERT INTO arista_cafeextn SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cafeextn')
  25. INSERT INTO arista_fmsaddr SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM fmsaddr')
  26. INSERT INTO arista_cabilloi SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM cabilloi')
  27. INSERT INTO arista_caferate SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caferate')
  29. DBCC DBreindex (arista_caclient)
  30. DBCC DBreindex (arista_camatgrp)
  31. DBCC DBreindex (arista_camatter)
  32. DBCC DBreindex (arista_cablaloc)
  33. DBCC DBreindex (arista_catimtrn)
  34. DBCC DBreindex (arista_cafintrn)
  35. DBCC DBreindex (arista_cadescrp)
  36. DBCC DBreindex (arista_cafeextn)
  37. DBCC DBreindex (arista_fmsaddr)
  38. DBCC DBreindex (arista_cabilloi)
  39. DBCC DBreindex (arista_caferate)
  41. If @@error <> 0
  42.     Rollback Transaction
  43. 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!
Oct 30 '07 #1
1 1681
1,017 Expert 512MB
Try something like this for each table:

Expand|Select|Wrap|Line Numbers
  1. Begin Tran
  2. INSERT INTO arista_caclient SELECT * FROM OPENQUERY(arista_ODBClink,'SELECT * FROM caclient WHERE cl_datopn>=''01/01/1900'' OR cl_datopn is null')
  3. If @@error <> 0
  4.   BEGIN
  5.     Rollback Tran
  6.     Insert into some table (status, date) values (‘Failed insert into arista_caclient’, getdate())
  7.   END
  8. ELSE
  9.   BEGIN
  10.     Commit Tran
  11.     Insert into some table (status, date) values (‘success inserting into arista_caclient’, getdate())
  13.   END
  15. Begin Tran
  16. 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)')
  17. If @@error <> 0
  18.   BEGIN
  19.     Rollback Tran
  20.     Insert into some table (status, date) values (‘Failed insert into arista_camatgrp’, getdate())
  21. END
  22. Else 
  23.   BEGIN
  24.     Commit Tran
  25.     Insert into some table (status, date) values (‘success inserting into arista_camatgrp’, getdate())
  27.   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.
Nov 4 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Richard Webb | last post: by
6 posts views Thread by jcrouse | last post: by
2 posts views Thread by David Garamond | last post: by
reply views Thread by Brian Cole | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.