Mysteriously slow select

[JaybeeinTekTips]

Hi all,

I've got some code here that runs for about a minute without the red entries, but for 100 minutes (and more) with them - any ideas, people?

Thanks,

Jaybee.

SET NOCOUNT ON ;
Select DISTINCT (tblAccounts.accountID) ,
tblAccounts.accountFirstName ,
tblAccounts.accountLastName,
tblServicesTree.serviceName,
tblcommunicationsSent.SentTo


FROM dbo.fn_getValidSubscriptions() as vs
JOIN tblAccounts on tblAccounts.accountID = vs.accountID
JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID
JOIN tblcommunicationsSent on tblcommunicationsSent.accountID = vs.accountID
WHERE tblAccounts.accountEnabled <> 0 AND tblAccounts.accountSubscriptionsEnabled <> 0
and tblAccounts.accountID <> 3159
AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

order by tblAccounts.accountLastname, tblAccounts.accountFirstname

 

[LNBruno]

without the red entries

What "red entries?"

< M!ke >
I am not a hamster and life is not a wheel.

 

[JaybeeinTekTips]

I'll use blue!!

SET NOCOUNT ON ;
Select DISTINCT (tblAccounts.accountID) ,
tblAccounts.accountFirstName ,
tblAccounts.accountLastName,
tblServicesTree.serviceName,
[blue]tblcommunicationsSent.SentTo[/blue]



FROM dbo.fn_getValidSubscriptions() as vs
JOIN tblAccounts on tblAccounts.accountID = vs.accountID
JOIN tblServicesTree on tblServicesTree.serviceID = vs.serviceID
[blue]JOIN tblcommunicationsSent on tblcommunicationsSent.accountID = vs.accountID[/blue]
WHERE tblAccounts.accountEnabled <> 0 AND tblAccounts.accountSubscriptionsEnabled <> 0
and tblAccounts.accountID <> 3159
AND tblAccounts.ntUserID NOT IN ('Domain1','Domain2','Domain3')

order by tblAccounts.accountLastname, tblAccounts.accountFirstname

 

[SQLSister]

At a guess, I'd say it's the distinct that is causing the problem. Adding another join gives that many more records to go through and find the distinct values for.

"NOTHING is more important in a database than integrity." ESquared

 

[LNBruno]

Well, first guess: index on tblcommunicationsSent?

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLSister]

Could you post some sample data and sample results?
Maybe we could help see how you could approach this differntly that way.

"NOTHING is more important in a database than integrity." ESquared

 

[Lamprey13]

Two things (besides the index):

If you can change the "<> 0" to "= 1" thay might make a difference. Also, if you are running SQL2000 and those are bit fields you will gain some performace if you cast. for example: tblAccounts.accountEnabled = CAST(1 AS BIT)