SQL Server self join (1 Viewer)

[llkhoutx]

I have a 100M row SQL Server table (SStb) joined against 2K local Access (Atb) table top update 3 fields in 100K rows of SStb. Atb is a lookup table. In Access, the query takes 1.5 days to run.

I'm charged with optimizing same. With DAO, the parsing takes about an hour, cycling through the SStb rows, one by one. An hour is also too long.

SStb has several user columns, there's no SQL Server scratch tables to populate the 2K lookup.

Could I somehow build a temporary SQL Server table using Access DAO/ADO to hold the 2K, 2 column lookup rows? More suscinctly, can I populate a SS temporary table using Access DAO/ADO? If so, how?

Alternatively, can I populate 2 columns or 2K rows of the 100Krows of SStb with my lookup values and create a pass through query self joining SStb 3 times on the lookup columns?

Hopefully, this is clear. :banghead: Thank you in advance for your thoughts.

 

[mdlueck]

Must you perform the query split across SQL / Access? Yuck!

If you could get it all into SQL, that would be ideal. Then you would have lots at your disposal, including doing all of that business in a Stored Procedure... be it a SP that issues multiple SQL's, or one/multiple CTE's that all feed into a final SQL. etc...

I think your lingering performance problem comes from the fact that you are using two database engines, LAN speed between computers, etc... that all is a lot of overhead.

 

[llkhoutx]

The whole idea is to update a portion of the sql server rows with Access data.:banghead:

 

[mdlueck]

I have a replication process which downloads data from an iSeries server running DB2/400 and publishes that data to SQL Server. The download completes in 30 seconds, and publishing the data to SQL Server takes 1.5 Hr! (On a good day.) Just the overhead to issue the individual ADO.Command/.Parameters for each record is that costly.