WHERE clause optimization

How best to optimize the WHERE clause in a query where several parameters MAY or may not impact the WHERE clause.  For example:

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

If all three parameters = 'ALL', then the query is a realatively easy (and fast) SELECT * FROM TABLE.  This could be achieved by a simple IF statement, eg:
IF @Parameter1 = 'ALL' AND @Parameter2 = 'ALL' AND @Parameter3 = 'ALL' THEN SELECT * FROM TABLE
ELSE
SELECT * FROM TABLE WHERE Field1 = @Parameter1 AND Field2=...

However, in reality, the "real" query has 22 potential parameters, any combination of which could be used in the query.

Can anyone reccommend a way to structure the where clause (other than using IF statements to evaluate the "ALL" condition of each potential combination of paramters) WITHOUT using dynamic SQL to build the where clause on the fly?

Thanks!




ColinYYZAsked:
Who is Participating?
 
ColinYYZConnect With a Mentor Author Commented:
I was able to finally get around the problem by converting the VARCHAR(50) searchfields to INTEGERS (foreign keys), with the actual string values stored in a lookup table.  SQL Server stopped trying to use the unnecessary index after that.

The WHERE @intSearchFieldX IS NULL or @intSearchFieldX = intSearchFieldX structure worked perfectly after that.

Thanks to everyone for their suggestions - they definately helped to get the brain cells going.
0
 
Anthony PerkinsCommented:
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.
0
 
Jinesh KamdarCommented:

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

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ColinYYZAuthor Commented:
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!

0
 
ColinYYZAuthor Commented:
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...
0
 
Anthony PerkinsCommented:
How big is the table? Is it jsut one table?  Can you post the structure of the table as well as the real table?
0
 
ColinYYZAuthor Commented:
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)

Open in new window

0
 
YiogiCommented:
did you try reindexing the table?
0
 
ColinYYZAuthor Commented:
Yup - no change
0
 
YiogiCommented:
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.
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
>>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,

0
 
Scott PletcherSenior DBACommented:
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 ...
0
 
Computer101Commented:
Closed, 250 points refunded.
Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.