speed problem

[junkmail]

I am trying to upload records from a view into another table only when the record does not exist in the second table. The table being inserted into is a linked server on godaddy and the view is local. Both have hundreds of thousand records. below is what I am currently doing but it takes so long it eventually times out. one thing to note is that typically there would only be 10 to 20 thousand records needed to upload per day.

insert into table b (dateloaded,origzip,opcode,scandate,mailzip,barcodeid,serviceid,mailerid,uniqueid,jobid,scandataid)
select sd.dateloaded,sd.origzip,sd.opcode,sd.scandate,sd.mailzip,sd.barcodeid,sd.serviceid,sd.mailerid,sd.uniqueid,sd.jobid,sd.id from table_view sd
where sd.id not in (select scandataid from table b)

 

[gmmastros]

Are you running this code locally in the database that contains the view?

How long does it take for this code to run?

Create Table #RemoteTable(ScanDataId Int Primary Key)

Insert Into #RemoteTable(ScanDataId)
Select ScanDataId From Table b

Drop Table #RemoteTable

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

Yes, I am running it locally.

 

[gmmastros]

There are various ways you can speed this up.

It could be that uploading 20k rows is the really slow part. This would depend on the amount of data exists for each row, and the speed of your internet connection.

It could also be that determining the rows that should be uploaded is the slow part.

By running the code I showed earlier, it should give a good indication if the "Determining the rows" part is slow or not.

Basically, if the size of the data you are uploading is causing the problem, then you could try uploading in batches. If the problem is determining which rows to upload, you could try adding a flag to each row that is in your local database for "Uploaded". Then you wouldn't even need to check what is in the remote server, you simply upload things that are not marked as uploaded.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

Yes, Thanks for your suggestions.