I have a table which have four index. And I want to run a query which have
the criteria to match the index (my_index_4). However when i passed differen
t value to the query, the speed of the query will be different. Sometimes, t
he query cannot hit the ind
ex. The details as follows.
Table : mytable (about 900,000 records with no clustered index index)
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
*Query 1: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2003-01-01' and '2003-12-31'
*Query 2: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2002-01-01' and '2002-12-31'
Query 3: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2001-01-01' and '2001-12-31'
Query 4: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2000-01-01' and '2000-12-31'
Query 5: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1999-01-01' and '1999-12-31'
Query 6: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
Query 7: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
*Query 8: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '1997-01-01' and '1997-12-31'
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18
seconds. I found that those queries did not hit the index. Therefore, it us
ed table scan. Can you tell me why some queries did not use the index (my_in
dex_4). I tried to re-buil
t the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then
all queries (Query 9-12)take only 2 seconds.
Query 9: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
Query 10: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 11: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
There are about 5000 records for each query (one year for one company).
Thanks,
SimonSimon
Why did you create indexes on columns which kave already covered by another
index?
Look at this article helps you
http://www.sql-server-performance.c...ing_indexes.asp
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...
quote:
> Hi all,
> I have a table which have four index. And I want to run a query which
have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.
quote:
> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date
+ sequence_no)
quote:
> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> 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'
quote:
> *Query 2: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'
quote:
> Query 3: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'
quote:
> Query 4: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
quote:
> Query 5: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'
quote:
> Query 6: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
quote:
> Query 7: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
quote:
> *Query 8: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
quote:
> Query 1, 2& 5 take about 2 seconds. However, other queries take more than
18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.
quote:
> Then I rewrite the query using the "index=" to force it use the index.
Then all queries (Query 9-12)take only 2 seconds.
quote:
> Query 9: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'
quote:
> Query 10: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'
quote:
> Query 11: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'
quote:|||Also make sure the index statistics are up to date... It really sounds to me
> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>
like some of the queries return more records than others, which could cause
SQL to to a table scan... Use Query ANalyzer and look at the plan to see if
it changes between index seek and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...
quote:
> Hi all,
> I have a table which have four index. And I want to run a query which
have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.
quote:
> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date
+ sequence_no)
quote:
> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> 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'
quote:
> *Query 2: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'
quote:
> Query 3: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'
quote:
> Query 4: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
quote:
> Query 5: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'
quote:
> Query 6: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
quote:
> Query 7: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
quote:
> *Query 8: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
quote:
> Query 1, 2& 5 take about 2 seconds. However, other queries take more than
18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.
quote:
> Then I rewrite the query using the "index=" to force it use the index.
Then all queries (Query 9-12)take only 2 seconds.
quote:
> Query 9: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'
quote:
> Query 10: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'
quote:
> Query 11: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'
quote:|||> why some queries did not use the index (my_index_4). I tried to re-built
> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>
the index, and
quote:
> update statistic and it still have the same result.
Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman|||Sorry, wongly typed. The my_index_1 should be
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_no + se
quence_no)
========================================
=========================
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
-- Simon wrote: --
Hi all,
I have a table which have four index. And I want to run a query which have
the criteria to match the index (my_index_4). However when i passed differen
t value to the query, the speed of the query will be different. Sometimes, t
he query cannot hit th
e index. The details as follows.
Table : mytable (about 900,000 records with no clustered index index)
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
*Query 1: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2003-01-01' and '2003-12-31'
*Query 2: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2002-01-01' and '2002-12-31'
Query 3: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2001-01-01' and '2001-12-31'
Query 4: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2000-01-01' and '2000-12-31'
Query 5: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1999-01-01' and '1999-12-31'
Query 6: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
Query 7: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
*Query 8: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '1997-01-01' and '1997-12-31'
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18
seconds. I found that those queries did not hit the index. Therefore, it us
ed table scan. Can you tell me why some queries did not use the index (my_in
dex_4). I tried to re
-built the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then
all queries (Query 9-12)take only 2 seconds.
Query 9: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
Query 10: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 11: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
There are about 5000 records for each query (one year for one company).
Thanks,
Simon|||I try to re-built the index, update the stat, and do almost everything (etc
.dbcc.....). But all are failure.
But now, the problem is solved. The solution is to change the primary key t
o clustered index, and then re-built other index. Anyway, thanks all of you
r help.
Cheers,
Simon
-- ben brugman wrote: --
quote:
> why some queries did not use the index (my_index_4). I tried to re-built
the index, and
quote:
> update statistic and it still have the same result.
Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman
No comments:
Post a Comment