SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor replacement help


Cursor replacement help

Author
Message
olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
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,
[B] [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
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17723 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
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


Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9000 Visits: 8490
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
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;


olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62309 Visits: 17954
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/
http://www.sqlservercentral.com/articles/APPLY/69954/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
Thanks Sean, will do
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17723 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
olibbhq
olibbhq
SSC Eights!
SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)SSC Eights! (894 reputation)

Group: General Forum Members
Points: 894 Visits: 364
Many thanks, reduced times from 1min 7 secs to 4 seconds.

Really appreciate your help,

Oliver
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search