enrique_aeo
asked on
set statics IO - Which query is more efficient?
I have two query
query 1
select Production.obtenerNombrePr
p.ProductID, p.Quantity
from Production.ProductInventor
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.ProductInventor
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
query 1
select Production.obtenerNombrePr
p.ProductID, p.Quantity
from Production.ProductInventor
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.ProductInventor
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
anything else dwkor?
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