Click here to Skip to main content
15,892,927 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

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.

Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900