February 13, 2024 at 3:53 pm
Dear all,
Thank you for looking into my question.
I have a table with 7.2 million records (table size 3 GB, Index size 1.6 GB). It has about 7 non-clustered indexes and a clustered index and the statistics are up-to-date.
I wrote a query to have a foreign key lookup from the table above. The foreign key table is a tiny one with about 500 records.
I am selecting almost all of the columns from the bigger table.
I have an order by clause on a varchar column that is killing the performance..
The query takes about 25 seconds to come back. I want it to be under 5 seconds.
Is there any basic tuning techniques that I can implement?
Thanks
February 13, 2024 at 3:58 pm
paste the query and the ddl of the tables including the indexes (anonymizing field names if necessary) and we can take a look at it
February 13, 2024 at 4:39 pm
SELECT TOP(50) [c].[Barcode], [c].[BottomDepth], [c].[BoxCount], [c].[BoxNumber],
[c].[CertifiedDate], [c].[CheckedDate], [c].[Comment], [c].[Condition], [c].[ContainerName],
[c].[ContainerOwnerId], [c].[Core], [c].[CoreShift], [c].[CreatedBy], [c].[CreatedDate],
[c].[DepthMaxText], [c].[DepthMinText], [c].[FormationId], [c].[ImmediateContainer],
[c].[InUse], [c].[Interval], [c].[LogDepth], [c].[ModifiedBy], [c].[ModifiedDate], [c].[Movement],
[c].[NextCertificationDate], [c].[Notes], [c].[Permeablity], [c].[Porosity], [c].[PossiblePlugs],
[c].[Quality], [c].[RecordItemStatusId], [c].[SampleCount], [c].[SampleNumber], [c].[SerialNumber],
[c].[SetCount], [c].[SetId], [c].[Spacing], [c].[StorageTypeId], [c].[ThirdPartyOwner], [c].[TopDepth],
[c].[Weight], [c0].[Id], [c0].[ContainerTypeName]
FROM [Container] AS [c]
LEFT JOIN [Admin].[ContainerType] AS [c0] ON [c].[ContainerTypeId] = [c0].[Id]
WHERE [c].[Active] = CAST(1 AS bit) AND [c].[ImmediateContainer] = CAST(0 AS bit)
ORDER BY [c0].[ContainerTypeName]
CREATE TABLE [dbo].[Container](
[Barcode] [bigint] NOT NULL,
[ContainerTypeId] [int] NULL,
[SetId] [nvarchar](64) NULL,
[Core] [nvarchar](64) NULL,
[BoxNumber] [nvarchar](64) NULL,
[TopDepth] [numeric](18, 2) NULL,
[BottomDepth] [numeric](18, 2) NULL,
[DepthMinText] [nvarchar](32) NULL,
[DepthMaxText] [nvarchar](32) NULL,
[Interval] [int] NULL,
[ContainerName] [nvarchar](1000) NULL,
[Comment] [nvarchar](256) NULL,
[SampleCount] [smallint] NULL,
[ThirdPartyOwner] [nvarchar](256) NULL,
[ContainerOwnerId] [int] NULL,
[SerialNumber] [nvarchar](256) NULL,
[CertifiedDate] [datetime2](7) NULL,
[NextCertificationDate] [datetime2](7) NULL,
[InUse] [int] NULL,
[ImmediateContainer] [bit] NULL,
[FormationId] [int] NULL,
[SampleNumber] [nvarchar](64) NULL,
[BoxCount] [smallint] NULL,
[SetCount] [smallint] NULL,
[Porosity] [nvarchar](64) NULL,
[Spacing] [nvarchar](64) NULL,
[Permeablity] [nvarchar](64) NULL,
[Quality] [nvarchar](64) NULL,
[Condition] [nvarchar](64) NULL,
[CheckedDate] [datetime2](7) NULL,
[Movement] [nvarchar](128) NULL,
[PossiblePlugs] [nvarchar](128) NULL,
[Weight] [nvarchar](128) NULL,
[CoreShift] [numeric](18, 2) NULL,
[LogDepth] [numeric](18, 2) NULL,
[Active] [bit] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[RowGuid] [uniqueidentifier] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime2](7) NULL,
[DeletedBy] [int] NULL,
[DeletedDate] [datetime2](7) NULL,
[RecordItemStatusId] [int] NULL,
[Notes] [nvarchar](max) NULL,
[StorageTypeId] [int] NULL,
CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED
(
[Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_ContainerOwner_ContainerOwnerId] FOREIGN KEY([ContainerOwnerId])
REFERENCES [Admin].[ContainerOwner] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_ContainerOwner_ContainerOwnerId]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_ContainerType_ContainerTypeId] FOREIGN KEY([ContainerTypeId])
REFERENCES [Admin].[ContainerType] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_ContainerType_ContainerTypeId]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_Formation_FormationId] FOREIGN KEY([FormationId])
REFERENCES [Admin].[Formation] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_Formation_FormationId]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_Item_Barcode] FOREIGN KEY([Barcode]) REFERENCES [dbo].[Item] ([Barcode])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_Item_Barcode]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_RecordItemStatus_RecordItemStatusId] FOREIGN KEY([RecordItemStatusId]) REFERENCES [dbo].[RecordItemStatus] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_RecordItemStatus_RecordItemStatusId]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_StorageType_StorageTypeId] FOREIGN KEY([StorageTypeId]) REFERENCES [Admin].[StorageType] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_StorageType_StorageTypeId]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_CreatedBy] FOREIGN KEY([CreatedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_CreatedBy]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_DeletedBy] FOREIGN KEY([DeletedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_DeletedBy]
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_ModifiedBy] FOREIGN KEY([ModifiedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_ModifiedBy]
---- Container Type
CREATE TABLE [Admin].[ContainerType](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ContainerTypeName] [nvarchar](200) NOT NULL,
[PerUnit] [real] NOT NULL,
[IsFixed] [bit] NOT NULL,
[Width] [real] NULL,
[Length] [real] NULL,
[Height] [real] NULL,
[SortOrder] [int] NULL,
[UomId] [int] NULL,
[UnitofMeasureId] [int] NULL,
[Active] [bit] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[RowGuid] [uniqueidentifier] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime2](7) NULL,
[DeletedBy] [int] NULL,
[DeletedDate] [datetime2](7) NULL,
[ContainerTypeCateryId] [int] NULL,
[IsEditable] [bit] NOT NULL,
CONSTRAINT [PK_ContainerType] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [Admin].[ContainerType] ADD CONSTRAINT [DF__Container__IsEdi__35E8B1EA] DEFAULT (CONVERT([bit],(0))) FOR [IsEditable]
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_ContainerTypeCatery_ContainerTypeCateryId] FOREIGN KEY([ContainerTypeCateryId])
REFERENCES [Admin].[ContainerTypeCatery] ([Id])
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_ContainerTypeCatery_ContainerTypeCateryId]
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_UnitofMeasure_UnitofMeasureId] FOREIGN KEY([UnitofMeasureId])
REFERENCES [Admin].[UnitofMeasure] ([Id])
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_UnitofMeasure_UnitofMeasureId]
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_CreatedBy] FOREIGN KEY([CreatedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_CreatedBy]
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_DeletedBy] FOREIGN KEY([DeletedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_DeletedBy]
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_ModifiedBy] FOREIGN KEY([ModifiedBy]) REFERENCES [Access].[User] ([Id])
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_ModifiedBy]
February 13, 2024 at 4:39 pm
SELECT TOP(50) [c].[Barcode], [c].[BottomDepth], [c].[BoxCount], [c].[BoxNumber],
[c].[CertifiedDate], [c].[CheckedDate], [c].[Comment], [c].[Condition], [c].[ContainerName],
[c].[ContainerOwnerId], [c].[Core], [c].[CoreShift], [c].[CreatedBy], [c].[CreatedDate],
[c].[DepthMaxText], [c].[DepthMinText], [c].[FormationId], [c].[ImmediateContainer],
[c].[InUse], [c].[Interval], [c].[LogDepth], [c].[ModifiedBy], [c].[ModifiedDate], [c].[Movement],
[c].[NextCertificationDate], [c].[Notes], [c].[Permeablity], [c].[Porosity], [c].[PossiblePlugs],
[c].[Quality], [c].[RecordItemStatusId], [c].[SampleCount], [c].[SampleNumber], [c].[SerialNumber],
[c].[SetCount], [c].[SetId], [c].[Spacing], [c].[StorageTypeId], [c].[ThirdPartyOwner], [c].[TopDepth],
[c].[Weight], [c0].[Id], [c0].[ContainerTypeName]
FROM [Container] AS [c]
LEFT JOIN [Admin].[ContainerType] AS [c0] ON [c].[ContainerTypeId] = [c0].[Id]
WHERE [c].[Active] = CAST(1 AS bit) AND [c].[ImmediateContainer] = CAST(0 AS bit)
ORDER BY [c0].[ContainerTypeName]
/****** Object: Table [dbo].[Container] Script Date: 2/13/2024 4:29:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Container](
[Barcode] [bigint] NOT NULL,
[ContainerTypeId] [int] NULL,
[SetId] [nvarchar](64) NULL,
[Core] [nvarchar](64) NULL,
[BoxNumber] [nvarchar](64) NULL,
[TopDepth] [numeric](18, 2) NULL,
[BottomDepth] [numeric](18, 2) NULL,
[DepthMinText] [nvarchar](32) NULL,
[DepthMaxText] [nvarchar](32) NULL,
[Interval] [int] NULL,
[ContainerName] [nvarchar](1000) NULL,
[Comment] [nvarchar](256) NULL,
[SampleCount] [smallint] NULL,
[ThirdPartyOwner] [nvarchar](256) NULL,
[ContainerOwnerId] [int] NULL,
[SerialNumber] [nvarchar](256) NULL,
[CertifiedDate] [datetime2](7) NULL,
[NextCertificationDate] [datetime2](7) NULL,
[InUse] [int] NULL,
[ImmediateContainer] [bit] NULL,
[FormationId] [int] NULL,
[SampleNumber] [nvarchar](64) NULL,
[BoxCount] [smallint] NULL,
[SetCount] [smallint] NULL,
[Porosity] [nvarchar](64) NULL,
[Spacing] [nvarchar](64) NULL,
[Permeablity] [nvarchar](64) NULL,
[Quality] [nvarchar](64) NULL,
[Condition] [nvarchar](64) NULL,
[CheckedDate] [datetime2](7) NULL,
[Movement] [nvarchar](128) NULL,
[PossiblePlugs] [nvarchar](128) NULL,
[Weight] [nvarchar](128) NULL,
[CoreShift] [numeric](18, 2) NULL,
[LogDepth] [numeric](18, 2) NULL,
[Active] [bit] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[RowGuid] [uniqueidentifier] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime2](7) NULL,
[DeletedBy] [int] NULL,
[DeletedDate] [datetime2](7) NULL,
[RecordItemStatusId] [int] NULL,
[Notes] [nvarchar](max) NULL,
[StorageTypeId] [int] NULL,
CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED
(
[Barcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_ContainerOwner_ContainerOwnerId] FOREIGN KEY([ContainerOwnerId])
REFERENCES [Admin].[ContainerOwner] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_ContainerOwner_ContainerOwnerId]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_ContainerType_ContainerTypeId] FOREIGN KEY([ContainerTypeId])
REFERENCES [Admin].[ContainerType] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_ContainerType_ContainerTypeId]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_Formation_FormationId] FOREIGN KEY([FormationId])
REFERENCES [Admin].[Formation] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_Formation_FormationId]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_Item_Barcode] FOREIGN KEY([Barcode])
REFERENCES [dbo].[Item] ([Barcode])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_Item_Barcode]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_RecordItemStatus_RecordItemStatusId] FOREIGN KEY([RecordItemStatusId])
REFERENCES [dbo].[RecordItemStatus] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_RecordItemStatus_RecordItemStatusId]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_StorageType_StorageTypeId] FOREIGN KEY([StorageTypeId])
REFERENCES [Admin].[StorageType] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_StorageType_StorageTypeId]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_CreatedBy] FOREIGN KEY([CreatedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_CreatedBy]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_DeletedBy] FOREIGN KEY([DeletedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_DeletedBy]
GO
ALTER TABLE [dbo].[Container] WITH CHECK ADD CONSTRAINT [FK_Container_User_ModifiedBy] FOREIGN KEY([ModifiedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [dbo].[Container] CHECK CONSTRAINT [FK_Container_User_ModifiedBy]
GO
---- Container Type
CREATE TABLE [Admin].[ContainerType](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ContainerTypeName] [nvarchar](200) NOT NULL,
[PerUnit] [real] NOT NULL,
[IsFixed] [bit] NOT NULL,
[Width] [real] NULL,
[Length] [real] NULL,
[Height] [real] NULL,
[SortOrder] [int] NULL,
[UomId] [int] NULL,
[UnitofMeasureId] [int] NULL,
[Active] [bit] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[RowGuid] [uniqueidentifier] NULL,
[CreatedBy] [int] NULL,
[CreatedDate] [datetime2](7) NOT NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime2](7) NULL,
[DeletedBy] [int] NULL,
[DeletedDate] [datetime2](7) NULL,
[ContainerTypeCategoryId] [int] NULL,
[IsEditable] [bit] NOT NULL,
CONSTRAINT [PK_ContainerType] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Admin].[ContainerType] ADD CONSTRAINT [DF__Container__IsEdi__35E8B1EA] DEFAULT (CONVERT([bit],(0))) FOR [IsEditable]
GO
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_ContainerTypeCategory_ContainerTypeCategoryId] FOREIGN KEY([ContainerTypeCategoryId])
REFERENCES [Admin].[ContainerTypeCategory] ([Id])
GO
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_ContainerTypeCategory_ContainerTypeCategoryId]
GO
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_UnitofMeasure_UnitofMeasureId] FOREIGN KEY([UnitofMeasureId])
REFERENCES [Admin].[UnitofMeasure] ([Id])
GO
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_UnitofMeasure_UnitofMeasureId]
GO
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_CreatedBy] FOREIGN KEY([CreatedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_CreatedBy]
GO
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_DeletedBy] FOREIGN KEY([DeletedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_DeletedBy]
GO
ALTER TABLE [Admin].[ContainerType] WITH CHECK ADD CONSTRAINT [FK_ContainerType_User_ModifiedBy] FOREIGN KEY([ModifiedBy])
REFERENCES [Access].[User] ([Id])
GO
ALTER TABLE [Admin].[ContainerType] CHECK CONSTRAINT [FK_ContainerType_User_ModifiedBy]
GO
February 13, 2024 at 6:15 pm
You didn't get any nonclustered index definitions on here, but one problem I see:
CAST(1 as bit) and CAST(0 as bit) if it is even sargable, will mess up cardinality.
Are most of the containers expected to be active = 1 and ImmediateContainer = 0 status?
If less than 25-10% of the records are in that pair of status types you may need an index on those two columns and probably need to fix the functions in the WHERE clause.
If more than 10-25% of the containers are in that pair of status, you need to first query the top 50 containerId and containerTypeName where those two statuses match so that when SQL does the sort, it isn't sorting 7200 byte rows on however many rows match that. Then get the rest of the metadata and join it back, something like this:
WITH [ContainerByName] AS (
SELECT TOP (50)
[Cont].[Barcode]
,[ContType].[Id]
,[ContType].[ContainerTypeName]
FROM [dbo].[Container] AS [Cont]
INNER JOIN
[dbo].[ContainerType] AS [ContType]
ON
[Cont].[ContainerTypeId] = [ContType].[Id]
WHERE
[Cont].[ImmediateContainer] = 0
AND
[Cont].[Active] = 1
ORDER BY
[ContType].[ContainerTypeName]
),
[ContainerMetadata] AS (
SELECT
[Cont].[Barcode]
,[Cont].[BottomDepth]
/*etc*/
FROM
[dbo].[Container] AS [Cont]
)
SELECT [ContainerMetadata].[Barcode]
,[ContainerMetadata].[BottomDepth]
,[ContainerByName].[ContainerTypeName]
FROM
[ContainerByName]
INNER JOIN
[ContainerMetadata]
ON [ContainerByName].[Barcode] = [ContainerMetadata].[Barcode]
February 13, 2024 at 7:37 pm
Don't change the CASTing for the bits in the WHERE clause, leave as is. Otherwise I believe SQL will default to a different data type which would force a column conversion, which would be much, much worse than a pre-query-plan-building CAST in the WHERE clause.
Do you have a lot of actual rows where "Active = 0"? If not, if "Active" is there "just in case you need it later", get rid of the column.
If ImmediateContainer is a mix of 0s and 1s, and/or you don't want to remove the Active column, then for best performance of this query you will need a separate nonclustered index on ( Active, ImmediateContainer, BarCode ). That index will be useful as long as you don't SELECT too many rows from the Container table with conditions on Active and ImmediateContainer. For just 50 rows, I would always expect SQL to read the nonclus index and then do lookups to the clus index to get the rest of the row. 50 lookups should be fast enough.
IF you do have quite a mix of Active/Inactive and ImmediateContainer = 0/1, and you most often / almost always query by those conditions, then you should consider prefixing your clustered index with those columns. Yes, if either of the column changes, that would a delete and (re)insert, so you have to factor that into your considerations as well. However, it would give you much faster lookups when matching Active and ImmediateContainer in the WHERE clause.
Btw, do you genuinely require nvarchar for all those char columns? Remember, nvarchar requires twice as many bytes as varchar, so only use nvarchar if you really need it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 13, 2024 at 8:45 pm
Thank you very much for your feedback. Let me work on implementing your suggestions
February 13, 2024 at 10:24 pm
Don't change the CASTing for the bits in the WHERE clause, leave as is. Otherwise I believe SQL will default to a different data type which would force a column conversion, which would be much, much worse than a pre-query-plan-building CAST in the WHERE clause.
I just tested it a second time and reconfirmed that the implicit type conversion does NOT occur. Even setting it back to 2008 compatibility.
February 13, 2024 at 10:47 pm
In this case it doesn't. But what is the harm in CASTing?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 14, 2024 at 1:39 pm
I searched through this, but it remains unclear to me, is there an index on ContainerTypeName? Preferably where that column is the leading column in the index so it's running the statistics for the index? Without that, ORDER BY is simply going to lead to scans of the large table. Also, an execution plan would be handy in order to understand how the optimizer is resolving this query. And yeah, the CAST statements aren't going to hurt performance here. They're being done to hard coded values, not columns. It shouldn't interfere with performance unless you're casting them to data types that don't match what's in the tables. That might, emphasis on might, cause issues. Again, an execution plan would tell us more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 15, 2024 at 4:13 pm
Container type name is in a lookup table, even if there is an index on the foreign key, the key won't be ordered in the alphabetical order of lookup name. Hence why I was pretty sure that it needed to be sorted with a narrower query before joining it back to the rest of the metadata.
Casting that type in the where predicate is crap code. In this specific code, casting 1 to bit does not interfere with sargability but in some cases it will interfere with cardinality and its unnecessary cruft that serves no value.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply