Can Azure SQL Run Complex Queries?

Question

Hi All

I am very new to both SQL and Azure.

I use Azure SQL into an Access frontend, currently we have a set of very complex queries that run in Access using the data from SQL backend.

But these take awhile to run in Access, and was hoping to move that processing to Azure if possible?

Its a bunch of SELECT queries combined with UNION queries that eventuate in one all emcompassing UNION query.

Is this possible to migrate to Azure?

Thanks

Answer 1

Your question is a little bit vague, but on SQL Azure as on-premise SQL Server you can use e.g. stored procedure for complex code; the front end then only have to call that SP. Additional advantage is that you maintain the code centralized in the SQL Azure database instead of client side.

See Create a Stored Procedure and CREATE PROCEDURE (Transact-SQL)

Answer 2

Ok sounds good, I'm just hoping it can handle what I need, below is the run down:

The final query is Master_Data2:

SELECT [Master_Data].[ID], [Master_Data].[POL Name], [Master_Data].[POD Name], [Master_Data].[Carrier], [Master_Data].[Contract Type], [Master_Data].[Contract], [Master_Data].[20GP All In], [Master_Data].[40GP All In], [Master_Data].[40HC All In], [Master_Data].[Valid From], [Master_Data].[Valid To], [Master_Data].[Transit], [Master_Data].[Direct], [Master_Data].[Notes]
FROM [Master_Data]

UNION 

SELECT FRT_Table.ID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, FRT_Table.[20GP Cost], FRT_Table.[40GP Cost], FRT_Table.[40HC Cost], FRT_Table.[Valid From], FRT_Table.[Valid To], Transits_Table.Transit, Transits_Table.Direct, FRT_Table.Notes
FROM Transits_Table RIGHT JOIN FRT_Table ON (Transits_Table.POLName = FRT_Table.[POL Name]) AND (Transits_Table.PODName = FRT_Table.[POD Name]) AND (Transits_Table.Carrier = FRT_Table.Carrier)
WHERE (((FRT_Table.ID) Not In (SELECT Master_Data.ID FROM Master_Data) And (FRT_Table.ID) Not In (SELECT Master_Data_Offset.ID FROM Master_Data_Offset)));


UNION SELECT Master_Data_Offset.ID, Master_Data_Offset.[POL Name], Master_Data_Offset.[POD Name], Master_Data_Offset.Carrier, Master_Data_Offset.[Contract Type], Master_Data_Offset.Contract, Master_Data_Offset.[20GP All In], Master_Data_Offset.[40GP All In], Master_Data_Offset.[40HC All In], Master_Data_Offset.[Valid From], Master_Data_Offset.[Valid To], Master_Data_Offset.Transit, Master_Data_Offset.Direct, Master_Data_Offset.Notes
FROM Master_Data_Offset;

As you can see that uses two other queries as below:

Master_Data:

SELECT t1.ID, t1.[POL Name], t1.[POD Name], t1.Carrier, t1.[Contract Type], t1.Contract, [20GP Cost]+[20GP BAF]+[20GP GRI]+[20GP PSS]+[20GP MISC] AS [20GP All In], [40GP Cost]+[40GP BAF]+[40GP GRI]+[40GP PSS]+[40GP MISC] AS [40GP All In], [40HC Cost]+[40HC BAF]+[40HC GRI]+[40HC PSS]+[40HC MISC] AS [40HC All In], t1.[20GP Cost], t1.[40GP Cost], t1.[40HC Cost], Transits_Table.Transit, Transits_Table.Direct, t1.Notes, t2.[20GP BAF], t2.[20GP GRI], t2.[20GP PSS], t2.[20GP MISC], t2.[40GP BAF], t2.[40GP GRI], t2.[40GP PSS], t2.[40GP MISC], t2.[40HC BAF], t2.[40HC GRI], t2.[40HC PSS], t2.[40HC MISC], t2.Notes AS FRT_Notes, ([Valid To]-[Valid From])+1 AS Days, IIf([t1].[Valid From]>[t2].[Valid From],[t1].[Valid From],[t2].[Valid From]) AS [Valid From], IIf([t1].[Valid To]<[t2].[Valid To],[t1].[Valid To],[t2].[Valid To]) AS [Valid To]
FROM (FRT_Table AS t1 INNER JOIN FRT_Additionals_Table AS t2 ON t1.[Carrier] = t2.[Carrier]) LEFT JOIN Transits_Table ON (t1.[POL Name] = Transits_Table.[POLName]) AND (t1.[POD Name] = Transits_Table.[PODName]) AND (t1.Carrier = Transits_Table.[Carrier])
WHERE (((t1.[Valid From]) Between [t2].[Valid From] And [t2].[Valid To])) OR (((t1.[Valid To]) Between [t2].[Valid From] And [t2].[Valid To]));

