CODEWITHSUNDEEP
sql-server
Chio
Chio

Reputation: 63

To get the number of orders placed on the current day

I am using sql-server 2012

The query is 

1.select count(*) from table where orderti=getdate()

2.select count(*) from table where orderti>=convert(date,getdate()) and orderti<
  dateadd(day,convert(date,getdate())

the table structure is:

sales(orderti datetime)

non clustered index on orderti.

I want to know what is the difference in writing styles of 2 queries mentioned above.

which one is efficient ?

any help? Thanks, Chio

Upvotes: 0

Views: 281

Answers (2)

Karthik Kola
Karthik Kola

Reputation: 93

I would prefer option-2 due to couple of reasons

  1. Better don't wrap filtered columns with functions as standard practice, not valid in the case of course because optimizer is cleaver in 2012 version.

  2. Although 2012 version of optimizer is using index seek which dosen't mean it saves CPU cycles for CAST

Upvotes: 0

ughai
ughai

Reputation: 9880

Based on your question

Query 1 

select count(*) from table where orderti=getdate()

This query will not give you the orders for the current day because

  1. orderti is datetime and will contain the time portion as well
  2. getdate() contains both the current date and time as well

This this query is trying to do is to get all orders with orderti = current date and time which is not what you require.

The query which you are looking for is this

select count(*) from table where CONVERT(DATE,orderti)=CONVERT(DATE,getdate())

Query 2

The query you are looking for is

select count(*) from table where orderti>=convert(date,getdate()) and orderti< dateadd(day,1,convert(date,getdate()))

On your question

which one is efficient ?

Based on the statistics and execution plan, both query do a index seek and are equally efficient.

Table 'sales'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'sales'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plans Update: With around 650k records there are variations in the statistics however the plan remains the same.

Table 'sales'. Scan count 1, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sales'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I would recommend Query 2 as it has lesser logical reads and doesn't have a CAST/Convert.

Upvotes: 1

Related Questions