query on datetime column with

Hi,
I want to query my table using datetime column, which is indexed. One query
with covert function, one without. Funny thing is the one with convert
function is much faster than without. Should not be?
-- query 1, cost 11.48%
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
--query 2, cost 88.52%
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
pos_settlementdate = '2004-07-05 00:00:00'
order by pos_trandate_time
Andrew
What kind of index is defined on this column?
I have my doubts that you have seen an INDEX SEEK on this column ( moreover
,you have two columns taking part in WHERE clause)
What amount of data that you are queried?
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> Hi,
> I want to query my table using datetime column, which is indexed. One
query
> with covert function, one without. Funny thing is the one with convert
> function is much faster than without. Should not be?
> -- query 1, cost 11.48%
> 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
> --query 2, cost 88.52%
> 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
> pos_settlementdate = '2004-07-05 00:00:00'
> order by pos_trandate_time
>
|||Thanks for your reply, Uri,
this how I create the index:
CREATE INDEX idx_pos_txn_log1
ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
GO
These two queries give me same 66 records, out of 7 million records in one
table.
I am really new to SQL server, can you tell me what's index seek?
regards
andrew
"Uri Dimant" wrote:

> Andrew
> What kind of index is defined on this column?
> I have my doubts that you have seen an INDEX SEEK on this column ( moreover
> ,you have two columns taking part in WHERE clause)
> What amount of data that you are queried?
>
>
>
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:C118DD02-7B93-4835-8FC6-12D0AB41C434@.microsoft.com...
> query
> pos_seq_no
> and
> pos_seq_no
> and
>
>
|||Andrew
Index seek means that there is non clustered index on the table and Query
Optimizer is able to get the data directly from the index page by using
pointers in the leaf level of B-Tree and not scaning entire table.
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:0D2CF5FE-0D34-4BAF-A9BA-A188AC2A55DF@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply, Uri,
> this how I create the index:
> CREATE INDEX idx_pos_txn_log1
> ON pos_txn_log(pos_tid, pos_rid, pos_settlementdate)
> GO
> These two queries give me same 66 records, out of 7 million records in one
> table.
> I am really new to SQL server, can you tell me what's index seek?
> regards
> andrew
>
> "Uri Dimant" wrote:
moreover[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]
CONVERT(char(8),[vbcol=seagreen]