SQL Server Performance

cursors vs cross apply

Discussion in 'SQL Server 2008 General Developer Questions' started by hayko98, Feb 10, 2011.

  1. hayko98 New Member

    Hi all,
    I use cursor to find the zone name for pickup addresses(which zone
    this Pickup address belogs to).I have map table(SA_union) which has
    ZONENAME(varchar) and GEOM (geometry)columns.Also i use TRIP table
    which has trip_id,PICKUP_ADDRESS_X and PICKUP_ADDRESS_Y column.By
    using cursor i retrive zonename for each trip_ID for 1 day
    period.Query runs 2 min For 2000 trip.My question is can i use CROSS
    APPLY (or ?)to make query run faster?.PICKUP_ADDRESS_X and
    PICKUP_ADDRESS_Y are stored as INT,that's why divided by 1000000.0.
    Here is my query:

    SET NOCOUNT on
    DECLARE @T AS TABLE
    (
    Trip_ID int,
    PU_X FLOAT(1) ,
    PU_Y FLOAT (1)
    );

    INSERT INTO @T
    SELECT TRIP_ID, PICKUP_ADDRESS_X / 1000000.0 ,
    PICKUP_ADDRESS_Y / 1000000.0
    FROM dbo.TRIP
    WHERE due_date BETWEEN '2010-10-10 00:00:00.951' AND '2010-10-10
    23:59:59.951'

    DECLARE T_cursor CURSOR
    FOR
    SELECT Trip_ID, PU_X, PU_Y
    FROM @T ;

    OPEN T_cursor;

    DECLARE @ID int,
    @X FLOAT(1),
    @Y FLOAT(1),
    @counter INT;
    SET @counter =0;

    FETCH NEXT FROM T_cursor INTO @ID, @X, @Y;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @counter = @counter+1;
    DECLARE @h geometry;
    SET @h = geometry::STGeomFromText('POINT('+str(@X,11,6)+' '+str(@Y,
    11,6)+')', 0);

    SELECT @ID AS TRIP_ID, ZONENAME, geom
    FROM dbo.SA_union
    WHERE geom.STContains(@h)=1
    FETCH NEXT FROM T_cursor INTO @ID, @X, @Y;

    END
    CLOSE T_cursor;
    DEALLOCATE T_cursor;
    Thank You
  2. satya Moderator

    Is it a compulsion that you must use CURSOR or can you redesign using CTE (common table expression) to obtain results quickly?
  3. hayko98 New Member

    Thank You for your respond.
    This is what i come up with(Thanks to Erland Sommarskog).Now it runs in 4 sec!!!SET NOCOUNT on
    DECLARE
    @T AS TABLE(
    Trip_ID int,
    PU_X FLOAT(1) ,
    PU_Y FLOAT (1)
    );INSERT
    INTO @T
    SELECT TRIP_ID, PICKUP_ADDRESS_X / 1000000.0 , PICKUP_ADDRESS_Y / 1000000.0FROM
    dbo.TRIPWHERE
    due_date BETWEEN '2010-10-10 00:00:00.951' AND '2010-10-10 23:59:59.951'
    SELECT Trip_ID AS TRIP_ID, ZONENAME, geom
    FROM dbo.SA_union SA
    JOIN @T T
    ON
    SA.geom.STContains(geometry::STGeomFromText('POINT('+str(T.PU_X,11,6) +' '+str(T.PU_Y,11,6)+')', 0))=1

Share This Page