Cursor replacement help

  • Morning,

    Thank you for your help in advance, I have built a cursor to run through list and with the use of table functions build up a set of data. Whilst I am unable to send everything through I can at least try to give you an idea of what this looks like.

    CREATE TABLE [dbo].[#TSC_ExportedColumnIds](

    [ID] [int] NOT NULL,

    [ColumnType] [int] NOT NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (1, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (2, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (3, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (4, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (5, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (6, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (7, 3)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (8, 3)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (9, 3)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (10, 1)

    GO

    INSERT [dbo].[#TSC_ExportedColumnIds] ([ID], [ColumnType]) VALUES (11, 1)

    DECLARE @TEMP_EXPORT TABLE (

    [A] [varchar](500) NULL,

    [nvarchar](500) NULL,

    [C] [nvarchar](50) NULL,

    [D] [nvarchar](50) NULL,

    [E] [nvarchar](1000) NULL,

    [F] [varchar](1000) NULL,

    [G] [varchar](500) NULL

    )

    DECLARE @COLUMN_ID INT

    DECLARE @getID CURSOR

    SET @getID = CURSOR FOR

    SELECT ID

    FROM dbo.#TSC_ExportedColumnIds

    OPEN @getID

    FETCH NEXT

    FROM @getID INTO @COLUMN_ID

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @COLUMN_ID

    DECLARE @COLUMN_TYPE INT

    SELECT @COLUMN_TYPE = TSC_COLUMNS.ColumnType

    FROM TSC_COLUMNS

    WHERE TSC_COLUMNS.ID = @COLUMN_ID

    IF @COLUMN_TYPE = 1

    BEGIN

    INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL

    --INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID)

    --INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4)

    END

    IF @COLUMN_TYPE = 2

    BEGIN

    INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL

    --INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](@COLUMN_ID)

    --INSERT INTO @TEMP_EXPORT SELECT * FROM [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](@COLUMN_ID,4)

    END

    IF @COLUMN_TYPE = 3

    BEGIN

    INSERT INTO @TEMP_EXPORT SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL

    --INSERT INTO @TEMP_EXPORT SELECT A,B,C,D,E,F,G FROM [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4)

    END

    FETCH NEXT

    FROM @getID INTO @COLUMN_ID

    END

    CLOSE @getID

    DEALLOCATE @getID

    select * from @TEMP_EXPORT

    I have commented out the functions but kept them in to give you an idea of what is being created.

    My question is, would it be possible to turn this into a set operation? At the moment on real data this query runs through a list of about 2500 rows and creates a table with around 100,000 rows in about a minute.

    Many thanks for your thoughts,

    Oliver

  • Without knowing a little more about what exactly your functions return, this is just a shot in the dark:

    INSERT INTO @TEMP_EXPORT

    SELECT *

    FROM [dbo].[#TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT *

    FROM [dbo].[#TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM [dbo].[#TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4) b

    WHERE a.ColumnType = 3

    You could try running it with INSERT commented out to see if it returns what you're expecting.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi Dwain,

    Thanks for the help, I made a few changes but the key is that I need to push the column_id variable to each function. How do I do this? With the cursor I set column_id is the cursor which it iterates through.

    Many Thanks for your help,

    Oliver

    SELECT @COLUMN_TYPE = TSC_COLUMNS.ColumnType

    FROM TSC_COLUMNS

    WHERE TSC_COLUMNS.ID = @COLUMN_ID

    INSERT INTO @TEMP_EXPORT

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](@COLUMN_ID) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](@COLUMN_ID,4) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](@COLUMN_ID) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](@COLUMN_ID,4) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](@COLUMN_ID,4) b

    WHERE a.ColumnType = 3

  • oliver.morris (3/7/2013)


    Hi Dwain,

    Thanks for the help, I made a few changes but the key is that I need to push the column_id variable to each function. How do I do this? With the cursor I set column_id is the cursor which it iterates through.

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b

    WHERE a.ColumnType = 3;

    Sorry, I never really read the whole thread, so pretty sure that my post above is incorrect. Instead, give this a shot: -

    SELECT A,B,C,D,E,F,G

    FROM (SELECT A.ID, B.ColumnType

    FROM dbo.TSC_ExportedColumnIds A

    INNER JOIN TSC_COLUMNS B ON A.ID = B.ID

    ) a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM (SELECT A.ID, B.ColumnType

    FROM dbo.TSC_ExportedColumnIds A

    INNER JOIN TSC_COLUMNS B ON A.ID = B.ID

    ) a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b

    WHERE a.ColumnType IN (1,2)

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM (SELECT A.ID, B.ColumnType

    FROM dbo.TSC_ExportedColumnIds A

    INNER JOIN TSC_COLUMNS B ON A.ID = B.ID

    ) a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam0](a.ID,4) b

    WHERE a.ColumnType = 3;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thanks for the help this makes sense. The query works considerably faster. However I have one issue. Each part needs to run in order e.g.

    For the 1st ID - if it was type 1 for example it needs to run

    udf_TSC_qry_ExportBlockColumnParam1

    and then

    udf_TSC_qry_ExportBlockColumnParam2

    So that in the output it is organised like:

    OutputParam1 (ID 1)

    OutputParam2 (ID 1)

    OutputParam1 (ID 2)

    OutputParam2 (ID 2)

    and not

    OutputParam1 (ID 1)

    OutputParam1 (ID 2)

    OutputParam2 (ID 1)

    OutputParam2 (ID 2)

    I hope this makes sense. Many thanks for your help, its almost there and runs in 1 second compared to 1 minute!

    Oliver

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b

    WHERE a.ColumnType = 3;

  • Hi,

    Worked it out, added a new field for ordering:

    SELECT 1 as OrderME,*

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT 2,*

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT 3,*

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT 4,*

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT 5, A,B,C,D,E,F,G

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b

    WHERE a.ColumnType = 3

    order by ID,OrderMe

    This works great, thank you so much for your help. I wish I could understand how to work up cross apply more. I will sit in a quiet room and work it out.

    Many Thanks,

    Oliver

  • oliver.morris (3/7/2013)


    I wish I could understand how to work up cross apply more. I will sit in a quiet room and work it out.

    Many Thanks,

    Oliver

    Check out Paul White's articles.

    http://www.sqlservercentral.com/articles/APPLY/69953/[/url]

    http://www.sqlservercentral.com/articles/APPLY/69954/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks Sean, will do

  • oliver.morris (3/7/2013)


    Thanks for the help this makes sense. The query works considerably faster. However I have one issue. Each part needs to run in order e.g.

    For the 1st ID - if it was type 1 for example it needs to run

    udf_TSC_qry_ExportBlockColumnParam1

    and then

    udf_TSC_qry_ExportBlockColumnParam2

    So that in the output it is organised like:

    OutputParam1 (ID 1)

    OutputParam2 (ID 1)

    OutputParam1 (ID 2)

    OutputParam2 (ID 2)

    and not

    OutputParam1 (ID 1)

    OutputParam1 (ID 2)

    OutputParam2 (ID 1)

    OutputParam2 (ID 2)

    I hope this makes sense. Many thanks for your help, its almost there and runs in 1 second compared to 1 minute!

    Oliver

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam1](a.ID) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportBlockColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 1

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam1](a.ID) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT *

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportSequenceColumnParam2](a.ID,4) b

    WHERE a.ColumnType = 2

    UNION ALL

    SELECT A,B,C,D,E,F,G

    FROM [dbo].[TSC_ExportedColumnIds] a

    CROSS APPLY [dbo].[udf_TSC_qry_ExportEventColumnParam0](a.ID,4) b

    WHERE a.ColumnType = 3;

    Looks like I was pretty close. Glad you worked it out.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Many thanks, reduced times from 1min 7 secs to 4 seconds.

    Really appreciate your help,

    Oliver

Viewing 10 posts - 1 through 9 (of 9 total)

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