Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cursor replacement help Expand / Collapse
Author
Message
Posted Thursday, March 07, 2013 4:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
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
Post #1427886
Posted Thursday, March 07, 2013 5:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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!
Post #1427908
Posted Thursday, March 07, 2013 5:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
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

Post #1427918
Posted Thursday, March 07, 2013 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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;




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1427929
Posted Thursday, March 07, 2013 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
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;

Post #1427931
Posted Thursday, March 07, 2013 6:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
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
Post #1427937
Posted Thursday, March 07, 2013 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's 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)
Post #1427978
Posted Thursday, March 07, 2013 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
Thanks Sean, will do
Post #1427981
Posted Thursday, March 07, 2013 7:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
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!
Post #1427987
Posted Thursday, March 07, 2013 7:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 6:16 AM
Points: 151, Visits: 282
Many thanks, reduced times from 1min 7 secs to 4 seconds.

Really appreciate your help,

Oliver
Post #1427993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse