Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

Query question

Hi,
I am logging process data every 5 min, every record has a datetime field in
addition to the process data one of the requierements of the application is
to avg the data every 3 hours. is there any simple way to accomplish this
with a query or will it be better create a job that runs every 3 hrs and
average the last 3 hrs and insert the results into a new table.
ThanksJulio
If you need to agregate the data every three hours , so yes , create a job
"Julio Delgado" <jdelgado89@.hotmail.com> wrote in message
news:OWLI7ip4GHA.3400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I am logging process data every 5 min, every record has a datetime field
> in addition to the process data one of the requierements of the
> application is to avg the data every 3 hours. is there any simple way to
> accomplish this with a query or will it be better create a job that runs
> every 3 hrs and average the last 3 hrs and insert the results into a new
> table.
> Thanks
>

query problem

i have a msde 2000 database, and one of the field are filled by datetime with the format "yyyy-mm-dd hh:mm:ss".
I make a query like this:
"select * from mytable where myfield like '%2006-06-15%'"
but i didn't get any record for the result and i have make it sure that there are lot of records with that value in myfield. The main problem is the char "-" that separate the date value, becouse if i make a query :
"select * from mytable" then i get all of my records.
Is there any suggest to solf my problem?why u want a like clause on a date time field.

Instead try myfield >= '2006-06-15' and myfield < '2006-06-16'|||Is your date field a datetime datatype, or is it a string?|||Your date is actually formatted more like 0x000097E2008BE35B even though SQL Server displays it as 2006-06-16 13:29 when it returns a value to the client machine. You can use string patterns on it, and SQL Server will happily convert for you, but it isn't efficient and worse yet it isn't always predictable.

The suggested syntax that Ronin gave is both efficient and predictable.

-PatP|||0x000097E2008BE35B?

Is is that late already? I missed my 0x000097E200A4CB80 meeting!

Query Problem

I am stuck with a query. May be I am missing something.Plz have a look...

CREATE TABLE [Order](
Orderid VARCHAR(10),
orderdate DATETIME)

CREATE TABLE Product(
Prodid VARCHAR(10),
ProdDes VARCHAR(20),
ProdPrice INT)

CREATE TABLE OrdDetails(
OrddetailsID VARCHAR(10),
OrdID VARCHAR(10),
ProdID VARCHAR(10))

INSERT INTO [Order] VALUES ('1','5/11/2006')
INSERT INTO [Order] VALUES ('2','5/11/2006')
INSERT INTO [Order] VALUES ('3','6/11/2006')

INSERT INTO Product VALUES ('1','TV',16)
INSERT INTO Product VALUES ('2','LCD',20)
INSERT INTO Product VALUES ('3','DVD',9)
INSERT INTO Product VALUES ('4','MP',19)

INSERT INTO OrdDetails VALUES ('1','1','1')
INSERT INTO OrdDetails VALUES ('2','1','2')
INSERT INTO OrdDetails VALUES ('3','1','3')
INSERT INTO OrdDetails VALUES ('4','2','2')
INSERT INTO OrdDetails VALUES ('5','2','4')
INSERT INTO OrdDetails VALUES ('6','3','2')

--Query

select dd.orderdate,

max(dd.totprice)

FROM

(select TOP 100 PERCENT dbo.[Order].orderdate,
dbo.[Order].Orderid,
SUM(dbo. Product .ProdPrice) AS TotPrice
FROM dbo.OrdDetails INNER JOIN
dbo.[Order] ON dbo.OrdDetails.OrdID = dbo.[Order].Orderid INNER JOIN
dbo. Product ON dbo.OrdDetails.ProdID = dbo. Product .Prodid
GROUP BY dbo.[Order].orderdate, dbo.[Order].Orderid
ORDER BY dbo.[Order].orderdate, dbo.[Order].Orderid)dd
group by dd.orderdate

--Query
Drop table [Order]
Drop Table Product
Drop Table OrdDetails

The output needed is maximum summation of the order no ,I mean group by orderdate then orderno

Orderdate Ordernumber
5 nov 1
6 nov 3

at this momnet I am getting the orderdate and price
Plz help
Thanks!!"maximum summation of the order no" ???|||"maximum summation of the order no" ???
Thanks Rudy,
The requirement is -
Date Orderid Sum(price)

5/11/2006 1 41
5/11/2006 2 39
6/11/2006 3 20

Now in each date we need to pick the highest sum(price) with respective to orderid
So the final ouptput should be
Orderdate Orderid
5/11/2006 1
6/11/2006 3
I think now its a bit more clear...|||select O.orderdate
, O.Orderid
, sum(P.ProdPrice) AS TotPrice
from dbo.[Order] as O
inner
join dbo.OrdDetails as OD
on OD.OrdID = O.Orderid
inner
join dbo.Product as P
on P.Prodid = OD.ProdID
group
by O.orderdate
, O.Orderid
having sum(P.ProdPrice) =
( select max(TP)
from (
select sum(dbo.Product.ProdPrice) AS TP
from dbo.[Order]
inner
join dbo.OrdDetails
on dbo.OrdDetails.OrdID
= dbo.[Order].Orderid
inner
join dbo.Product
on dbo.Product.Prodid
= dbo.OrdDetails.ProdID
where dbo.[Order].orderdate
= O.orderdate
group
by dbo.[Order].Orderid
) as same_day_orders
)
order
by O.orderdateresults:

2006-05-11 00:00:00 1 45
2006-06-11 00:00:00 3 20|||So what you really want is to find the orderid and orderdate for the order with the largest total price for a given day? If that is the case, then the best way that I know to get that answer is in stages, something like:CREATE TABLE #ordersByDate (
orderdate DATETIME
, orderid INT
, TotPrice INT
)

