I have a query that reports back to me how much time elapsed for records to be added. I also in this query track the size of an image that was added to the database. From what I can see it looks like it is returning the whole image from the linked server and then calculating the size. Is there an easy way I can get the calculation of size to occur on the other side of the link so that the return can be faster?
SELECT [Date] = CAST(DATEADD(DAY,-@DaysBack,GETDATE()) AS DATE) ,NOTES = N.RPMID ,TimeTaken = DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate) ,PictureSize = SUM(CAST(DATALENGTH(SI.[Image])AS BIGINT)) ,RecordsAdded = CAST(COUNT(*) AS BIGINT) FROM DBServer1.DB1.dbo.Notes AS N WITH (NOLOCK) INNER JOIN DBServer1.DB1.dbo.NoteDistribution AS ND WITH (NOLOCK) ON N.Notes=ND.Notes INNER JOIN dbo.Submission_Notes AS SN WITH (NOLOCK) ON SN.RetailPlanGuid=N.RPMID LEFT JOIN DBServer1.DB1.dbo.SavedImage SI WITH (NOLOCK) ON SI.Notes=N.Notes WHERE 1=1 AND N.RecordStampWhenAdd > CAST(GETDATE()-@DaysBack AS DATE) AND N.RecordStampWhenAdd < CAST(GETDATE()-@DaysBack+1 AS DATE) AND DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate) > 0 GROUP BY N.RPMID ,DATEDIFF("SECOND",SN.PickedUpDate,SN.ProcessedDate)
The part in question is the picturesize that is returned. I want the calculations in there to be done on the server where the data is stored and just have the number return to me. Any help on this would be great. Thanks.