March 7, 2013 at 4:01 am
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
March 7, 2013 at 5:34 am
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 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
March 7, 2013 at 5:48 am
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
March 7, 2013 at 6:05 am
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;
March 7, 2013 at 6:14 am
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;
March 7, 2013 at 6:25 am
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
March 7, 2013 at 7:39 am
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/
March 7, 2013 at 7:43 am
Thanks Sean, will do
March 7, 2013 at 7:48 am
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 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
March 7, 2013 at 7:55 am
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