sql-server - 使用具有中等数据的表提高 SQL Server 查询性能

Question

我有一个包含 6000 条记录的表，但将来可能会更大。我附上了当前 SQL 查询的屏幕转储，该查询需要 12 到 14 秒才能加载 6000 行数据。

最常查询的列已经有索引

如何提高查询性能或修改查询性能？

CREATE TABLE [dbo].[Item]
  (
     [Srno]          [BIGINT] IDENTITY(1, 1) NOT NULL,
     [ITEMCode]      [BIGINT] NOT NULL,
     [BranchId]      [BIGINT] NOT NULL,
     [ITEM_No]       [VARCHAR](50) NULL,
     [ITEM_Desc]     [VARCHAR](200) NULL,
     [DeptId]        [BIGINT] NULL,
     [CatId]         [BIGINT] NULL,
     [SizeId]        [BIGINT] NULL,
     [CostPrice]     [DECIMAL](18, 3) NULL,
     [SalesPrice]    [DECIMAL](18, 2) NULL,
     [ITEM_InStock]  [BIGINT] NULL,
     [UserId]        [BIGINT] NULL,
     [Active]        [BIT] NOT NULL,
     [IsDeleted]     [BIT] NOT NULL,
     [Quantity]      [INT] NULL,
     [IsFavourite]   [BIT] NULL,
     [IsProductLink] [BIT] NULL,
     CONSTRAINT [PK_Item_1] PRIMARY KEY CLUSTERED ( [ITEMCode] ASC, [BranchId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  )
ON [PRIMARY]

ALTER TABLE [dbo].[Item]
  WITH CHECK ADD CONSTRAINT [FK_Item_Item] FOREIGN KEY([ITEMCode], [BranchId]) REFERENCES [dbo].[Item] ([ITEMCode], [BranchId])

GO

ALTER TABLE [dbo].[Item]
  CHECK CONSTRAINT [FK_Item_Item]

GO

ALTER TABLE [dbo].[Item]
  ADD CONSTRAINT [DF_Item_ITEM_UnitPrice] DEFAULT ((0)) FOR [CostPrice]

GO

ALTER TABLE [dbo].[Item]
  ADD CONSTRAINT [DF_Item_ITEM_RETAILPRICE] DEFAULT ((0)) FOR [SalesPrice]

GO

ALTER TABLE [dbo].[Item]
  ADD CONSTRAINT [DF_Item_ITEM_InStock] DEFAULT ((0)) FOR [ITEM_InStock]

GO

ALTER TABLE [dbo].[Item]
  ADD CONSTRAINT [DF_Item_Active] DEFAULT ((0)) FOR [Active]

GO

ALTER TABLE [dbo].[Item]
  ADD CONSTRAINT [DF_Item_DelFlag] DEFAULT ((0)) FOR [IsDeleted]

GO 

我已更新查询并删除了部门表的联接，但仍然需要10秒

   SELECT Item.ITEMCode                 AS ItemId,
       ISNULL(Item.ITEM_No, '0')     AS ItemNo,
       isnull(Item.Barcode, '')      AS Barcode,
       Item.ITEM_Desc                AS ItemName,
       isnull(item_Imagepath, '')    AS ItemImage,
       0                             AS availableQty,
       Item.SalesPrice               AS Price,
       isnull(Item.TaxApply, 0)      AS isTax,
       Item.TaxType,
       ISNULL(Item.ITM_Type, '0')    AS ITMType,
       Item.Profit_Type              AS ProfitType,
       Item.Profit_Amt               AS ProfitAmt,
       Item.CostPrice,
       ISNULL(Item.ITEM_Remarks, '') AS Remark
FROM   Item      

Answer 1

如果读取 6000 条(小)记录需要 10 秒，那么我只能假设/希望您的连接在这里引入了很多延迟!？!

您可以尝试以下操作并看看这需要多长时间吗？

SET STATISTICS TIME ON

SELECT * INTO #test FROM [Item]

如果运行速度很快，那么我会寻找方法来加快计算机和 Azure 服务器之间的连接速度。 (网络数据包大小？)