Post reply

CTE Vs Temp table - Index Scan and Seek

  • March 16, 2011 at 1:40 am

    #236410

    I have table tbl_US with uptshipmentid as primary key column and also a clustered index on it. The table has more than 45 lakhs of rows

    Now, the following query uses index scan and it takes 30s and matching is only 6 rows

    ;with shipments (ShipmentID) as (

    select dbo.udf_getShipmentID(uptreferenceid, uptreferencetype) from tbl_UA where

    UPTINVOICEID = 36999 AND CHARGECODE = 'DAR' AND CHARGEAMT = 0

    )

    select * from tbl_US where UPTShipmentID in (select ShipmentID from shipments)

    Same query, with temp table

    declare @shipments table ( shipmentid bigint)

    insert into @shipments

    select dbo.udf_getShipmentID(uptreferenceid, uptreferencetype) from tbl_UA where

    UPTINVOICEID = 36999 AND CHARGECODE = 'DAR' AND CHARGEAMT = 0

    select * from tbl_US where UPTShipmentID in (select ShipmentID from @shipments)

    returns the same 6 rows within a second and uses index seek?

    What would be the problem with first one.

    Thanks for ur time

    regards

    KRS

  • March 16, 2011 at 1:58 am

    #1299243

    Sometimes it's more efficient to break a query up and use temporary storage, like a temp table or, as you did, a table variable.

    CTEs, despite their name, are not tables, they're just named subqueries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply