Comparison of Null values is giving me poor performance

Question

Comparison of Null values is giving me poor performance

zach_john

SSCertifiable

Points: 5922
More actions
July 16, 2009 at 9:54 am

#223604

Is there a better way to write the query to the given table ?
SELECT Top 1 @ContainerUUID=ContainerUUID
from CM_VENDOR_CONTAINER(NOLOCK)
where IsNULL(VENDorContainername,'')=IsNULL(@VENDorContainerName,'')
And IsNULL(VENDorContainerID,'')=IsNULL(@VENDorContainerID,'')
And FileUUID=@FileUUID
CREATE TABLE [dbo].[CM_VENDOR_CONTAINER](
[ContainerUUID] [uniqueidentifier] NOT NULL CONSTRAINT [CMVNDCTNR_ContainerUUID_DF] DEFAULT (newid()),
[FileUUID] [uniqueidentifier] NOT NULL,
[ParentContainerUUID] [uniqueidentifier] NOT NULL,
[TopContainerUUID] [uniqueidentifier] NOT NULL,
[VendorContainerID] [varchar](9) NULL,
[VendorContainerName] [varchar](50) NULL,
[CardCount] [int] NOT NULL,
[UTCInserted] [datetime] NOT NULL CONSTRAINT [CMVNDCTNR_InsertedUTC_DF] DEFAULT (getutcdate()),
[UTCUpdated] [datetime] NOT NULL CONSTRAINT [CMVNDCTNR_UpdatedUTC_DF] DEFAULT (getutcdate()),
[SrvName] [varchar](30) NOT NULL CONSTRAINT [CMVNDCTNR_SrvName_DF] DEFAULT (@@servername),
[SerialNum] [bigint] NULL,
[ClientID] [int] NULL,
[SubprogID] [int] NULL,
[PackageID] [int] NULL,
[FHID] [int] NULL,
[ShippingUUID] [uniqueidentifier] NULL,
CONSTRAINT [CM_VENDOR_CONTAINER_PK] PRIMARY KEY NONCLUSTERED
([ContainerUUID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 40) ON [GEMINI_18_Indx_FG]
) ON [GEMINI_18_Data_FG]
CREATE NONCLUSTERED INDEX [CM_VNDR_CNTR_NAME_ID_FILID_IDX] ON [dbo].[CM_VENDOR_CONTAINER]
(
[VendorContainerName] ASC,
[VendorContainerID] ASC,
[FileUUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [GEMINI_18_Indx_FG]

John Zacharkan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

zach_john (7/16/2009)
Is there a better way to write the query to the given table ?
SELECT Top 1 @ContainerUUID=ContainerUUID
from CM_VENDOR_CONTAINER(NOLOCK)
where IsNULL(VENDorContainername,'')=IsNULL(@VENDorContainerName,'')
And IsNULL(VENDorContainerID,'')=IsNULL(@VENDorContainerID,'')
And FileUUID=@FileUUID
CREATE TABLE [dbo].[CM_VENDOR_CONTAINER](
[ContainerUUID] [uniqueidentifier] NOT NULL CONSTRAINT [CMVNDCTNR_ContainerUUID_DF] DEFAULT (newid()),
[FileUUID] [uniqueidentifier] NOT NULL,
[ParentContainerUUID] [uniqueidentifier] NOT NULL,
[TopContainerUUID] [uniqueidentifier] NOT NULL,
[VendorContainerID] [varchar](9) NULL,
[VendorContainerName] [varchar](50) NULL,
[CardCount] [int] NOT NULL,
[UTCInserted] [datetime] NOT NULL CONSTRAINT [CMVNDCTNR_InsertedUTC_DF] DEFAULT (getutcdate()),
[UTCUpdated] [datetime] NOT NULL CONSTRAINT [CMVNDCTNR_UpdatedUTC_DF] DEFAULT (getutcdate()),
[SrvName] [varchar](30) NOT NULL CONSTRAINT [CMVNDCTNR_SrvName_DF] DEFAULT (@@servername),
[SerialNum] [bigint] NULL,
[ClientID] [int] NULL,
[SubprogID] [int] NULL,
[PackageID] [int] NULL,
[FHID] [int] NULL,
[ShippingUUID] [uniqueidentifier] NULL,
CONSTRAINT [CM_VENDOR_CONTAINER_PK] PRIMARY KEY NONCLUSTERED
([ContainerUUID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 40) ON [GEMINI_18_Indx_FG]
) ON [GEMINI_18_Data_FG]
CREATE NONCLUSTERED INDEX [CM_VNDR_CNTR_NAME_ID_FILID_IDX] ON [dbo].[CM_VENDOR_CONTAINER]
(
[VendorContainerName] ASC,
[VendorContainerID] ASC,
[FileUUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [GEMINI_18_Indx_FG]

The problem is, the function that you're running on the columns, ISNULL, forces a table scan. Since it looks like you're trying to use NULL as a valid value, you shouldn't. NULL is only one value, not known. If you need to pass in a blank value, then default the values in the table to something like 'UNKNOWN' or 'NA' and then you can be better off with something like this:

...

WHERE VENDorContainerName = @VENDorContainerName

And VENDorContainerID=@VENDorContainerID

And FileUUID=@FileUUID

Otherwise, instead of what you have, try using an OR clause and simply stating:

...OR

(VENDorContainerName IS NULL AND VENDorContainerID IS NULL)

It should behave better than the previous query although you're still unlikely to see good index use since NULL values are not indexed and searching for the NULL's will probably require a scan.

Is there a reason your table doesn't have a clustered index? It should. Storage of clustered indexes is better in sQL Server than storage of heap tables (tables without a clustered index).

"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

zach_john SSCertifiable Points: 5922 More actions · Answer 2

Thanks Grant,

I've passed it on to the developers and agree with your analysis. I'm not sure how much code they would have to alter to change from a NULL value.

Non use of cluster index drives me insane here most of my work has been on environmental issues but I've seen and reported this problem numerous times.

We'll see what they have to say.

Thanks

John Zacharkan