Solved

set statics IO - Which query is more efficient?

Posted on 2011-02-12
7
325 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error Creating Foreign Keys in SQL Database 7 34
Generate Weekly Schedule 15 28
Extract string portion 2 24
Please help for the below sql query. 1 24
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

785 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