I have developed a WPF client that uses a Azure SQL database to query for invoices on shipments.
The customer have been complaining on long loadtimes for the Shipment list and i have found that for each Shipment the application queries Azure SQL database for invoices on the shipment and that is what takes time.
The queries are made in a foreach loop on each shipment and there is no other way to do it since shipments are loaded from a webservice by filtering in the client. The invoice table does not have the columns to use the filter.
So to test this I did the folowing:
Created a TSQL script that runs Dynamic sql on 100 shipments to get the invoice and executed it in SQL Server management studio. Dynamic SQL is executed for each of the 100 shipments. I logged the time before the dynamic sql execution and after the dynamic sql exeutionExecutiontime for each shipment takes 0 milliseconds, which fast enough.
Created a Console application in C# that collects 100 shipments from Azure stores them in a List and the run a query to get the invoice in a foreachloop on the List. Executiontime for each shipment varies between 43 and 58 milliseconds, which is way to long.
To benchmark i created the Invoice table on one of our own SQL servers in our network, copied the data from Azure SQLdatabase and ran the console application. Executiontime for each shipment varies between 0 and 15 milliseconds, which is fast enough.
So my question is how can there be such a dramatic difference between running 100 queries in a loop in Microsoft SQLServer management studio VS a Console application? Does MSSMS have a magic connection?
Samplecode:
Console application:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;
namespace ConsoleApp8
{
class Program
{
public struct res
{
public string ShipmentId;
public DateTime St;
public DateTime Sl;
public double Exectimems;
public res(string shipmentid, DateTime st)
{
ShipmentId = shipmentid;
St = st;
Sl = new DateTime();
Exectimems = 0;
}
}
static void Main(string[] args)
{
//string connstr = "Data Source=LocalNetWorkSQLServer;Initial Catalog=Dummy;User ID=sa;Password=somepassword;MultipleActiveResultSets=True";
string connstr = "Server = tcp:Server.database.windows.net;Database=Dummy;User ID =Username@Server;Password=somepassword;Trusted_Connection=False;Encrypt=True;MultipleActiveResultSets=True;";
List<string> Shipments = new List<string>();
List<res> results = new List<res>();
SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder(connstr);
//Get all shipments to find invoices on and put them in a list.
string sql = "Select top 100 ShipmentId"
+ " From("
+ " Select distinct shipmentid"
+ " From TKL_Invoices With(readuncommitted)"
+ " Where OfficeId = 'swe') x";
using (SqlConnection cnn = new SqlConnection(connstr))
{
cnn.Open();
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
cmd.CommandType = System.Data.CommandType.Text;
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
Shipments.Add(rd["ShipmentId"].ToString());
}
}
cnn.Close();
}
//Get first invoice on each shipment
sql = "TKL_GetSavedInvoices";
using (SqlConnection cnn = new SqlConnection(connstr))
{
cnn.Open();
foreach (string Shipment in Shipments)
{
res r = new res(Shipment, DateTime.Now);
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ShipmentId", Shipment);
cmd.Parameters.AddWithValue("@OfficeId", "swe");
cmd.Parameters.AddWithValue("@Dbg", "1");
string invoice = cmd.ExecuteScalar().ToString();
r.Sl = DateTime.Now;
TimeSpan timeDiff = r.St - r.Sl;
r.Exectimems = timeDiff.TotalMilliseconds;
results.Add(r);
Console.WriteLine(Shipment, r.Exectimems.ToString());
}
}
cnn.Close();
}
//Log result to file
string prefix = "NetWorkSQLServer";
if (scb.DataSource.ToLower().Contains("tcp:"))
prefix = "Azure";
string filename = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WPF_Client_" + prefix + "_QueryResult_" + scb.DataSource.Replace(':','_') + "." + scb.InitialCatalog + ".csv");
if (File.Exists(filename))
File.Delete(filename);
StringBuilder sb = new StringBuilder();
sb.Append("ShipmentId;TimeBeforeQyery;TimeAfterQyery;QueryTimeMilliseconds");
foreach (res r in results)
{
sb.Append(Environment.NewLine)
.Append(r.ShipmentId).Append(";")
.Append(r.St.ToLongTimeString()).Append(";")
.Append(r.Sl.ToLongTimeString()).Append(";")
.Append(r.Exectimems.ToString()).Append(";");
}
File.WriteAllText(filename, sb.ToString());
Console.WriteLine("Done");
Console.ReadKey();
}
}
}
SQL database objects and data:
/****** Object: Index [ix_TKL_Invoices001] Script Date: 2018-06-21 15:12:43 ******/
DROP INDEX [ix_TKL_Invoices001] ON [dbo].[Tkl_Invoices]
GO
/****** Object: Table [dbo].[Tkl_Invoices] Script Date: 2018-06-21 15:12:43 ******/
DROP TABLE [dbo].[Tkl_Invoices]
GO
/****** Object: Table [dbo].[Tkl_Invoices] Script Date: 2018-06-21 15:12:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tkl_Invoices](
[ShipmentId] [varchar](50) NOT NULL,
[OfficeId] [varchar](50) NULL,
[InvoiceNo] [varchar](50) NOT NULL,
CONSTRAINT [PK_Tkl_Invoices] PRIMARY KEY CLUSTERED
(
[ShipmentId] ASC,
[InvoiceNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [ix_TKL_Invoices001] Script Date: 2018-06-21 15:12:43 ******/
CREATE NONCLUSTERED INDEX [ix_TKL_Invoices001] ON [dbo].[Tkl_Invoices]
(
[OfficeId] ASC,
[ShipmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Insert into Tkl_Invoices(ShipmentId, OfficeId, InvoiceNo)
Select '1511546743', 'SWE', '250279'
Union Select '1512550199', 'SWE', '1111111'
Union Select '1512554318', 'SWE', '250249'
Union Select '1601561108', 'SWE', '250279'
Union Select '1602561957', 'SWE', '249700'
Union Select '1602562876', 'SWE', '250582'
Union Select '1602566412', 'SWE', '250577'
Union Select '1602567219', 'SWE', '249699'
Union Select '1602567387', 'SWE', '250577'
Union Select '1603568831', 'SWE', '250577'
Union Select '1603569044', 'SWE', '250326'
Union Select '1603569143', 'SWE', '249846'
Union Select '1603569720', 'SWE', '249994'
Union Select '1603570257', 'SWE', '250279'
Union Select '1603570259', 'SWE', '250582'
Union Select '1603570940', 'SWE', '1234'
Union Select '1603572374', 'SWE', '250118'
Union Select '1603572376', 'SWE', '250200'
Union Select '1603572378', 'SWE', '250034'
Union Select '1603572450', 'SWE', '249923'
Union Select '1603572450', 'SWE', '250262'
Union Select '1603572601', 'SWE', '249927'
Union Select '1603572603', 'SWE', '250577'
Union Select '1603573496', 'SWE', '249701'
Union Select '1603573826', 'SWE', '249712'
Union Select '1603574012', 'SWE', '250577'
Union Select '1603574022', 'SWE', '250055'
Union Select '1603574184', 'SWE', '249888'
Union Select '1603574193', 'SWE', '249702'
Union Select '1603574195', 'SWE', '250239'
Union Select '1603574417', 'SWE', '249921'
Union Select '1603574610', 'SWE', '1000000'
Union Select '1603574652', 'SWE', '249704'
Union Select '1603574865', 'SWE', '249706'
Union Select '1603574874', 'SWE', '250109'
Union Select '1603575030', 'SWE', '250175'
Union Select '1603575032', 'SWE', '250172'
Union Select '1603575127', 'SWE', '249732'
Union Select '1603575147', 'SWE', '250155'
Union Select '1603575181', 'SWE', '250108'
Union Select '1603575531', 'SWE', '250034'
Union Select '1603575538', 'SWE', '249994'
Union Select '1603575540', 'SWE', '250168'
Union Select '1603575558', 'SWE', '250084'
Union Select '1603575562', 'SWE', '250234'
Union Select '1603575566', 'SWE', '250057'
Union Select '1603575586', 'SWE', '250034'
Union Select '1603575592', 'SWE', '250193'
Union Select '1603575594', 'SWE', '250185'
Union Select '1603575598', 'SWE', '250034'
Union Select '1603575627', 'SWE', '250080'
Union Select '1603575633', 'SWE', '250163'
Union Select '1603575635', 'SWE', '249820'
Union Select '1603575637', 'SWE', '250108'
Union Select '1603575641', 'SWE', '250034'
Union Select '1603575644', 'SWE', '250102'
Union Select '1603575646', 'SWE', '250084'
Union Select '1603575653', 'SWE', '250117'
Union Select '1603575655', 'SWE', '250117'
Union Select '1603575868', 'SWE', '250084'
Union Select '1603575872', 'SWE', '250171'
Union Select '1603575874', 'SWE', '250036'
Union Select '1603575876', 'SWE', '250036'
Union Select '1603575879', 'SWE', '250036'
Union Select '1603575881', 'SWE', '250036'
Union Select '1603575887', 'SWE', '250036'
Union Select '1603575894', 'SWE', '250081'
Union Select '1603575896', 'SWE', '250065'
Union Select '1603575905', 'SWE', '250160'
Union Select '1603575909', 'SWE', '250083'
Union Select '1603575914', 'SWE', '250186'
Union Select '1603575917', 'SWE', '250105'
Union Select '1603575919', 'SWE', '250094'
Union Select '1603575921', 'SWE', '250183'
Union Select '1603575966', 'SWE', '250104'
Union Select '1603575976', 'SWE', '250124'
Union Select '1603575984', 'SWE', '250199'
Union Select '1603575992', 'SWE', '250501'
Union Select '1603575998', 'SWE', '250170'
Union Select '1603576000', 'SWE', '250034'
Union Select '1603576183', 'SWE', '250095'
Union Select '1603576188', 'SWE', '250114'
Union Select '1603576192', 'SWE', '250159'
Union Select '1603576209', 'SWE', '250129'
Union Select '1603576268', 'SWE', '250176'
Union Select '1603576286', 'SWE', '250130'
Union Select '1603576294', 'SWE', '249715'
Union Select '1603576296', 'SWE', '249716'
Union Select '1604576489', 'SWE', '250135'
Union Select '1604576823', 'SWE', '250036'
Union Select '1604576884', 'SWE', '250039'
Union Select '1604576909', 'SWE', '250145'
Union Select '1604576913', 'SWE', '249922'
Union Select '1604576944', 'SWE', '249682'
Union Select '1604577069', 'SWE', '249717'
Union Select '1604577112', 'SWE', '250082'
Union Select '1604577121', 'SWE', '250234'
Union Select '1604577133', 'SWE', '250234'
Union Select '1604577137', 'SWE', '250234'
Union Select '1604577139', 'SWE', '250234'
/****** Object: StoredProcedure [dbo].[TKL_GetSavedInvoices] Script Date: 2018-06-21 15:23:51 ******/
DROP PROCEDURE [dbo].[TKL_GetSavedInvoices]
GO
/****** Object: StoredProcedure [dbo].[TKL_GetSavedInvoices] Script Date: 2018-06-21 15:23:51 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[TKL_GetSavedInvoices]
@ShipmentId nvarchar(50)
,@OfficeId nvarchar(255) = null
,@Dbg smallint = 0
as
begin
declare @InvoiceNo nvarchar(50)
if Exists(Select 1 From TKL_Invoices with(readuncommitted)
where ShipmentId = @ShipmentId
and OfficeId = isnull(@OfficeId, OfficeId)
)
begin
If(@dbg = 0)
begin
select
InvoiceNo
from TKL_Invoices with(readuncommitted)
where ShipmentId = @ShipmentId
and OfficeId = isnull(@OfficeId, OfficeId)
order by Head desc
OPTION ( OPTIMIZE FOR UNKNOWN )
End Else
begin
select
@InvoiceNo = InvoiceNo
from TKL_Invoices with(readuncommitted)
where ShipmentId = @ShipmentId
and OfficeId = isnull(@OfficeId, OfficeId)
order by Head desc
OPTION ( OPTIMIZE FOR UNKNOWN )
Select @InvoiceNo as InvoiceNo
End
End Else
begin
Select 'NA' as InvoiceNo
End
end
GO
Continue reading...