INSERT INTO #ordersByDate (
orderdate, orderid, TotPrice
) SELECT Convert(DATETIME, Convert(CHAR(10), o.orderdate, 121)) AS orderdate
, o.orderid
, Sum(p.ProdPrice) AS TotPrice
FROM dbo.[Order] AS o
INNER JOiN dbo.ordDetails AS od
ON (od.ordID = o.orderid)
INNER JOIN dbo.Product AS p
ON (p.prodId = od.ProdId)
GROUP BY o.orderdate, o.orderid

SELECT orderdate, orderid
FROM #ordersByDate AS obd
WHERE obd.TotPrice = (SELECT Max(z1.TotPrice)
FROM #ordersByDate AS z1
WHERE z1.orderdate = obd.orderdate)
ORDER BY obd.orderdate, obd.orderid

DROP TABLE #ordersByDateThe problem is that this kind of question "pokes at the seams" of the SQL Engine. Because this kind of query requires careful sequencing of query evaluation in order to determine what occurs where/when, and SQL (like most query languages) doesn't offer explicit control of sub-expression evaluation. That's actually a blessing, because you get really complicated really fast when you try to handle things like that in a language, and simply making sequential steps is easy to write, read, and understand.

I build a temp table, and populate it very similar to your derived table dd. Once I've populated that, I make a two stage pass through it to compare this row with the largest order for this row's date, and only return this row in the result set if it is the largest. Just a word of warning, this code does NOT eliminate ties, so it is possible to get multiple rows returned for a given date.

R937's solution is elegant, and it is a single SQL operation, but for large result sets I think it will be rather slow because of the work that it needs to do to generate every row.

-PatP|||Thank you Great Guys!! :) :)
Thanks for those wonderful solutions...
Wishing you both A very HAPPY NEW YEAR 2007!!
And wish you all a wonderful and prosperous New Year 2007.
Enjoy !!!
:beer:

Query plans

I am a bit confused after reading a SQL Server Magazine
article about query plans
If i have a query
DECLARE @.odate AS DATETIME
SET @.odate = '19980506'
SELECT * FROM Orders
WHERE OrderDate >= @.odate
GO
Will above query planbe different than
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
If yes why ?
Bot abobe queries will be compliled and execution plan
will be prepared and then executed
Thanks
Sanjaythere may or may not be a different plan,
there may be slightly difference statistical estimates of
the row count involved
in the first, the optimizer will estimate the number of
rows involved for any generic value of OrderDate.
in the second, it will try to get a better estimate based
on the Orderdate value or '19980506'
>--Original Message--
>I am a bit confused after reading a SQL Server Magazine
>article about query plans
>If i have a query
> DECLARE @.odate AS DATETIME
> SET @.odate = '19980506'
> SELECT * FROM Orders
> WHERE OrderDate >= @.odate
> GO
>Will above query planbe different than
> SELECT * FROM Orders
> WHERE OrderDate >= '19980506'
>If yes why ?
>Bot abobe queries will be compliled and execution plan
>will be prepared and then executed
>Thanks
>Sanjay
>
>.
>|||Sanjay
For a variable in a open-ended range (i.e. < or >) the optimizer will
estimate that 30% of the rows in the table will meet the cristeria. That is
far to many to use an index seek with a nonclustered index, but a clustered
index could be considered.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:311501c373f8$ebe59080$a501280a@.phx.gbl...
> What is that estimate in first case, is it fixed like 10%
> 20% etc, is it selective enough to do a index seek
> Sanjay
> >--Original Message--
> >there may or may not be a different plan,
> >there may be slightly difference statistical estimates of
> >the row count involved
> >in the first, the optimizer will estimate the number of
> >rows involved for any generic value of OrderDate.
> >in the second, it will try to get a better estimate based
> >on the Orderdate value or '19980506'
> >
> >
> >>--Original Message--
> >>I am a bit confused after reading a SQL Server Magazine
> >>article about query plans
> >>
> >>If i have a query
> >> DECLARE @.odate AS DATETIME
> >> SET @.odate = '19980506'
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= @.odate
> >> GO
> >>
> >>Will above query planbe different than
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= '19980506'
> >>
> >>If yes why ?
> >>Bot abobe queries will be compliled and execution plan
> >>will be prepared and then executed
> >>
> >>Thanks
> >>Sanjay
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Query performance

have a database table. The table has number of fields. Out of those fields one is Company and
another is DateTime. The table has thousands of records. I want to get the most recent
record for each company. In order to do that I am using the following query

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE Company=X.Company)
ORDER BY Company

Note: There is only one record exists for the given company on a given date

The problem is that this query is very slow. Am I doing something wrong or there could be another
alternative way to improve it ?

Just to clarify that I am using Access database

Thanks in advance
KDV

This forum is for SQL Server so you should post this in a MS Access forum. You should check if you have the required indexes on the table. For this example, it would help to have index on Company and DateTime columns.

Query over two columns

Hi,
I have a table that includes two Datetime columns. The first column holds
the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the
table between a date time range.
i.e return all events between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Maccawhy do you store it in two datetime columns?
or is it 2 varchar columns?
If so then try this.
select * from [a table]
where cast(datecol + ' ' + timecol as datetime) between
'2006/5/8 09:00:00' and '2006/5/10 21:30:00'|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?

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]

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_timeAndrew
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...
> > 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
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...
> 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...
> > > 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
> > >
> > >
> >
> >
> >

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_timeAndrew
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 ( moreove
r
> ,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]