Fast and Slow (simple select statement)

Question

Fast and Slow (simple select statement)

eamonroche

SSCommitted

Points: 1678
More actions
December 6, 2005 at 9:06 am

#102971

the sql code below works but it takes 35 seconds to run, even though my tblUsers table is very small (600 records).
However when I comment out the "inner join" line, the statement runs instantaneously.
Also if I remove the first part of the "OR" in the where clause it also works instananeously
ps: the purpose of the "where clause" is to detect if the user is in a certain category, but only if the category is being filtered for
can anyone tell me what is causing the code to be sluggish or what i can do to fix.
Thanks in advance

**************
declare @intContactCategory int
select @intContactCategory = 9
SELECT
pk_intUserID
from dbo.tblUsers U
inner join dbo.tblAgencies A on U.fk_intAgencyID = A.pk_intAgencyID
where
(@intContactCategory is null or U.pk_intUserID in (select CC.fk_intUserID from dbo.tblContactCategories_Users CC where CC.fk_intContactCategoryID = @intContactCategory))
***************

Viewing 12 posts - 1 through 11 (of 11 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

>>even though my tblUsers table is very small (600 records).

Right. But what about the size of the other 2 tables ? What size are tblAgencies and tblContactCategories *and* how are they indexed.

In short, please post the DDL of your tables.

eamonroche SSCommitted Points: 1678 More actions · Answer 2

Thanks. both the tblAgencies and tblContactCategories tables are also small (10 records and 400 records). here are the DDLs:

tblUsers:

CREATE TABLE [tblUsers] (

[pk_intUserID] [int] IDENTITY (1, 1) NOT NULL ,

[fk_intAgencyID] [int] NOT NULL ,

[vcFirstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vcLastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vcUsername] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vcPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[DF_tblUsers_bPrivate_address] DEFAULT (0),

CONSTRAINT [PK_tblUsers] PRIMARY KEY NONCLUSTERED

(

[pk_intUserID]

&nbsp ON [PRIMARY] ,

CONSTRAINT [IX_tblUsers_1] UNIQUE NONCLUSTERED

(

[vcUsername]

&nbsp ON [PRIMARY] ,

CONSTRAINT [FK_tblUsers_tblAgencies] FOREIGN KEY

(

[fk_intAgencyID]

&nbsp REFERENCES [tblAgencies] (

[pk_intAgencyID]

&nbsp,

CONSTRAINT [FK_tblUsers_tblJobDescriptions] FOREIGN KEY

(

[fk_intJobDescriptionID]

&nbsp REFERENCES [tblJobDescriptions] (

[pk_intJobDescriptionID]

&nbsp

go

tblAgencies:

CREATE TABLE [tblAgencies] (

[pk_intAgencyID] [int] IDENTITY (1, 1) NOT NULL ,

[fk_intAgencyTypeID] [int] NOT NULL ,

[vcAgencyName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[vcAgencyAbbrev] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[bEnabled] [bit] NOT NULL CONSTRAINT [DF_tblAgencies_bEnabled] DEFAULT (1),

CONSTRAINT [PK_tblAgencies] PRIMARY KEY CLUSTERED

(

[pk_intAgencyID]

&nbsp ON [PRIMARY] ,

CONSTRAINT [FK_tblAgencies_tblAgencyTypes] FOREIGN KEY

(

[fk_intAgencyTypeID]

&nbsp REFERENCES [tblAgencyTypes] (

[pk_intAgencyTypeID]

&nbsp

) ON [PRIMARY]

GO

ron k SSCertifiable Points: 6103 More actions · Answer 3

This is most probably the part that is the cause of it to be slow:

or U.pk_intUserID in (select CC.fk_intUserID from dbo.tblContactCategories_Users CC where CC.fk_intContactCategoryID = @intContactCategory))

The IN gets converted to something like this:

U.pk_intUserId = "AA" Or

U.pk_intUserId = "bb" OR

u.pk_intUserid = "cc" Or ...

397 more times for each user id even if a match is found on the very first user id.

Try converting this to using EXISTS instead of IN. EXISTS will stop processing and return "true" as soon as a match is found.

Ron

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Haven't looked at the DDLs yet, but in your where clause, you're not using a column name...

"where

(@intContactCategory is null or U.pk_intUserID in (select CC.fk_intUserID from dbo.tblContactCategories_Users CC where CC.fk_intContactCategoryID = @intContactCategory))"...

You're using a variable "@intContactCategory" there..use the column name instead...

**ASCII stupid question, get a stupid ANSI !!!**

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

>>Haven't looked at the DDLs yet, but in your where clause, you're not using a column name...

My assumption is that @intContactCategory needs to act as "all categories" if it contains Null, hence that construct.

Is this correct ?

If so, it might help the optimizer if you build an IF.. THEN..ELSE and not bother hitting the tblContactCategories table if "all categories" is the required query.

ron k SSCertifiable Points: 6103 More actions · Answer 6

I think this works:

(@intContactCategory is null or EXISTS (select CC.fk_intUserID from dbo.tblContactCategories_Users CC where CC.fk_intContactCategoryID = @intContactCategory

And cc.fk_intUserId = U.pk_UserId))

Ron

Edit: An index on cc.fk_intContactCategoryID + cc.fk_intUserId OR UserId/ContactCategory would be most helpful.

eamonroche SSCommitted Points: 1678 More actions · Answer 7

thanks ron.

yes, the EXISTS variation returns the same correct recordset but unfortunately still at 35sec

to answer your earlier q:

@intContactCategory is null

acts as ALL categories or NONE

ron k SSCertifiable Points: 6103 More actions · Answer 8

I really think this will perform well with the proper indexes as I suggested above.

ron

eamonroche SSCommitted Points: 1678 More actions · Answer 9

Ron, Just tried your indexing....this does the trick!!!

Thanks.

So, you think i should use EXISTS instead of IN? Performs better?

Thanks again

ron k SSCertifiable Points: 6103 More actions · Answer 10

Yes, EXISTS should give you better performance. The greater the number of potential items within the IN the better the performance of EXISTS over using IN.

ron

P. S. With all due credit to Remi who first pointed this out to me.

sushila SSC-Dedicated Points: 35293 More actions · Answer 11

I'm still curious about how this would work when "@intContactCategory is null" given the t-sql & where clause in the first post (unless it has been changed to follow PW's suggestion)?!?!?!...

**ASCII stupid question, get a stupid ANSI !!!**