execution plan: query cost

hi experts, i have this query
-- query 1
select *
from dbo.Data
where DATEADD(day, 1, ADate) > GETDATE()
go

-- query 1
select *
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
go

this is statistics

(273 row(s) affected)
Table 'Data'. Scan count 1, logical reads 1141, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(273 row(s) affected)
Table 'Data'. Scan count 1, logical reads 847, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

i attached the execution plan, my questios is: what's the mean query cost 34% and 66%
executionPLAN.jpg
enrique_aeoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AnujSQL Server DBACommented:
SQL Server uses statistics, indexes details to create execution plans, based on this execution plan SQL Server will decide the best way to execute the query.

Here the cost of the query is calculated for a batch having two query, and cost of the first query related to the batch is only 34% and the second is 66%, meaning second query is expensive than the first one.
0
enrique_aeoAuthor Commented:
but the number of pages read is lower in the second query ... that does not mean that the second query is better?
0
Éric MoreauSenior .Net ConsultantCommented:
by any chance, do you have an index on ADate? Because the second query is using the raw value of ADate (and not a transformed value as in the first query), SQL can better use indexes. The proof of this is that you can see in the execution plan that your second query is doing a SEEK instead of a SCAN.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jogosCommented:
<<but the number of pages read is lower in the second query ... that does not mean that the second query is better?>>

When first query Sql must read the pages from disk. With second query pages (from same table) are still in buffer cache -> less reads

Just change both from order and the the same will happen.

To have correct statistics you must clean the cache as in next link, but don't do that on production! Only on your local system.
 http://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/
0
Éric MoreauSenior .Net ConsultantCommented:
jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.
0
AnujSQL Server DBACommented:
Personally, i feel
where ADate > DATEADD(day, -1, GetDate())  is efficient than

where DATEADD(day, 1, ADate) > GETDATE()

because in second query you have used a function on ADate column, that makes the where clause un SARGABLE meaning non searchable arguments and even if your ADate is indexed, the optimizer cannot make use of the index and result in SCAN instead of SEEK.

Also for the second query most of the cost is for the lookup operation, means fetching the rest of the columns from clustered index, if you create a covered index in that table you will notice the difference.

Try the following query, this time you will find second is better; this query avoids lookup

select ADate
from dbo.Data
where DATEADD(day, 1, ADate) > GETDATE()
go


select ADate
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
0
jogosCommented:
What we learn from the plan
ADATE is in an index -> don't use DATEADD on it in your WHERE while it will prevent that index being used (in second you see index used -> 1% off that statement)

SELECT *  -> key loockup
If you can limit the columns you select and those columns you add as 'included columns' to the index on ADATE then that costly (99%) key loockup won't be necessarry either.
0
jogosCommented:
<<jogos, we can see that both queries are performing "logical reads" meaning the data is in memory even when performing the first query.>>
Indeed in this case the less reads is caused by using the index, prooves that using the index is more efficient when it would be the slower physical reads.

... and both our second comments are saying the same ... in other words
0
enrique_aeoAuthor Commented:
this is the code
create table dbo.Data
(
      ID int not null identity(1,1),
      Placeholder char(100) not null
            constraint DEF_Data_Placeholder
            default 'This is placeholder',
      ADate datetime not null
      
      constraint PK_Data
      primary key clustered(ID)      
)
go

create nonclustered index IDX_Data_ADate
on dbo.Data(ADate)
go
0
Éric MoreauSenior .Net ConsultantCommented:
my comments are still valid. because you have an index on the ADate column, you better use a raw value to use the index in your query
0
enrique_aeoAuthor Commented:
I understand  this query
select *
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
go

is best based on the results of the statistics, but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%

do not understand why the second query is more expensive? when you have less logical reads.

For this case led me to the results of the statistics or the execution plan?
0
jogosCommented:
See here for info about  covered indexes
http://msdn.microsoft.com/en-us/library/ms190806.aspx

Pay attention to the peformance-loss when you do much updates on the column Placeholder, because also the index must be updated then.
0
enrique_aeoAuthor Commented:
Sorry experts
but I do not understand the difference in % in the execution plan?
what's the mean query cost 34% and 66%
0
jogosCommented:
Query cost means  how long that query will take relative to total batch time (most interpretation) but it's a little more complicate than just comparing duration time.

Other example that shows conflicting indicators (but more measurements )
http://stackoverflow.com/questions/564717/measuring-query-performance-execution-plan-query-cost-vs-time-taken

Is it the estimated or actual execution plan?


For your query I repeat query 2 is very fast in finding the records in the index, but 99% of that query cost is there the key lookup. So if you can manage to make the index a covered index then you can have a huge gain.

A link that have a more typed out explenation (especialy under tooltip immage)
http://sqlserverpedia.com/wiki/Examining_Query_Execution_Plans
0
enrique_aeoAuthor Commented:
it is actual execution plan
0
jogosCommented:
Covered index?
0
enrique_aeoAuthor Commented:
I understand that covered index solves the problem. But my question was for the percentage batch, I now understand that there are more things you should check and improve on the results, I analyze all the information provided by you to close the question, thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.