And Master_Data_Offset:

SELECT t1.ID, t1.[POL Name], t1.[POD Name], t1.Carrier, t1.[Contract Type], t1.Contract, t1.[20GP Cost] AS [20GP All In], t1.[40GP Cost] AS [40GP All In], t1.[40HC Cost] AS [40HC All In], t1.[20GP Cost], t1.[40GP Cost], t1.[40HC Cost], Transits_Table.Transit, Transits_Table.Direct, t1.Notes, t1.[Valid From] AS [qValid From], t1.[Valid To] AS [qValid To], t2.[20GP BAF], t2.[20GP GRI], t2.[20GP PSS], t2.[20GP MISC], t2.[40GP BAF], t2.[40GP GRI], t2.[40GP PSS], t2.[40GP MISC], t2.[40HC BAF], t2.[40HC GRI], t2.[40HC PSS], t2.[40HC MISC], t2.Notes AS FRT_Notes, ([Valid To]-[Valid From])+1 AS Days, t1.[Valid From] AS [Valid From], [t2].[Valid From]-1 AS [Valid To]
FROM Transits_Table RIGHT JOIN (FRT_Table AS t1 INNER JOIN FRT_Additionals_Table AS t2 ON t1.Carrier = t2.Carrier) ON (Transits_Table.Carrier = t1.Carrier) AND (Transits_Table.PODName = t1.[POD Name]) AND (Transits_Table.POLName = t1.[POL Name])
WHERE (((t1.[Valid To])>=[t2].[Valid To]) AND ((t1.[Valid From])<=[t2].[Valid From]) AND (([t2].[Valid From]-1)>=[t1].[Valid From]));

UNION SELECT t1.ID, t1.[POL Name], t1.[POD Name], t1.Carrier, t1.[Contract Type], t1.Contract, [20GP Cost]+[20GP BAF]+[20GP GRI]+[20GP PSS]+[20GP MISC] AS [20GP All In], [40GP Cost]+[40GP BAF]+[40GP GRI]+[40GP PSS]+[40GP MISC] AS [40GP All In], [40HC Cost]+[40HC BAF]+[40HC GRI]+[40HC PSS]+[40HC MISC] AS [40HC All In], t1.[20GP Cost], t1.[40GP Cost], t1.[40HC Cost], Transits_Table.Transit, Transits_Table.Direct, t1.Notes, t1.[Valid From] AS [qValid From], t1.[Valid To] AS [qValid To], t2.[20GP BAF], t2.[20GP GRI], t2.[20GP PSS], t2.[20GP MISC], t2.[40GP BAF], t2.[40GP GRI], t2.[40GP PSS], t2.[40GP MISC], t2.[40HC BAF], t2.[40HC GRI], t2.[40HC PSS], t2.[40HC MISC], t2.Notes AS FRT_Notes, ([t2].[Valid To]-[t2].[Valid From])+1 AS Days, t2.[Valid From], t2.[Valid To]
FROM Transits_Table RIGHT JOIN (FRT_Table AS t1 INNER JOIN FRT_Additionals_Table AS t2 ON t1.Carrier = t2.Carrier) ON (Transits_Table.POLName = t1.[POL Name]) AND (Transits_Table.PODName = t1.[POD Name]) AND (Transits_Table.Carrier = t1.Carrier)
WHERE (((t1.[Valid From])<[t2].[Valid From]) AND ((t1.[Valid To])>[t2].[Valid To]));

Those are the queries, what I ultimately need Azure to do is upon user command (which I'll likely do via VBA in Access), is to:

1. Delete all records in dbo.Master_Data2_Temp
2. Run Master_Data2 query above and put the results into dbo.Master_Data2_Temp

But I need Azure to run this at it own time, as currently its run in Access on the users computer and it can take like 50 minutes at times.

Is something like this possible?

Answer 3

You can schedule jobs in Azure SQL. How you do that depends on whether you use SQL Database or Managed Instance.

For Managed Instance, you probably want to use SQL Server Agent and create a job there.

For SQL Database, you don't have SQL Server Agent. Either use Elastic Jobs (no. GUI, I'm afraid) or Azure Automation to create Runbooks.

Answer 4

We are just using Azure SQL Database at the moment.

Sorry I have read through a few of the links and I'm completely lost, is there a way to store the three queries above in Azure SQL Database, and then using VBA in MS Access run this process (the below is the current VBA code but I need Azure to do this, not Access):

• CurrentDb.Execute "DELETE * FROM dbo_Master_Data2_Temp", dbFailOnError + dbSeeChanges
• CurrentDb.Execute "INSERT INTO dbo_Master_Data2_Temp SELECT Master_Data2.* FROM Master_Data2;", dbFailOnError

Thanks

Answer 5

Yes, I believe the environment can run complex queries.