query on primary key field

Hello, I have a query on primary key fields, the performance very bad. it
takes about 111 seconds to get no record found, out of 5 million records in
one table.
It is totally unacceptable, what I can do:
1. create non-cluster index? from what I know since the query fields are
primary key, this should be not necessary.
2. remove all those convert function in the query? but from the query
anlyzer result, seems there are not so much computation time
consumed.(I/O=60, CPU=2.65)
Here are the query:
select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
=? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
cc_trandate_time
Any idea? Thanks in advance.
AndrewYes, I am assuming that cc_tid is your Primary Key field? Make sure that it
is indexed, regardless. If it is, it is already indexed but make sure that
it is not the Clustered Index. A better choice of a Clustered Index would be
your cc_trandate_time field as it would be a good candidate for a range
query, which you are doing, it is not "too" wide, and is not likely to be
updated frequently.
What is doe the Optimizer estimated execution plan look like? Could you
publish the text results with the sub-tree estimated costs?
Also, what is the expected number of returned rows? How many cpus and
memory on this server? How many different independent disks is this database
distributed accross?
Thanks.
Sincerely,
Anthony Thomas
"Andrew" wrote:
> Hello, I have a query on primary key fields, the performance very bad. it
> takes about 111 seconds to get no record found, out of 5 million records in
> one table.
> It is totally unacceptable, what I can do:
> 1. create non-cluster index? from what I know since the query fields are
> primary key, this should be not necessary.
> 2. remove all those convert function in the query? but from the query
> anlyzer result, seems there are not so much computation time
> consumed.(I/O=60, CPU=2.65)
> Here are the query:
> select CONVERT(char(8), cc_trandate_time, 3) as tx_date, CONVERT(char(8),
> cc_trandate_time, 8) as tx_time, cc_tran_amt as tx_amt, cc_seq_no as
> tx_seq_no, cc_trantype as tx_tran_type from cashcard_txn_log where cc_tid
> =? and cc_trandate_time >= ? and cc_trandate_time <= ? order by
> cc_trandate_time
> Any idea? Thanks in advance.
> Andrew