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

quote:

---

Sometimes.....it works better to drop all indices, load the data, then add back the indices. BUT only volume testing would prove this approach.

---

quote:

---

You may also want to (re)view your transaction logging approach. Would it be faster to re-load the data than log the data and rewind. Note that the TRUNCATE command is a(n) (partially) UNLOGGED transactional event. Therefore I'm not sure if there's any point in it being inside your BEGIN/COMMIT.

---

quote:

---

You may also wish to investigate using the BCP utility...for dataloads it SHould be faster than plain vanilla "insert into"s.....not sure if it works with OPENQUERY.

---

quote:

---

Qualifying tables with the "owner" is also good practice (from a security+speed viewpoint)....but probably not a performance win in this instance.

---

quote:

---

What's the percieved value of the DBREINDEX if the data is "fresh reloaded" each day?

---

quote:

---

Originally posted by Kristen

If you can put an ORDER BY in the OpenQuery so that rows arrive in Clustered Index [i.e. of the receiving database] order that will ensure that the table is built efficiently.

---

quote:

---

Drop the indexes and re-create them afterwards. You are currently doing all the index work twice - once at each row insert, and then you are throwing all that away and making a new index with DBREINDEX.

"... to add other indexes without needed to re-visit the code"

---