Hi, I read that writing query with filtering criteria fully qualified greatly reduces the I/O operation. So I'm changing the WHERE clause which are not fully qualified filtering, but when I examine the execution plans I do not see differences between the query with filters fully qualified and not. For Example:IF (OBJECT_ID('OrderDetail', 'U') IS NOT NULL) DROP TABLE dbrderDetail GO-- Create table OrderDetailCREATE TABLE dbrderDetail( OrderDetailID INT IDENTITY(1, 1) NOT NULL, OrderHeaderID INT NOT NULL, ProductID INT NOT NULL, RigNumber AS (OrderDetailID * 2) , UnitPrice MONEY DEFAULT 0 NOT NULL PRIMARY KEY(OrderDetailID))-- Drop table OrderIF (OBJECT_ID('OrderHeader', 'U') IS NOT NULL)DROP TABLE dbrderHeader GO-- Create table OrderHeaderCREATE TABLE dbrderHeader( OrderID INT IDENTITY(1, 1) NOT NULL,OrderDATE DATETIME DEFAULT GETDATE() NOT NULL,OrderNUMBER AS (ISNULL(N'SO' + CONVERT([nvarchar](23), [OrderID], 0), N'*** ERROR ***')),CustomerID INT DEFAULT 1 NOT NULL,ShipName VARCHAR (20) DEFAULT 'Name',ShipAddress VARCHAR (40) DEFAULT 'Address',ShipVia VARCHAR (40) DEFAULT 'Via',ShipCity VARCHAR (20) DEFAULT 'City',ShipRegion VARCHAR (20) DEFAULT 'Region',ShipPostalCode VARCHAR (20) DEFAULT 'Postal code',ShipCountry VARCHAR (20) DEFAULT 'Country'PRIMARY KEY(OrderID) )-- ALTER TABLE ADD CONSTRAINTALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderHeaderID] FOREIGN KEY ([OrderHeaderID]) REFERENCES [dbo].[OrderHeader] ([OrderID])DECLARE @i AS INT SET @i = 0WHILE (@i < 20000)BEGIN SET NOCOUNT ON INSERT INTO dbrderHeader DEFAULT VALUES -- Row 1 INSERT INTO dbrderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM dbrderHeader), (@i * 2)+1, ((@i * 2)+1)/2 -- Row 2 INSERT INTO dbrderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM dbrderHeader), (@i * 3)+1, ((@i * 3)+1)/3 -- Row 3 INSERT INTO dbrderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM dbrderHeader), (@i * 4)+1, ((@i * 4)+1)/4 SET @i = (@i + 1) SET NOCOUNT ONEND The following two queries are executed with the same number I/O operations: 1) select * from dbrderHeader OH join dbrderDetail OD ON OD.OrderHeaderID = OH.OrderID where OH.OrderID >= 1100 2) select * from dbrderHeader OH join dbrderDetail OD ON OD.OrderHeaderID = OH.OrderID where OH.OrderID >= 1100 and OD.OrderHeaderID >= 1100 Table 'OrderDetail'. Scan count 1, logical reads 217, physical reads 0, read-ahead reads 0. Table 'OrderHeader'. Scan count 1, logical reads 199, physical reads 0, read-ahead reads 0. SQL Server Execution Times: CPU time = 204 ms, elapsed time = 3593 ms. I'm wrong to expect a lower I/O from the second query (fully qualified) ? I tried both with SQL Server 2000 and SQL Server 2005. Thanks in advance. Sergio
Fully qualified name or filter will not reduce I/O but it may reduce recompilation cost... http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm