I am developing an overnight batch job and do not know the best way to approach this in order to obtain the best overall performance.
I am looking to reconcile remote tables (from a non native database) with local SQL Server tables. Mostly its going to involve single table to table join but the volumes of data I am reconciling is fairly large (totalling over 20Gb of remote data and 90Gb of local data), so I cannot store all the remote tables locally at the same time due to space limitations. Sometimes it will also use remote views instead of tables. The length of the overnight job should not matter unless it runs over and clashs with normal daytime server activity.
I have devised two methods of approaching this and have tried to summarise my perceived pros and cons below.
1. Step-by-step approach
Create linked servers to remote Ingres database
Copy data for say 5/40 tables locally and create indexes on these table.
Perform the reconciliation
Truncate local tables (with remote data in) and repeat for next set of 5 tables.
PROS : Works on data locally. Affects network performance for less time than option 2 (whilst it is fetching all remote data)
CONS: Requires some space to store remote data locally.
2. Joins directly on linked servers
Create linked servers to remote Ingres database
Perform reconciliation and output results to local tables.
PROS : doesnt require any space to store data locally (tempdb will probably be just as large as in option 1)
CONS: network performance ? execution plan performance ?
So please could you help with the following
a) Could people correct any incorrect assumptions Ive made and also perhaps suggest the better performing solution.
b) Would creating indexed views as a sort of remote alias ( on a select * from linkedserver..remotetable view) on each linked server table assist performance of step 2?
I dont have access to the remote server at this, so am unable to perform a test of the two methods. Any suggestions much appreciated. Thanks in advance !
J
Cheers
John (Sudmill)
I am looking to reconcile remote tables (from a non native database) with local SQL Server tables. Mostly its going to involve single table to table join but the volumes of data I am reconciling is fairly large (totalling over 20Gb of remote data and 90Gb of local data), so I cannot store all the remote tables locally at the same time due to space limitations. Sometimes it will also use remote views instead of tables. The length of the overnight job should not matter unless it runs over and clashs with normal daytime server activity.
I have devised two methods of approaching this and have tried to summarise my perceived pros and cons below.
1. Step-by-step approach
Create linked servers to remote Ingres database
Copy data for say 5/40 tables locally and create indexes on these table.
Perform the reconciliation
Truncate local tables (with remote data in) and repeat for next set of 5 tables.
PROS : Works on data locally. Affects network performance for less time than option 2 (whilst it is fetching all remote data)
CONS: Requires some space to store remote data locally.
2. Joins directly on linked servers
Create linked servers to remote Ingres database
Perform reconciliation and output results to local tables.
PROS : doesnt require any space to store data locally (tempdb will probably be just as large as in option 1)
CONS: network performance ? execution plan performance ?
So please could you help with the following
a) Could people correct any incorrect assumptions Ive made and also perhaps suggest the better performing solution.
b) Would creating indexed views as a sort of remote alias ( on a select * from linkedserver..remotetable view) on each linked server table assist performance of step 2?
I dont have access to the remote server at this, so am unable to perform a test of the two methods. Any suggestions much appreciated. Thanks in advance !
J
Cheers
John (Sudmill)