query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
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
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:
> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> 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
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> > Hi,
> >
> > My query 1 needs to narrow down a time range. I index the column
> > (pos_trandate_time, plus the other on the where clause), but the
> performance
> > still very slow, around 1 minutes. Wherease the query on date only is much
> > faster. What should I do?
> >
> > --query 1, cost 99%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> > pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> > as tx_msg_type, pos_trans_code as tx_tran_type
> > from pos_txn_log (index=pk_pos_txn_log)
> > where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> > and pos_trandate_time <= '2004-07-05 23:59:59'
> > order by pos_trandate_time
> >
> > -- query 2, cost %1
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > order by pos_trandate_time
> >
> >
> > Thanks
> > Wang
>
>