Solved

set statics IO - Which query is more efficient?

Posted on 2011-02-12
7
326 Views
Last Modified: 2012-05-11
I have two query
query 1
select  Production.obtenerNombreProducto(p.ProductID) as 'Nombre Producto',
            p.ProductID, p.Quantity
from Production.ProductInventory p

set static io
(1069 row(s) affected)
Table 'ProductInventory'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

query two
select p.Name, p.ProductID,pi.Quantity
from Production.Product p inner join Production.ProductInventory pi
     on p.ProductID = pi.ProductID

set static io
(1069 row(s) affected)
Table 'ProductInventory'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I attached the execution plan.
What information should I consider in order to know what the query more efficient?

queryONE.jpg
queryTWO.jpg
0
Comment
Question by:enrique_aeo
  • 4
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34879093
they are doing different things so they cannot be compared....

they are both efficient.

if you don't need the product name  then query 1 may be the efficient way of obtaining the quantity of
product... We would need to actually see the code in the function....

if you want the name and quantity then query 2 is the most efficient out of the 2 queries shown....

what are you really trying to do....
do you have a real problem with the queries...

efficientcy comes in many different packages... if it AINT BROKE DON'T FIX
0
 

Author Comment

by:enrique_aeo
ID: 34879103
a database manager told me that better to use a join to scalar functions (because running row by row), I want to do is prove it. IF this example I can not, you have other where you can do
0
 
LVL 13

Accepted Solution

by:
dwkor earned 250 total points
ID: 34879497
This is extremely bad idea to have scalar functions on the predicates (in both - joins and where clause). It basically kills the possible index usage.

Check this script and IO usage
use tempdb
go

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

;with CTE(Num)
as
(
	select 1
	union all 
	select Num + 1 
	from CTE
	where Num <= 100000
)
insert into dbo.Data(ADate)
	select DATEADD(day,-Num % 365, GetDate())
	from CTE
option (MAXRECURSION 0)
go

-- Getting all record for the last week
-- enable display execution plans
set statistics io on
go

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

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

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:enrique_aeo
ID: 34880260
can you help me
select *
from dbo.Data
where DATEADD(day, 1, ADate) > GETDATE()
go

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

select *
from dbo.Data
where ADate > DATEADD(day, -1, GetDate())
/*
(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.
*/

can help you interpret the results?
0
 

Author Comment

by:enrique_aeo
ID: 34880283
and the execution plan PLEASE
queryONE.jpg
queryTWO.jpg
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 250 total points
ID: 34880310
when you do the dateadd() function on Getdate()

the dbms knows it is dealing with a constant and so can evaluate the expression 1 at the start of the
query... it can then use the fixed value to start an "index" SCAN  OF THE TABLE from a point within the data...

when you do the dateadd on the table column the dbms doesn't has any fixed point reference which it can
calculate and so has to do a full scan of the table/index resulting in increased IO....

that is why the first took 1141 reads and the second 847.....

you need to read up on sargability to understand some of the implications of this...

there is an EE article covering some of the topic in the SQL Server Database area...

....


However , whilst as an experienced IT professional you learn various tricks of thr trade and the standard
methods for ensuring "efficient" access to your data... what counts in the final instance is how the DBMS
actually decides to process the request.... this is an area that is subject to change from a large variety of sources... statistics, access paths, concurrent database usage, disk access, improvements/changes in the underlying database engine, etc....

so it is advisable to check which access paths the dbms is chosing for your statements, and plan to mofify the environmental factors if appropiate... sql statement coding, index availability, currency of statistics, etc...

coming back to IF IT AIN'T BROKE DON'T FIX...
0
 

Author Comment

by:enrique_aeo
ID: 34891757
anything else dwkor?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question