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.
In MSSQL there is no such thing as getdate()-7.
Why not just trim the idea a bit?
SELECT distinct iUserID
FROM OrderHistory
WHERE tDate between dateadd(day, -7, getdate()) and
getdate()
Stephen Russell
DBA / .Net Developer
Memphis TN 38115
901.246-0159
"A good way to judge people is by observing how they treat those who
can do them absolutely no good." ---Unknown
http://spaces.msn.com/members/srussell/
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.467 / Virus Database: 269.7.0/803 - Release Date: 5/13/2007
12:17 PM
_______________________________________________
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.
