Latency on Azure SQL Server vs. Fabric Datawarehouse SQL Server

Question

Azure SQL Server vs. Fabric Datawarehouse SQL Server

I'm in the process of moving an application's data source from Azure SQL Server to a Fabric Datawarehouse. However, it seems like latency is a huge difference on those two platforms.

I have written a small test program to run some testing. Basically, it just takes 1000 round trips to the server executing a small T-SQL statement and logs the time used.

My Azure SQL Server is located in "West Europe," while my Fabric Datawarehouse SQL Server is located in "North Europe."

I have two Azure VMs, one located in "West Europe" and the other located in "North Europe."

Running from a client in West Europe:

Azure SQL Server = 2 seconds

Fabric SQL Server = 27 seconds

Running from a client in North Europe:

Azure SQL Server = 17 seconds

Fabric SQL Server = 17 seconds

So, everything is as expected (it is vital where the client program resides in terms of latency), except that Fabric querying takes 17 seconds even when my test program runs from a VM located in the same area as the Fabric Datawarehouse (North Europe in this case).

The SQL itself: "SELECT 'Hello World'" should not take more than a nanosecond (or two) to execute on both, so the difference has to be latency. And I really don't understand why the difference should be that big. Is there something that can be adjusted on the Fabric side? Anyone?

My testprogram is quite simple

    Dim CurrConnection = New SqlConnection(SQL_CONNECTION_STRING)
    With CurrConnection
        .Open()
        Dim SCommand As New SqlClient.SqlCommand
        With SCommand
            .Connection = CurrConnection
            Console.WriteLine("----------------------------CONNECTING TO SQL RUNNING 1000 HOOPS --------------------------------------------------------")
            Debug.Print(Now().ToString)
            Console.WriteLine(Now().ToString)

            For i = 1 To 1000
                .CommandText = "SELECT 'Hello World From SQL SERVER'"
                Dim p As Object = .ExecuteScalar()
                If i Mod 100 = 0 Then
                    Console.WriteLine(i & " -  " & p.ToString)
                End If
            Next
        End With
        .Close()
    End With
    Console.WriteLine(Now().ToString)
    Console.WriteLine("----------------------------FINSH CONNECTING TO SQL RUNNING 1000 CALL--------------------------------------------------------")

Tnx perove

Answer 1

Azure SQL server is built for OLTP workload, so it will run better for multiple small queries. Fabrick Wharehouse is built for OLAP workload and will be a better suite for a smaller number of queries each aggregating a large amount of data.

If your application is meant to run many small queries, each reading or updating just a few table rows, you should stick with the Azure SQL server.