MSSMS与Windows客户端的查询时间相差很大
MSSMS与Windows客户端的查询时间相差很大
提问于 2018-06-21 13:26:43
我开发了一个WPF客户端，它使用Azure SQL数据库查询发货发票。
客户一直抱怨装运清单的装载时间很长，我发现，对于每一批货物，应用程序都会查询Azure SQL数据库，以获取货物上的发票，这需要时间。
这些查询是在每次发货时都在一个foreach循环中进行的，并且没有其他方法可以做到这一点，因为发货是通过过滤客户机从way服务加载的。发票表没有要使用筛选器的列。
为了检验这个，我做了个折叠式：
- 创建一个在100次发货上运行动态sql的TSQL脚本，以获取发票并在Server管理演播室中执行它。对100批货件中的每一批执行动态SQL。我记录了每次发货的动态sql执行之前和动态exeutionExecutiontime之后的时间为0毫秒，这足够快。
- 在C#中创建一个控制台应用程序，收集来自Azure的100批货件，将它们存储在一个列表中，然后运行一个查询，在列表上的前循环中获取发票。每批货物的执行时间从43毫秒到58毫秒不等，这是很长的时间。
- 为了进行基准测试，我在网络中的一个自己的SQL服务器上创建了发票表，从Azure SQLdatabase复制数据并运行控制台应用程序。每批货物的执行时间在0到15毫秒之间，足够快。 所以我的问题是，在微软SQLServer管理工作室的循环中运行100个查询与在控制台应用程序中运行100个查询之间怎么会有如此巨大的区别？MSSMS有神奇的连接吗？
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数据库表：
/****** 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
--Data for the table
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'
--SQL Stored procedure to get invoices(@Dbg is to get one invoice only)
/****** 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回答 1
Stack Overflow用户
发布于 2018-06-22 20:22:29
这太浪费了！
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");为什么在每个循环中都要将开销作为一个新命令，而不是保留该命令呢？
public static void SQLLoop()
{
List<string> shipments = new List<string>();
string sql = "adsffo;hkjasd";
using (SqlConnection cnn = new SqlConnection("asfd"))
{
cnn.Open();
using (SqlCommand cmd = new SqlCommand(sql, cnn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
List<res> results = new List<res>();
cmd.CommandType = System.Data.CommandType.StoredProcedure;;
cmd.Parameters.Add("@ShipmentId", SqlDbType.VarChar, 100);
cmd.Parameters.AddWithValue("@OfficeId", "swe");
cmd.Parameters.AddWithValue("@Dbg", "1");
foreach (string shipment in shipments)
{
res r = new res(shipment, DateTime.Now);
cmd.Parameters["@ShipmentId"].Value = shipment;
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());
}
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50969749
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号