Index enquiry

hi all,
I have a table with 700K records with the primary key as cluster index.
TableA {
chrRef char(10), -- key
chrStatus char(2),
dtTrade datetime,
dtSettle datetime,
....
}
When query the table by filter records on non-primay key, the performance is
acceptable (less than 10K records)
e.g. select * from TableA where dtTrade = '20060101'
When records has been grown to 700K, the query is quite slow. I have added
an index (IX_dtTrade) on the column "dtTrade" in order to reduce the query
time. However, i found that SQL server did not use the index (IX_dtTrade) to
speed up the query. SQL server still using the cluster index to retrieve
records. From the help, i found that there was a method to force SQL server
to use the index. As a result, the query time reduce a lot.
e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
For this case,
1) Is there any setup so that the SQL server will use the index (IX_dtTrade)
automatically without explicit the cluase (with index ())?
2) When create index, what is the difference between single index and
compound index in SQL server? It seems that when an index is created on the
column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle" (IX_dtTrade_dtSettle),
the query time is same.
3) If there are many queries filter on columns "dtTrade", "dtSettle" and
"chrStatus", create an index on each column or a compound index on the three
columns?
3) Is it the only way to speed up the query time by creating index on target
column? (provided that no change on number of records)
Any suggestions? Thank in advance!!
Regards,
MartinCheck the execution plan to see how many rows are estimated to be returned. If SQL Server estimates
a large number of rows, it will consider a scan more efficient than using a non-clustered index.
This is because using a non-clustered index, SQL Server will navigate the index, and *for each row*
access the data page. Imagine if you return 10 000 rows, then you have 10 000 data page accesses,
even if the whole table perhaps fits on 5 000 pages. This is easier to explain with a white-board.
Assuming the estimate is off, we need to figure out why. It could be several reasons, for instance:
Bad statistics.
The query you showed us is not what you are running.
You use a stored procedure and the data you search for is a parameter.
The data you search for is a variable.
The condition for the data isn't expressed as in your example.
As for your questions:
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
There's no "magic button" for this. See my above elaboration.
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
An index on several columns, say (a, b) , can be good for conditions like:
A = 2 AND B = 7
But not for:
B = 45
So you need to know your queries in order to create a good indexing strategy. If you are uncertain,
start by one index per column.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
See above.
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
If you don't give SQL Server any way to limit which rows it need to look for in order to determine
which satisfies your condition, well, then SQL Server need to look at each row. An index does just
that.
Also, don't do SELECT *. Only return the columns you need. The main importance for this isn't
perhaps to reduce network bandwidth. It is that you probably lose the ability to cover your queries
with a non-clustered index. Such an index has all the columns that the query need in it and SQL
Server doesn't have to access the data page for each row, the answer is in the index page.
This is a big topic, so I suggest you start studying and reading a bit. Books Online has some good
sections which is a good start. Kalen Delaney's "Inside SQL Server" book is very good at describing
these constructs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
> hi all,
> I have a table with 700K records with the primary key as cluster index.
> TableA {
> chrRef char(10), -- key
> chrStatus char(2),
> dtTrade datetime,
> dtSettle datetime,
> ....
> }
> When query the table by filter records on non-primay key, the performance is acceptable (less than
> 10K records)
> e.g. select * from TableA where dtTrade = '20060101'
> When records has been grown to 700K, the query is quite slow. I have added an index (IX_dtTrade)
> on the column "dtTrade" in order to reduce the query time. However, i found that SQL server did
> not use the index (IX_dtTrade) to speed up the query. SQL server still using the cluster index to
> retrieve records. From the help, i found that there was a method to force SQL server to use the
> index. As a result, the query time reduce a lot.
> e.g. select * from TableA with index (IX_dtTrade) where dtTrade = '20060101'
> For this case,
> 1) Is there any setup so that the SQL server will use the index (IX_dtTrade) automatically without
> explicit the cluase (with index ())?
> 2) When create index, what is the difference between single index and compound index in SQL
> server? It seems that when an index is created on the column "dtTrade" (IX_dtTrade) and "dtTrade,
> dtSettle" (IX_dtTrade_dtSettle), the query time is same.
> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and "chrStatus", create an
> index on each column or a compound index on the three columns?
> 3) Is it the only way to speed up the query time by creating index on target column? (provided
> that no change on number of records)
> Any suggestions? Thank in advance!!
> Regards,
> Martin
>|||thank you for your suggestion!!! really useful!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23UbAQXn9GHA.3384@.TK2MSFTNGP05.phx.gbl...
> Check the execution plan to see how many rows are estimated to be
> returned. If SQL Server estimates a large number of rows, it will consider
> a scan more efficient than using a non-clustered index. This is because
> using a non-clustered index, SQL Server will navigate the index, and *for
> each row* access the data page. Imagine if you return 10 000 rows, then
> you have 10 000 data page accesses, even if the whole table perhaps fits
> on 5 000 pages. This is easier to explain with a white-board.
> Assuming the estimate is off, we need to figure out why. It could be
> several reasons, for instance:
> Bad statistics.
> The query you showed us is not what you are running.
> You use a stored procedure and the data you search for is a parameter.
> The data you search for is a variable.
> The condition for the data isn't expressed as in your example.
> As for your questions:
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
> There's no "magic button" for this. See my above elaboration.
>
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
> An index on several columns, say (a, b) , can be good for conditions like:
> A = 2 AND B = 7
> But not for:
> B = 45
> So you need to know your queries in order to create a good indexing
> strategy. If you are uncertain, start by one index per column.
>
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
> See above.
>
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
> If you don't give SQL Server any way to limit which rows it need to look
> for in order to determine which satisfies your condition, well, then SQL
> Server need to look at each row. An index does just that.
> Also, don't do SELECT *. Only return the columns you need. The main
> importance for this isn't perhaps to reduce network bandwidth. It is that
> you probably lose the ability to cover your queries with a non-clustered
> index. Such an index has all the columns that the query need in it and SQL
> Server doesn't have to access the data page for each row, the answer is in
> the index page.
> This is a big topic, so I suggest you start studying and reading a bit.
> Books Online has some good sections which is a good start. Kalen Delaney's
> "Inside SQL Server" book is very good at describing these constructs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:%23q8OTNn9GHA.788@.TK2MSFTNGP05.phx.gbl...
>> hi all,
>> I have a table with 700K records with the primary key as cluster index.
>> TableA {
>> chrRef char(10), -- key
>> chrStatus char(2),
>> dtTrade datetime,
>> dtSettle datetime,
>> ....
>> }
>> When query the table by filter records on non-primay key, the performance
>> is acceptable (less than 10K records)
>> e.g. select * from TableA where dtTrade = '20060101'
>> When records has been grown to 700K, the query is quite slow. I have
>> added an index (IX_dtTrade) on the column "dtTrade" in order to reduce
>> the query time. However, i found that SQL server did not use the index
>> (IX_dtTrade) to speed up the query. SQL server still using the cluster
>> index to retrieve records. From the help, i found that there was a method
>> to force SQL server to use the index. As a result, the query time reduce
>> a lot.
>> e.g. select * from TableA with index (IX_dtTrade) where dtTrade =>> '20060101'
>> For this case,
>> 1) Is there any setup so that the SQL server will use the index
>> (IX_dtTrade) automatically without explicit the cluase (with index ())?
>> 2) When create index, what is the difference between single index and
>> compound index in SQL server? It seems that when an index is created on
>> the column "dtTrade" (IX_dtTrade) and "dtTrade, dtSettle"
>> (IX_dtTrade_dtSettle), the query time is same.
>> 3) If there are many queries filter on columns "dtTrade", "dtSettle" and
>> "chrStatus", create an index on each column or a compound index on the
>> three columns?
>> 3) Is it the only way to speed up the query time by creating index on
>> target column? (provided that no change on number of records)
>> Any suggestions? Thank in advance!!
>> Regards,
>> Martin
>