时间戳

首页 > 解决方案 > MSSMS 和 Windows 客户端的查询时间大不同

c# - MSSMS 和 Windows 客户端的查询时间大不同

问题描述

我开发了一个 WPF 客户端，它使用 Azure SQL 数据库来查询发货发票。

客户一直在抱怨货件清单的加载时间过长，我发现对于每个货件，应用程序都会查询 Azure SQL 数据库以获取货件发票，这需要时间。

查询是在每个货物的 foreach 循环中进行的，并且没有其他方法可以做到这一点，因为货物是通过在客户端中过滤从 Web 服务加载的。发票表没有要使用过滤器的列。

因此，为了测试这一点，我做了以下操作：

  1. 创建了一个 TSQL 脚本，该脚本在 100 批货物上运行动态 sql 以获取发票并在 SQL Server 管理工作室中执行它。对 100 个装运中的每一个都执行动态 SQL。我记录了动态sql执行之前和动态sql执行之后的时间，每次发货的执行时间都是0毫秒，这已经足够快了。

  2. 在 C# 中创建了一个控制台应用程序，该应用程序从 Azure 收集 100 批货物，将它们存储在一个列表中，并运行查询以在列表上的 foreachloop 中获取发票。每批货物的执行时间在 43 到 58 毫秒之间变化，这太长了。

  3. 为了进行基准测试，我在网络中我们自己的一台 SQL 服务器上创建了 Invoice 表，从 Azure SQLdatabase 复制数据并运行控制台应用程序。每批货物的执行时间在 0 到 15 毫秒之间变化，这已经足够快了。

    所以我的问题是，在 Microsoft SQLServer 管理工作室与控制台应用程序中循环运行 100 个查询之间怎么会有如此巨大的差异？MSSMS 有神奇的联系吗？

示例代码：

//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

标签： c#sql-serverperformanceazuretsql

解决方案

这是浪费！ 

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());
            }
        }
    }
}

推荐阅读