SQL Server Performance

Fully qualified filtering

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sgovoni, Jun 10, 2008.

  1. sgovoni New Member

    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 db:confused:rderDetail
    GO-- Create table OrderDetailCREATE
    TABLE db:confused:rderDetail(
    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 db:confused:rderHeader
    GO-- Create table OrderHeaderCREATE
    TABLE db:confused:rderHeader(
    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 db:confused:rderHeader DEFAULT VALUES
    -- Row 1
    INSERT INTO db:confused:rderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM db:confused:rderHeader), (@i * 2)+1, ((@i * 2)+1)/2
    -- Row 2
    INSERT INTO db:confused:rderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM db:confused:rderHeader), (@i * 3)+1, ((@i * 3)+1)/3
    -- Row 3
    INSERT INTO db:confused:rderDetail (OrderHeaderID, ProductID, UnitPrice) SELECT (SELECT MAX(OrderID) FROM db:confused:rderHeader), (@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 db:confused:rderHeader OH join db:confused:rderDetail OD ON OD.OrderHeaderID = OH.OrderID where OH.OrderID >= 1100
    2) select * from db:confused:rderHeader OH join db:confused:rderDetail 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
  2. MohammedU New Member

Share This Page