Derek Kalweit wrote:
> Hello. I have this statement which is taking nearly 30 seconds to run,
> which is causing a performance bottleneck in a report:
>
> SELECT COUNT(*)
> FROM Users
> WHERE tCreated between getdate()-7 and getdate()
> AND iID IN
> (SELECT iUserID
> FROM OrderHistory
> WHERE tDate between getdate()-7 and getdate())
>
> If I change it to check JUST tCreated on the users table or just the
> iID IN, each run <2 seconds each. Having the and in there slows it
> down to the 30 seconds or so in the dataset I'm currently using. There
> are primary indexes on all iID columns, and regular indexes on
> Users.tCreated and OrderHistory.tDate.
>
> Does anyone have any idea why this would be slow? Is there something
> about MSSQL indexes that I'm not understanding?
>
> Thanks for any help.
>
>
Might try using variables so as not to call GetDate() so frequently, and
(NOLOCK) if your data permits.
Declare @From DateTime
Declare @To DateTime
set @To = GetDate()
set @From = @To - 7
SELECT COUNT(*)
FROM Users (NOLOCK)
WHERE tCreated between @From and @To
AND iID IN
(SELECT iUserID
FROM OrderHistory (NOLOCK)
WHERE tDate between @From and @To)
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
