Issues with Where Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Issues with Where Clause

Author

Topic

ashawley
Starting Member

3 Posts

Posted - 2007-09-21 : 15:37:13

I have a really strange one and I was wondering if anyone had ever seen before.

I have this WHERE clause that is affecting performance depending on how I run it:

If I run the query with:

AND (o.owner_user_id= 107 OR (o.order_status_id=4 AND o.PC_user_id= 107))

It takes more than a minute.

But if I first declare a variable (@user_id) and set it to equal 107 and run the query using the variable in the WHERE clause like this:

AND (o.owner_user_id= @user_id OR (o.order_status_id=4 AND o.PC_user_id= @user_id))

The darn thing takes about 2 seconds.

Now, has anyone ever seen something like this before? I mean it's doing the same thing!

Thanks,

Adam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-21 : 16:16:00

what data types are the PC_user_id and owner_user_id columns in your tables? What data type is the @user_id variable that you are declaring?

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-21 : 16:26:21

Are you clearing your cache before each run? I suspect that the results are being cached so the second query runs much faster. There are other things you can clear out, but I usually run this before each batch while I am testing performance (NOTE: probably should not do this on a production machine).

DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL') 
GO
DBCC FREESESSIONCACHE WITH NO_INFOMSGS 
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS 
GO
CHECKPOINT
GO

EDIT: Opps thought I was in the 2005 forum.. This might not work on 2000. I can't remember any more.. :)

ashawley
Starting Member

3 Posts

Posted - 2007-09-21 : 16:31:49

Thanks for the replies folks...

The datatype is int in both cases. Same with the @user_id variable.

Regarding the cache issue, I am on 2K so I'm not sure that's the issue. And I'm not running them both at once.

In fact, I stumbled upon this while testing. I did the variable thing because I wanted an easier way to try differnt user_id values.

It seems to occur with some user_id's and not others and it's not related to how big the result set is. It's VERY strange.

I'm plain flummoxed!!

Adam

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-21 : 17:05:26

It sounds like parameter sniffing..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-21 : 23:49:51

Care to either explain "parameter sniffing" or include your favorite URL on the subject?

--Jeff Moden

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:38:57

"I mean it's doing the same thing!"

What does the query plan look like for each method?


SET SHOWPLAN_TEXT ON
GO

-- ... put query here - e.g.:

SELECT * FROM Northwind.dbo.Products

GO
SET SHOWPLAN_TEXT OFF
GO

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-22 : 13:00:33

quote:
Originally posted by Jeff Moden

Care to either explain "parameter sniffing" or include your favorite URL on the subject?

--Jeff Moden

sure.. here's the first one I found when I googled: http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-23 : 11:06:22

Heh... I got the same one... thought you had a favorite. Thanks Dinakar.

--Jeff Moden

ashawley
Starting Member

3 Posts

Posted - 2007-09-24 : 12:49:38

Hey all. Thanks for the tips. Turns out it was indeed Parameter Sniffing. We were able to solve it by restarting SQL Server and running the "problematic" version of the query first. Not both run at the same speed.

If there's an easier way in SQL2K to fix Parameter Sniffing issues w/o restarting I'd like to know.

I couldn't use the methods outlined in the solutions linked above because we weren't using a stored proc. This is a dynamically generated query based on a bunch of parameters from a web app. So we were stuck w/trying the restart method.

Intersting "issue" though.

Thanks,

Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources