Solved

set statics IO - Which query is more efficient?

Posted on 2011-02-12
7
323 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now