set statics IO - Which query is more efficient?

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
enrique_aeoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dwkorConnect With a Mentor Commented:
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
 
LowfatspreadCommented:
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
 
enrique_aeoAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
enrique_aeoAuthor Commented:
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
 
enrique_aeoAuthor Commented:
and the execution plan PLEASE
queryONE.jpg
queryTWO.jpg
0
 
LowfatspreadConnect With a Mentor Commented:
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
 
enrique_aeoAuthor Commented:
anything else dwkor?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.