0

I'm attempting to improve performance of a set of views that we have on a local server, pulling data from another set of views on a remote Azure database.

Our system provider advised us to use ApplicationIntent=ReadOnly, but can we optimise these queries further, by using tempdb space on the remote server to create temporary tables, if read only is set?

I read this answer, which does help a little, but would appreciate any straightforward answers on this.

Please note, I'm not a DBA, so my knowledge is pretty limited. Thanks in advance.

The whole process combines SSIS and multiple views on a local SQL Server, pulling the data from the various views from the source to try and optimise the feed.

the process goes as follows...

  1. Clear down old data, removing the temporary information from the holding tables.
  2. Localise a list of keys from the source server...
INSERT INTO Source.ResourceCapture (VisitNumber) 
SELECT DISTINCT src.VISIT_NUMBER
FROM   [SOURCE.DATABASE.WINDOWS.NET].[Database].[dbo].[Composition_Questionaire] src
WHERE  src.Composition_Date >= GETDATE()-2 AND 
       src.Document_Title = 'Discharge Summary' AND 
       src.Status = 'final'
  1. Localise the rest of the data (containing the meat of the information to be passed) using a set of queries. It is the final INSERT statement that fails, due to the amount of processing required. Azure returns an error indicating that we're out of resource.
INSERT INTO [Source].[Composition_Questionaire] (
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No])
SELECT 
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No]
FROM 
    [SOURCE.DATABASE.WINDOWS.NET].[Database].[dbo].[Composition_Questionaire] src 
WHERE 
    DOCUMENT_TITLE = 'Discharge Summary' AND 
    VISIT_NUMBER COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT VisitNumber FROM Source.ResourceCapture) 

NOTE: Yes - I do know about the issue of optimising using a JOIN instead of IN, but I've tested both ways and the process still bombs after 10 minutes. I've also tried adjusting the collation from the supplier side to ours (i.e., within the IN clause), without success.

  1. Technically, everything after step 3 is irrelevant, as it all works fine on the local box.
  2. Resource keys (basically, unique identifiers) are created within the local database to feed specific values in the target tables.
INSERT INTO Core.KeyMap (ResourceID, MaxDateTime) 
SELECT      Resource_ID, MaxDateTime = MAX(CONVERT(DATETIME, Composition_Date) + CONVERT(DATETIME, Composition_Time)) 
FROM        Source.Composition_Questionaire 
WHERE       Resource_ID NOT IN (SELECT ResourceID FROM Core.KeyMap) AND 
            [Status] = 'final'
GROUP BY    Resource_ID
  1. Rebuild the resource table, removing any extraneous information.
  2. Build the final data for the primary table.
  3. Import data from another service to generate deomographic information.
  4. Map keys from the target system to the resource keys created in step 6.
  5. Mark the transferred data as complete.

The whole process worked well, initially, the longest time always taken by the transfer of information from the Azure servers to our own.

6
  • 1
    Did they say why ApplicationIntent=ReadOnly would help improve Linked Server query performance? (Spoiler alert, it probably won't.) That's a specific connection string option, typically used for Availability Group routing. There might be minute performance benefits of it, but nothing that's going to magically fix your main issues with querying across a Linked Server. Likely you're running into issues with Remote Scan operations causing all of the data to be brought across locally before being filtered. We'd need to see the actual execution plan to help you performance tune.
    – J.D.
    Jun 8, 2023 at 12:44
  • Thanks for getting back, @J.D. The supplier said that it would force the server to use the read-only replica to pull data. From what I understand of the views on the other side, however, they're extremely complicated, pulling data from JSON and other sources in the mix. I believe you're right with the remote scan issue. I'll take a look at that and see if we can optimise in anyway. What we've had to do is localise as much data as we can. However, we're dependant on comparing the data that we're pulling with what's already on our server, to minimise what we do pull (we don't want everything!)
    – Paul
    Jun 8, 2023 at 13:06
  • Are you guys using AlwaysOn Availability Groups? If you want to add the actual execution plan to your Post, we can probably confirm it's an issue with the entire dataset being pulled over before being filtered, and then suggest ways to fix / workaround that problem. Having the view definitions would be helpful too but the execution plan is most important.
    – J.D.
    Jun 8, 2023 at 13:53
  • @J.D. Thanks for that. Sadly, I only have access to our side of the view process, which I'm busy trying to optimise. The other side (the supplier views) are unavailable to us - all we can see is the output and field content. I know from what has been said by the supplier that the views do contain conversion processes from JSON to tables etc., but can't comment on the hows and whyfor's.
    – Paul
    Jun 9, 2023 at 9:04
  • @J.D. - Yes, it's a HA instance of SQL on Azure.
    – Paul
    Jun 9, 2023 at 9:04

