CODEWITHSUNDEEP
sqlsql-serverperformanceindexing
John
John

Reputation: 1890

How to speed up this simple query that uses a clustered index scan?

I have a simple inner join returning under 10k rows, but it's taking around 6 seconds. Can I speed it up with indexing improvements or something else?

In the past I have used a guid or incrementing int for the primary key and have never had an issue with larger datasets than this, I suspect the Stock.StockId varchar(50) is my issue. I realise I can change this to an int but thought there might be an alternative to save me updating all my data sources, fk references etc. after this column change. I introduced the clustered key on stockBinQuantity today which reduced the query time from 10 seconds to 6, still too slow though.

My data volume - Table Row Counts
Bin:1009
Stock: 11414
StockBinQuantity: 8456

Table columns and Indexes

Query and Execution Plan

Table Create Statements

-- STOCK
CREATE TABLE [dbo].[Stock](
    [StockId] [varchar](50) NOT NULL,
    [Alias] [varchar](50) NULL,
    [Description] [varchar](max) NOT NULL,
    [PartNumber] [varchar](250) NULL,
    [StockImage] [image] NULL,
    [IsRestricted] [bit] NOT NULL,
    [Notes] [varchar](max) NULL,
 CONSTRAINT [PK_Stock] PRIMARY KEY CLUSTERED 
(
    [StockId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Stock] ADD  CONSTRAINT [DF_Stock_Restricted]  DEFAULT ((0)) FOR [IsRestricted]
GO

-- BIN
CREATE TABLE [dbo].[Bin](
    [BinId] [int] IDENTITY(1,1) NOT NULL,
    [BinCode] [varchar](50) NULL,
    [WarehouseCode] [varchar](50) NOT NULL,
    [Name] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Bin_1] PRIMARY KEY CLUSTERED 
(
    [BinId] 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

ALTER TABLE [dbo].[Bin]  WITH CHECK ADD  CONSTRAINT [FK_Bin_Warehouse] FOREIGN KEY([WarehouseCode])
REFERENCES [dbo].[Warehouse] ([WarehouseCode])
GO

ALTER TABLE [dbo].[Bin] CHECK CONSTRAINT [FK_Bin_Warehouse]
GO

-- STOCKBINQUANTITY
CREATE TABLE [dbo].[StockBinQuantity](
    [StockBinQuantityId] [int] IDENTITY(1,1) NOT NULL,
    [StockId] [varchar](50) NOT NULL,
    [BinId] [int] NOT NULL,
    [QuantityOnHand] [int] NOT NULL,
    [QuantityOnOrder] [int] NOT NULL,
    [ReorderPoint] [int] NOT NULL,
 CONSTRAINT [PK_StockBinQuantity] PRIMARY KEY NONCLUSTERED 
(
    [StockBinQuantityId] 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

ALTER TABLE [dbo].[StockBinQuantity]  WITH CHECK ADD  CONSTRAINT [FK_StockBin_Stock] FOREIGN KEY([StockId])
REFERENCES [dbo].[Stock] ([StockId])
GO

ALTER TABLE [dbo].[StockBinQuantity] CHECK CONSTRAINT [FK_StockBin_Stock]
GO

ALTER TABLE [dbo].[StockBinQuantity]  WITH CHECK ADD  CONSTRAINT [FK_StockBinQuantity_Bin] FOREIGN KEY([BinId])
REFERENCES [dbo].[Bin] ([BinId])
GO

ALTER TABLE [dbo].[StockBinQuantity] CHECK CONSTRAINT [FK_StockBinQuantity_Bin]
GO

Upvotes: 0

Views: 221

Answers (1)

John
John

Reputation: 1890

Thanks guys for the comments - you're correct in that modifying the select statement speeds up the query. In my case defining every column except the image column on Stock provides a sub one second query run time, you've helped me determine it's this one field that slow's it down.

I also found How to improve performance in SQL Server table with image fields?, I will investigate.

Hence, with your help I have solved why it's slow in sql and I can leave out that one field. Now I need to refine my linq statement to reflect this.

I agree about the key being redundant as such, but it's referenced in a few places by existing code and tables and not slowing my queries down noticeably.

Upvotes: 1

Related Questions