Using 'OR' drastically increases execution time of query?

Question

Post reply

Using 'OR' drastically increases execution time of query?

John Higginbotham

SSC Enthusiast

Points: 102
More actions
January 16, 2008 at 9:00 am

#182803

Hi all,
I have an application audit log with 1 million rows of test data (it's going to be a busy app!) as follows
AuditID (PK)
EventID (INT)
EventFriendlyName (Varchar255)
BusinessObjecTypeFullName (Varchar 500)
BusinessObjecInstancetID (INT)
Username (Varchar 100)
Timestamp (DateTime)
When I query the data
SELECT
EventId,
EventFriendlyDescription,
BusinessObjectTypeFullName,
BusinessObjectInstanceId,
ISNULL(Detail, '') AS Detail,
Username,
[TimeStamp]
FROM
Auditing.Audit
WHERE
(
Username = @Username
OR
@Username = ''
)
AND
(
BusinessObjectTypeFullName = @BusinessObjectTypeFullName
OR
@BusinessObjectTypeFullName = ''
)
AND
(
BusinessObjectInstanceId = @BusinessObjectInstanceId
OR
@BusinessObjectInstanceId = ''
)
AND
(
@AuditDate IS NULL
OR
[Timestamp] > @AuditDate
)
the execution time is over 2 seconds, however, if I remove the argument
OR
@BusinessObjectInstanceId = ''
the execution time is reduced to .25 of a second.
Removing the other 'OR' arguments does not effect performance, just this particular one. It is the only column that has a large number of possible variations (5000), the other columns have a possible variance of up to 20/30.
Other than the Primary Key there is no indexing on this table, could indexing help? I am nervous about applying indexing to this table as it's so busy with inserts from the app.
Any help would be gratefully received!

Viewing 15 posts - 1 through 15 (of 16 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Indexing would defiently help you query speed since you are not querying based on the primary Key.

Be carefull about adding too many indexes since indexes can slow down DML statements.

I would say check all queries that you probably will be running and see what would be the best index. Maybe a clustered Index will help you a lot. Also try moving data older than a certain period of time to another table since I have a feeling that this table is going to grow like crazy.;)

-Roy

John Higginbotham SSC Enthusiast Points: 102 More actions · Answer 2

Do you have any suggestions for the index (es)? The query in the original post will be used far more than any other. I have tried a non clustered index just on the BusinessObjectInstanceId column and the query ignores it and just performs a clustered index scan on the primary key.

I have also tried running the query several times whilst running a trace, then importing the trace into database engine tuning advisor, which suggests no recommendations?

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

BusinessObjectInstanceId = @BusinessObjectInstanceId
OR
@BusinessObjectInstanceId = ''

Also, as BusinessObjectInstanceId is an integer, the implicit type casting will not help.

Try OR @BusinessObjectInstanceId = 0

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

you realize that you're using the Variable name (not the column name) in the OR, right? meaning - if the variable is the empty string - it should return every record in the table/query?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

John Higginbotham SSC Enthusiast Points: 102 More actions · Answer 5

Tried using OR @BusinessObjectInstanceId = 0 with no difference to the execution time

The parameter is being passed into this stored procedure and could be empty depending on what the user selects. It will normally not be empty, but we need to return the other parameters where this one is not specified.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

You can add an index on multipole columns. Username, BusinessObjectTypeFullName and Timestamp.

Also try to rewrite the where clause with CASE Statement. It will look more clean.

Where USERNAME = CASE when @username = ''

then USERNAME

ELSE @username END

AND BusinessObjectTypeFullName = CASE when @BusinessObjectTypeFullName =''

then BusinessObjectTypeFullName

ELSE @BusinessObjectTypeFullName

END

AND BusinessObjectInstanceId = CASE When @BusinessObjectInstanceId = ''

then BusinessObjectInstanceId

else @BusinessObjectInstanceId

End

AND [Timestamp] = CASE when @AuditDate = ''

Then [Timestamp]

else @AuditDate

End

-Roy

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

You might care to try adding the WITH RECOMPILE option to the SP. It sounds like the cached execution plan doesn't work so well in that case, so forcing it to ditch it and build a new one might be the way to go.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

John Higginbotham SSC Enthusiast Points: 102 More actions · Answer 8

Mmmmm... I have created a Clustered index on BusinessObjectInstanceId, BusinessObjectTypeFullName, Timestamp and there is a small improvement, but not siginficant enough to warrant the hit on the index rebuilds that will occur as a result.

I get the same execution time when I specify the parameter as a specific value as follows

OR

@BusinessObjectInstanceId = 2817

Why is the OR argument causing this delay just on this one column? Can I code the query differently to get around this?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

John Higginbotham (1/16/2008)
I get the same execution time when I specify the parameter as a specific value as follows
OR
@BusinessObjectInstanceId = 2817

Like someone said above, You are doing a coinditional search for the variable, not the data in the table.

When you do this, it will do a comple table scan (Or clustered Scan)

-Roy

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Have you made sure you don't have parameter sniffing going on?

It sounds like it's making assumptions on how big the recordset will be during the execution plan which then are incorrect with this new OR.

So - either use the WITH RECOMPILE, or simply try declaring local variables, set the local vars to what is being passed in, and use the local vars in the SP.

As in

create procedure mypro(@myparam int

--,etc....

)

AS

BEGIN

declare @mylocalvar int

--etc...

SET @mylocalvar=@myparam

--use @mylocalvar in the My stored procedure and NOT @myparam.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 11

I know I sound like a broken record sometimes, but have you looked at the execution plan to see which operations are specifically causing the most problem? That will lead you into areas where the indexes will do the most good. You can see the operation's overall cost within the query and you can see which predicates are leading to that cost.

"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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

Hey Kevin Boles! Here's your chance, buddy... show us what you've got! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Higginbotham SSC Enthusiast Points: 102 More actions · Answer 13

Thanks for the replies.

I'll change the way I'm doing this, rather than use 1 stored proc to handle all possible entries from the user I'll split it down into seperate procs explicitly querying for the user requirements.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 14

Since you're kind of at the mercy of what the users enter for parameters, I would do the whole thing in Dynamic SQL. You could get rid of all OR's. Use 3 SQL strings.

First is the select statement @s-2

Next would be the WHERE clause @W.

Next would be something for the AND @a.

I don't like multiple single quote characters so I use a vairable @Q

SET @s-2 to the select string.

SET @W = ' WHERE '

SET @a = ''

SET @Q = CHAR(39)

Build the where clause based on the input like:

IF @UserName IS NOT NULL

BEGIN

SET @W = @W + @a + 'UserName = ' + @Q + @UserName + @Q

SET @Q = ' AND '

END

When done check to make sure there's something in the WHERE clause.

IF @W = ' WHERE '

SET @W = ''

EXEC (@S + @W)

Any empty inputs will just not be part of the selection criteria.

Hope this helps.

Todd Fifield