WHERE clause optimization

Instead of "All"  I would use Null.  That is what it was intended for.  As in:

SELECT *
FROM TABLEName
WHERE (@Parameter1 Is Null OR Field1 = @Parameter1)
            AND (@Parameter2 Is Null OR Field2 = @Parameter2)
            AND (@Parameter3 Is Null OR Field3 = @Parameter3)

And make sure you not only explicitly name the columns instead of using *, but also index the table appropriately.

SELECT * FROM TABLE WHERE
(@Parameter1 = 'ALL' OR Field1 = @Parameter1) AND
(@Parameter2 = 'ALL' OR Field2 = @Parameter2) AND
(@Parameter3 = 'ALL' OR Field3 = @Parameter3)

Select allOpen in new window

Thanks to you both for your input!

I also tried (@Parameter1 = 'ALL' OR Field1 = @Parameter1) and there seemed to be no difference to the query cost compared to (@Parameter1 = 'ALL' OR (@Parameter1 <> 'ALL AND Field1 = @Parameter1)).

Changing the paremeter value from 'ALL' to NULL helped to improve performance somewhat, as I guess it was not having to evaluate a string (which, by the way, is VARCHAR(50)).

The thing that I find frustrating is that "SELECT * FROM TABLE" is lightening fast, but SELECT * FROM TABLE WHERE (@Parameter1 IS NULL OR Field1 = @Parameter1) AND (@Parameter2 IS NULL OR Field2 = @Parameter2) AND (@Parameter3 IS NULL OR Field3 = @Parameter3)...which is, esentially,  EXACTLY the same thing when all parameters are NULL...but the optimizer seems to be taking into conseration the "OR FieldX = @ParamterX" side of the OR, even though the parameters, in all cases, are NULL.

I want to try and avoid this (which will work, and work quickly, but my stored proc is complicated and would become very unwieldly):

IF @Parameter1 IS NULL AND @Parameter2 IS NULL AND @Parameter3 IS NULL
     SELECT * FROM TABLE
ELSE
     SELECT * FROM TABLE WHERE (@Parameter1 IS NULL OR Field1 = @Parameter1)...

Do you think this is the only approach?

Thanks again!

I should add one more thing...

Each of these searchable fields are indexed.  When all parameter values are NULL, the optimizer seems to want to do an index scan on ONE of the indexed fields, at significant cost, even though using the index is not necessary because I'm trying to return all rows...

How big is the table? Is it jsut one table?  Can you post the structure of the table as well as the real table?

Table is approximate 200,000 rows, but will potentially get much bigger.  It is a "lookup" table that contains a list of objects.  Those objects (which are accounts), have balances, and are stored in another table and are joined by clustered indexes.  The purpose of the "Object" table is to have a highly indexed lookup table where the various accounts (and the searchfields thereof) are located.  Once the list of desired accounts is obtained, a join is then done with the balance table.

I've attached the SQL to recreate the table, the indexes, and the offending query.  If you comment out the WHERE clause on the query, it runs instantly (< 1 sec with 200,000 rows).  When you leave the WHERE clause in, the optimizer does an index seek on sSearchField9 (!?!).

Let me know if there's anything else I can provide.  Thank you once again!

--C

--Create table
CREATE TABLE [dbo].[tblObject](
	[hID] [int] IDENTITY(1,1) NOT NULL,
	[sObject] [varchar](500) NOT NULL,
	[hSource] [int] NOT NULL,
	[hSegment] [int] NOT NULL,
	[sDescr] [varchar](max) NULL,
	[sSearchField1] [varchar](50) NULL,
	[sSearchField2] [varchar](50) NULL,
	[sSearchField3] [varchar](50) NULL,
	[sSearchField4] [varchar](50) NULL,
	[sSearchField5] [varchar](50) NULL,
	[sSearchField6] [varchar](50) NULL,
	[sSearchField7] [varchar](50) NULL,
	[sSearchField8] [varchar](50) NULL,
	[sSearchField9] [varchar](50) NULL,
	[sSearchField10] [varchar](50) NULL,
	[sSearchField11] [varchar](50) NULL,
	[sSearchField12] [varchar](50) NULL,
	[sSearchField13] [varchar](50) NULL,
	[sSearchField14] [varchar](50) NULL,
	[sSearchField15] [varchar](50) NULL
 CONSTRAINT [PK_tblObject] PRIMARY KEY CLUSTERED 
(
	[hID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
 
--Create indexes
CREATE NONCLUSTERED INDEX [IX_tblObject_hSource] ON [dbo].[tblObject] 
(
	[hSource] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_tblObject_sObject] ON [dbo].[tblObject] 
(
	[sObject] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField1] ON [dbo].[tblObject] 
(
	[sSearchField1] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField10] ON [dbo].[tblObject] 
(
	[sSearchField10] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField11] ON [dbo].[tblObject] 
(
	[sSearchField11] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField12] ON [dbo].[tblObject] 
(
	[sSearchField12] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField13] ON [dbo].[tblObject] 
(
	[sSearchField13] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField14] ON [dbo].[tblObject] 
(
	[sSearchField14] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField15] ON [dbo].[tblObject] 
(
	[sSearchField15] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField2] ON [dbo].[tblObject] 
(
	[sSearchField2] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField3] ON [dbo].[tblObject] 
(
	[sSearchField3] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField4] ON [dbo].[tblObject] 
(
	[sSearchField4] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField5] ON [dbo].[tblObject] 
(
	[sSearchField5] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField6] ON [dbo].[tblObject] 
(
	[sSearchField6] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField7] ON [dbo].[tblObject] 
(
	[sSearchField7] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField8] ON [dbo].[tblObject] 
(
	[sSearchField8] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_tblObject_sSearchField9] ON [dbo].[tblObject] 
(
	[sSearchField9] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
 
--Offending query
SELECT	hID hObject
FROM	tblObject
WHERE	(@hSource IS NULL OR hSource  = @hSource)
		AND (@sSearchFieldName_1 IS NULL OR sSearchField1 = @sSearchFieldName_1)
		AND (@sSearchFieldName_2 IS NULL OR sSearchField2 = @sSearchFieldName_2)
		AND (@sSearchFieldName_3 IS NULL OR sSearchField3 = @sSearchFieldName_3)
		AND (@sSearchFieldName_4 IS NULL OR sSearchField4 = @sSearchFieldName_4)
		AND (@sSearchFieldName_5 IS NULL OR sSearchField5 = @sSearchFieldName_5)
		AND (@sSearchFieldName_6 IS NULL OR sSearchField6 = @sSearchFieldName_6)
		AND (@sSearchFieldName_7 IS NULL OR sSearchField7 = @sSearchFieldName_7)
		AND (@sSearchFieldName_8 IS NULL OR sSearchField8 = @sSearchFieldName_8)
		AND (@sSearchFieldName_9 IS NULL OR sSearchField9 = @sSearchFieldName_9)
		AND (@sSearchFieldName_10 IS NULL OR sSearchField10 = @sSearchFieldName_10)
		AND (@sSearchFieldName_11 IS NULL OR sSearchField11 = @sSearchFieldName_11)
		AND (@sSearchFieldName_12 IS NULL OR sSearchField12 = @sSearchFieldName_12)
		AND (@sSearchFieldName_13 IS NULL OR sSearchField13 = @sSearchFieldName_13)
		AND (@sSearchFieldName_14 IS NULL OR sSearchField14 = @sSearchFieldName_14)
		AND (@sSearchFieldName_15 IS NULL OR sSearchField15 = @sSearchFieldName_15)

Select allOpen in new window

did you try reindexing the table?

Yup - no change

how about deleting all the indexes and creating a single index that contains all your fields instead?

And lastly also change the first line to:
SELECT * FROM TABLE WITH (NOLOCK)
.....

This will ignore any possible locks on the table and do the selection for you. It is a huge performance improvement sometimes, especially if you are accessing the table from other applications at the same time.

I recently came across something similar.  But in our case the tables joined each had over 10 million rows.  Much against my best wishes we ended up using Dynamic SQL, but without having to give Select permissions to the individual tables, which was my main concern.  If you want details I can post them.

There is one other approach that you may want to consider and that is Full-Text Search.  You would add an index to the Full-Text Catalog for each searcahable column and you can then drop all the non-clustered indexes for the same columns)  Your query then can be as simple as:

SELECT      hID hObject
FROM      tblObject
WHERE CONTAINS(*, 'Your Search value goes here')

However, read up on Full-Text Search first to see if it meets your needs.

>>The WHERE @intSearchFieldX IS NULL or @intSearchFieldX = intSearchFieldX structure worked perfectly after that.<<
You are welcome.

>>as the solution and the points be refunded to the Asker's account.<<
You have been here just 2 days and you have obviously made great strides in how to take advantage of this community.

Good luck,

Just tell SQL not to use an index (or to use the clus index only) to satisfy the query.

SELECT ...
FROM tableName WITH (INDEX(0))
WHERE ...

Closed, 250 points refunded.
Computer101
EE Admin