hi i have one table on local server patientSickDetail and same table on other server patientSickDetailHistory with same no of columns i want to update previous data daily and insert new records in patientSickDetailHistory from local server using remote query the size of both table increase daily now a days it is about 12GB.While during inseration of new records in patientSickDetailHistory it will take no time and insert But updating prevoius data in patientSickDetailHistory query take much time.Here is my Query
WITH Patient (VisitPrescID, Quantity,Issued,IssuedDate) AS ( SELECT VisitPrescID, Quantity,Issued,IssuedDate FROM patientSickDetail Where Issued IS NOT NULL AND Quantity IS NOT NULL ) UPDATE [REMOTE_SERVER].[REMOTE_DB].dbo.patientSickDetailHistory SET patientSickDetailHistory.Quantity= Patient.Quantity, patientSickDetailHistory.Issued=Patient.Issued, patientSickDetailHistory.IssuedDate=Patient.IssuedDate FROM Patient WHere patientSickDetailHistory.VisitPrescID=Patient.VisitPrescID
Select Query will take almost about 3 to 4 minutes for large data result other wise it will go smooth with seconds . while using cte i try to update it will take 16 minutes or 30 minutes for different number of records.VisitPrescID is also clustered index in both tables. total number of records updated will be about 1000 rows or 2000 rows daily. it is a good way to update or not if any one know any other way please guide me how to make update fast.Thanks in Advance.
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)