2 Answers 2

1

had the same issue on a server, what I did was instead of making views over a remote data, I made an staging table that is updated from a view from that remote server every 5 minutes, and I directed my codes to read the table instead of the view ( in fact just used the same name for table as view and renamed the view) the query over a linked server to a remote DB, can take long to respond depending on several factors, like:

  • network performance
  • amount of data fetched
  • the locks on the source data
  • how busy the source DB or server is
  • the isolation level on source and destination location
  • type of lined server and many more

so the speed on fetching can vary and with this method, you can always have a fresh copy for yourself in Destination server. you may want to change the sync duration, to meet your business needs.

1
  • Hi @Sina, thanks. That's basically what I'm doing at the moment. What I call a resource table in my OP is exactly that - localised data. The issue has actually been with relation to the use on the IN clause in one of the queries; it's simply not performant with a linked server. Thanks for your input, though - I appreciate it.
    – Paul
    Jun 12, 2023 at 8:29
0

So after a bit of searching and testing, the most optimal way of pulling the data across was to apply the same inital criteria to the query that pulls the Resource.VisitNumber values, as per step 2 in the OP.

We do have to ensure that all data is being pulled across from the source tables (we cannot be certain as yet, as the supplier has not given us any documentation pertaining to the source table/view structures). The structures do look similar, but data usage could be different.

Each table contains a similar format (note: I cannot give detailed inormation about the table structure as we only see fields from the views, and some of these are generated from JSON data), with equally named fields and general functions. On top of this:

  • The values are split into a separate date and time
  • Some of the views bear a vertical format, whereas some are horizontal
  • Not all of the expected view fields are indexed (though I'm unsure whether this has much bearing on the speed)
  • Some of the vertically formatted tables have nested vertical formats (yep!)

It's a bit of a crazy mish-mash.

So, to recap, we converted this SQL...

INSERT INTO [Source].[Composition_Questionaire] (
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No])
SELECT 
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No]
FROM 
    [SOURCE.DATABASE.WINDOWS.NET].[Database].[dbo].[Composition_Questionaire] src 
WHERE 
    DOCUMENT_TITLE = 'Discharge Summary' AND 
    VISIT_NUMBER COLLATE SQL_Latin1_General_CP1_CI_AS IN (SELECT VisitNumber FROM Source.ResourceCapture) 

...to this...

INSERT INTO [Source].[Composition_Questionaire] (
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No])
SELECT 
    [Resource_ID],[RTR_ID],[Visit_Number],[Status],[Composition_Date],[Composition_Time],
    [Document_Title],[Document_Description],[Component_Title],[Component_Description],
    [Finalised_By],[Component],[Question],[Response],[Instance],[Row_No],[Col_No]
FROM 
    [SOURCE.DATABASE.WINDOWS.NET].[Database].[dbo].[Composition_Questionaire] src 
WHERE 
    Composition_Date >= GETDATE()-2 AND 
    Document_Title = 'Discharge Summary' AND 
    Status = 'final'

Sadly, due to our lack of knowledge with the source system, we cannot guarantee that the dates we've specified are aligned from parent to child record, so we've allowed a couple of days to try and get everything associated. As always - we need more information.

I'd like to thank everyone for their input, it has been most welcome.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.