Help - Query with index problem

Hi all
I have a table which have four index. I found that some vales i can run the query which hit the index and some value cannot. The details as follows
Table : mytabl
Index : my_index_4 ( company_code + order_type + order_date
Query 1: select * from mytable where company_code='100' and order_type='ABC' and
order_date between '2003-01-01' and '2003-12-31
Query 2: select * from mytable where company_code='100' and order_type='ABC' and
order_date between '2002-01-01' and '2002-12-31
Query 3: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and
order_date between '2002-01-01' and '2002-12-31
Query 1 & 3 only need 1 second to return the result set. But Query 2 need more than 20 second to run. I check it and found that the Query will not hit the index. It will use table scan.
Can you tell me why (I tried to re-built the index, update statistic) ? How can I fix it without using the "index="
Regards
SimoDo you have an index on order_date? Clustered or non-clustered? How many
rows does query 1 vs. 2 return? Out of how many in the table? How many pages
does the table use?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:EC349819-01AD-4A88-87A1-48B4FA4836DE@.microsoft.com...
> Hi all,
> I have a table which have four index. I found that some vales i can run
the query which hit the index and some value cannot. The details as
follows.
> Table : mytable
> Index : my_index_4 ( company_code + order_type + order_date)
> Query 1: select * from mytable where company_code='100' and
order_type='ABC' and
> order_date between '2003-01-01' and '2003-12-31'
> Query 2: select * from mytable where company_code='100' and
order_type='ABC' and
> order_date between '2002-01-01' and '2002-12-31'
> Query 3: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and
> order_date between '2002-01-01' and '2002-12-31'
> Query 1 & 3 only need 1 second to return the result set. But Query 2 need
more than 20 second to run. I check it and found that the Query will not
hit the index. It will use table scan.
> Can you tell me why (I tried to re-built the index, update statistic) ?
How can I fix it without using the "index=".
> Regards,
> Simon
>|||Hi Simon
The only base not covered in your solutions is statistics
distribution - SQL Server is deciding that for last year's
data, a table scan is better, but for this year's the
index would be more efficient.
The UPDATE STATISTICS options you used are not specified
but you may want to start with
UPDATE STATISTICS ... WITH FULLSCAN, INDEX and work
downwards from there.
If the Optimizer then chooses correctly, you might want to
reduce to a 50% scan, then reduce to 25% or increase to
75% depending on the 50% result.
Brian
>--Original Message--
>Hi all,
>I have a table which have four index. I found that some
vales i can run the query which hit the index and some
value cannot. The details as follows.
>Table : mytable
>Index : my_index_4 ( company_code + order_type +
order_date)
>Query 1: select * from mytable where company_code='100'
and order_type='ABC' and
> order_date between '2003-01-01' and '2003-
12-31'
>Query 2: select * from mytable where company_code='100'
and order_type='ABC' and
> order_date between '2002-01-01' and '2002-
12-31'
>Query 3: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and
> order_date between '2002-01-01' and '2002-
12-31'
>Query 1 & 3 only need 1 second to return the result set.
But Query 2 need more than 20 second to run. I check it
and found that the Query will not hit the index. It will
use table scan.
>Can you tell me why (I tried to re-built the index,
update statistic) ? How can I fix it without using
the "index=".
>Regards,
>Simon
>
>.
>|||I tried to run "UPDATE STATISTICS mytable WITH FULLSCAN, INDEX "
It give the same result.
-- Brian Katz wrote: --
Hi Simon
The only base not covered in your solutions is statistics
distribution - SQL Server is deciding that for last year's
data, a table scan is better, but for this year's the
index would be more efficient.
The UPDATE STATISTICS options you used are not specified
but you may want to start with
UPDATE STATISTICS ... WITH FULLSCAN, INDEX and work
downwards from there.
If the Optimizer then chooses correctly, you might want to
reduce to a 50% scan, then reduce to 25% or increase to
75% depending on the 50% result.
Brian
>--Original Message--
>Hi all,
>>I have a table which have four index. I found that some
vales i can run the query which hit the index and some
value cannot. The details as follows.
>>Table : mytable
>Index : my_index_4 ( company_code + order_type +
order_date)
>>Query 1: select * from mytable where company_code='100'
and order_type='ABC' and
> order_date between '2003-01-01' and '2003-
12-31'
>>Query 2: select * from mytable where company_code='100'
and order_type='ABC' and
> order_date between '2002-01-01' and '2002-
12-31'
>>Query 3: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and
> order_date between '2002-01-01' and '2002-
12-31'
>>Query 1 & 3 only need 1 second to return the result set.
But Query 2 need more than 20 second to run. I check it
and found that the Query will not hit the index. It will
use table scan.
>>Can you tell me why (I tried to re-built the index,
update statistic) ? How can I fix it without using
the "index=".
>>Regards,
>Simon
>>.
>|||The table around 900,000 records. The query 1 and query 2 return about 5000 records.
All index are non-clustered index. Other than my_index_4, there are two other index with the filed "order_date".
Index : my_index_4 ( company_code + order_type + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_2 ( company_code + cutomer_no + order_date)
-- Tibor Karaszi wrote: --
Do you have an index on order_date? Clustered or non-clustered? How many
rows does query 1 vs. 2 return? Out of how many in the table? How many pages
does the table use?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:EC349819-01AD-4A88-87A1-48B4FA4836DE@.microsoft.com...
> Hi all,
>> I have a table which have four index. I found that some vales i can run
the query which hit the index and some value cannot. The details as
follows.
>> Table : mytable
> Index : my_index_4 ( company_code + order_type + order_date)
>> Query 1: select * from mytable where company_code='100' and
order_type='ABC' and
> order_date between '2003-01-01' and '2003-12-31'
>> Query 2: select * from mytable where company_code='100' and
order_type='ABC' and
> order_date between '2002-01-01' and '2002-12-31'
>> Query 3: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and
> order_date between '2002-01-01' and '2002-12-31'
>> Query 1 & 3 only need 1 second to return the result set. But Query 2 need
more than 20 second to run. I check it and found that the Query will not
hit the index. It will use table scan.
>> Can you tell me why (I tried to re-built the index, update statistic) ?
How can I fix it without using the "index=".
>> Regards,
> Simon
>>|||Seems like SQL Server estimates one query to return more rows than the other query. The query plan
should tell you this information. Since you do SELECT *, you effectively eliminate the option for
SQL Server to use a covering index. Do you really need all columns?
Since you don't have a clustered index and SQL Server cannot cover the query, SQL Server has to
visit one page for each row to return. I.e., SQL Server has to visit 5000 pages.
Creating a clustered index on order_date will probably make the query very fast...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"simon" <anonymous@.discussions.microsoft.com> wrote in message
news:9FCD7B02-8132-4D9A-B13C-B39BC3E7432C@.microsoft.com...
> The table around 900,000 records. The query 1 and query 2 return about 5000 records.
> All index are non-clustered index. Other than my_index_4, there are two other index with the
filed "order_date".
> Index : my_index_4 ( company_code + order_type + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> Index : my_index_2 ( company_code + cutomer_no + order_date)
>
> -- Tibor Karaszi wrote: --
> Do you have an index on order_date? Clustered or non-clustered? How many
> rows does query 1 vs. 2 return? Out of how many in the table? How many pages
> does the table use?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Simon" <anonymous@.discussions.microsoft.com> wrote in message
> news:EC349819-01AD-4A88-87A1-48B4FA4836DE@.microsoft.com...
> > Hi all,
> >> I have a table which have four index. I found that some vales i can run
> the query which hit the index and some value cannot. The details as
> follows.
> >> Table : mytable
> > Index : my_index_4 ( company_code + order_type + order_date)
> >> Query 1: select * from mytable where company_code='100' and
> order_type='ABC' and
> > order_date between '2003-01-01' and '2003-12-31'
> >> Query 2: select * from mytable where company_code='100' and
> order_type='ABC' and
> > order_date between '2002-01-01' and '2002-12-31'
> >> Query 3: select * from mytable (index=my_index_4) where
> company_code='100' and order_type='ABC' and
> > order_date between '2002-01-01' and '2002-12-31'
> >> Query 1 & 3 only need 1 second to return the result set. But Query 2 need
> more than 20 second to run. I check it and found that the Query will not
> hit the index. It will use table scan.
> >> Can you tell me why (I tried to re-built the index, update statistic) ?
> How can I fix it without using the "index=".
> >> Regards,
> > Simon
> >>