Performance : Queries taking very long time (Urgent)

The production_table has ~20 million records and has identity key which is
clustered and has non clustered index on Created_Date.
The following queries taking very long time as the plan using index scan.
Please advise for optimization.
select *
from production_table p -- production_table has ~10 million records
where datediff(dd, p.Created_Date, getdate()) = 7
select *
from production_table p -- -- production_table has ~10 million records
where datediff(mm, p.created_date, getdate()) = 1
Thanks in advance.
Hmm.. How many rows do the queries return ? Theres lots one could advice,
such as do you really need a select * or one could state, creating a
clustered index on Created_Date column or even trying to rewrite the where
clause something like
where created_date = getdate-7 and so on..
I think thats related to SARG and how the optimiser would pick the right
plan.. But with so many records, and with the select * in there, even if
does do an index seek , it would follow with a bookmark lookup to get the
rest of the data which could be expensive as well..
So without knowing a whole lot about ur application, it would be wrong for
me to really give the right solution...
Probably someone else can chime in with something better.
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
|||I would try running UPDATE STATISTICS initially, then try an index hint and
then consider clustering the date column rather than the identity column. You
could also test the use of calculated columns with indexes on the calculated
columns IE: day_number and month_number.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"DKRReddy" wrote:

> The production_table has ~20 million records and has identity key which is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>
>
|||Calculate the fixed date range before you query. Always list the
columns you require rather than use SELECT *.
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050131'
AND created_date < '20050201'
SELECT col1, col2, ...
FROM production_table
WHERE created_date >= '20050206'
AND created_date < '20050207'
David Portas
SQL Server MVP
|||David is correct,... when you use a function in a where clause, the
optimizer is unable to use index statistics to determine the best index.
Pre-determining the date ranges as David suggests will give the optimizer
visibility into the index statistics... Performance will then depend on the
number of rows that satisfy the where clause...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23$XOvrMDFHA.392@.TK2MSFTNGP14.phx.gbl...
> The production_table has ~20 million records and has identity key which
> is
> clustered and has non clustered index on Created_Date.
> The following queries taking very long time as the plan using index scan.
> Please advise for optimization.
> select *
> from production_table p -- production_table has ~10 million records
> where datediff(dd, p.Created_Date, getdate()) = 7
>
> select *
> from production_table p -- -- production_table has ~10 million
> records
> where datediff(mm, p.created_date, getdate()) = 1
>
> Thanks in advance.
>