June 13, 2016 at 9:31 am
Let me start at the beginning. I need to produce a result set that allows for bi-directional recursion, in other words the result set in total is based upon a recursive query and a column in the result set also is built by the recursion. Ultimately, this SQL will become a data source in SSIS, but this is not a SSIS question. All of this is due to converting as single PL SQL statement that uses sys_connect_by_path, which I am very happy to do.
I have the SQL using the CTE that functions as I expect. I also have SQL that uses a temporary table using SCOPE_IDENETITY in a WHILE loop that also functions as I expect. Both return the correct data set. The next piece of the puzzle is that resulting data set is approximately 1.3 million rows. At that volume, the CTE did not return even after two hours of executing. The While loop solution returns the 1.3 million rows in about one minute.
Now the problem comes when I attempt to encapsulate the While loop solution into a single SQL Command. I tried a TVF, but I cannot create a temporary table or turn off IDENTITY_INSERT for a table variable to use SCOPE_IDENTITY. As a Stored Procedure, the data returned after 2:30 minutes, however SSIS cannot read column metadata from a stored procedure.
I am open to suggestions on what to try next.
Thank you.
June 13, 2016 at 9:39 am
...however SSIS cannot read column metadata from a stored procedure.
You can get around that. You need to specify WITH RESULT SETS in your OLEDB source query and all will be well. Post back if you need more help with that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2016 at 10:08 am
Phil,
I had not known about WITH RESULTS SETS. Thank you.
As luck would have it, the next road block popped up.
When I execute the Procedure without the added WITH clause, I get my full result set.
However, when I add the RESULT SETS clause I get the following error. As an interesting observation, nine rows corresponds the number of root elements in the result set.
The final statement in my procedure is SELECT * FROM #RESULTS, with the column list rather than *.
(9 row(s) affected)
Msg 11537, Level 16, State 1, Procedure GetLoadMaster, Line 76
EXECUTE statement failed because its WITH RESULT SETS clause specified 29 column(s) for result set number 1, but the statement sent 1 column(s) at run time.
The statement has been terminated.
Nothing is ever simple, even it was it would not be as interesting.
June 13, 2016 at 10:57 am
Does your proc start with
SET NOCOUNT ON;
? If not, please add it and try again.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2016 at 11:25 am
Great catch and it does now. I had forgot to add that in when I switched from function to procedure. However the same error message was generated. The big changes is that the nine row result set disappeared and only the error was returned. I even dropped and recreated the proc to flush the plan from memory and then executed with recompile just to make sure. The story Continues…
Msg 11537, Level 16, State 1, Procedure GetLoadMaster, Line 77
EXECUTE statement failed because its WITH RESULT SETS clause specified 29 column(s) for result set number 1, but the statement sent 1 column(s) at run time.
The statement has been terminated.
June 13, 2016 at 11:42 am
Can you post the proc?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2016 at 11:48 am
CREATE PROCEDURE [MRP].[GetLoadMaster]
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #RESULTS (
[MODEL_SN] [varchar](255) NULL,
[QTY] [numeric](12, 3) NULL,
[PARENT_ORDER_ID] [varchar](255) NULL,
[PARENT_PART_ID] [varchar](255) NULL,
[ORDER_ID] [varchar](255) NULL,
[LINE_NUMBER] [int] NULL,
[PART_ID] [varchar](255) NULL,
[PART_TYPE] [varchar](255) NULL,
[LOG] [varchar](255) NULL,
[PHASE] [varchar](3) NULL,
[LOG_JOIN] [varchar](255) NULL,
[REQ_DATE] [date] NULL,
[PART_DESCRIPTION] [varchar](255) NULL,
[CLASS_CODE] [varchar](255) NULL,
[SHORTAGE_DATE] [date] NULL,
[ORDER_CREAT_DATE] [date] NULL,
[PARENT_PATH] [varchar](767) NULL,
[PATH] [varchar](767) NULL,
[PRIORITY_INDEX_PHASE] [int] NULL,
[PO_BUYER_ID] [varchar](255) NULL,
[ITEM_BUYER_ID] [varchar](255) NULL,
[WORK_ORDER_NUMBER] [varchar](6) NULL,
[DUE_DATE] [date] NULL,
[PROMISE_DATE] [date] NULL,
[IMS_DATE] [date] NULL,
[EST_COMPL_DATE] [date] NULL,
[IN_INSPECTION] [varchar](1) NULL
,[LEVEL] INT IDENTITY(1,1) NOT NULL
,[FULLPATH] VARCHAR(4000) NULL
);
CREATE INDEX #IXS ON #RESULTS(LEVEL) INCLUDE([PATH]);
SET IDENTITY_INSERT #RESULTS ON;
INSERT #RESULTS
(
[MODEL_SN],
[QTY],
[PARENT_ORDER_ID],
[PARENT_PART_ID],
[ORDER_ID],
[LINE_NUMBER],
[PART_ID],
[PART_TYPE],
[LOG],
[PHASE],
[LOG_JOIN],
[REQ_DATE],
[PART_DESCRIPTION],
[CLASS_CODE],
[SHORTAGE_DATE],
[ORDER_CREAT_DATE],
[PARENT_PATH],
[PATH],
[PRIORITY_INDEX_PHASE],
[PO_BUYER_ID],
[ITEM_BUYER_ID],
[WORK_ORDER_NUMBER],
[DUE_DATE],
[PROMISE_DATE],
[IMS_DATE],
[EST_COMPL_DATE],
[IN_INSPECTION]
,[LEVEL]
,[FULLPATH]
)
SELECT
[MODEL_SN],
[QTY],
[PARENT_ORDER_ID],
[PARENT_PART_ID],
[ORDER_ID],
[LINE_NUMBER],
[PART_ID],
[PART_TYPE],
[LOG],
[PHASE],
[LOG_JOIN],
[REQ_DATE],
[PART_DESCRIPTION],
[CLASS_CODE],
[SHORTAGE_DATE],
[ORDER_CREAT_DATE],
[PARENT_PATH],
[PATH],
[PRIORITY_INDEX_PHASE],
[PO_BUYER_ID],
[ITEM_BUYER_ID],
[WORK_ORDER_NUMBER],
[DUE_DATE],
[PROMISE_DATE],
[IMS_DATE],
[EST_COMPL_DATE],
[IN_INSPECTION]
,1
,CONVERT(varchar(767),[MRP].DECIMAL2BASE26(PRIORITY_INDEX_PHASE / 9)COLLATE SQL_Latin1_General_CP1_CS_AS + '^' + MODEL_SN + '^' + [LOG] + '^' + LTRIM(ORDER_ID + '[' + CAST(LINE_NUMBER AS varchar(50)) + ']') )AS [FULLPATH]
FROM MRP.V_MASTER_TEMP
WHERE PARENT_PATH IS NULL;
WHILE @@ROWCOUNT>0
INSERT #RESULTS(
[MODEL_SN],
[QTY],
[PARENT_ORDER_ID],
[PARENT_PART_ID],
[ORDER_ID],
[LINE_NUMBER],
[PART_ID],
[PART_TYPE],
[LOG],
[PHASE],
[LOG_JOIN],
[REQ_DATE],
[PART_DESCRIPTION],
[CLASS_CODE],
[SHORTAGE_DATE],
[ORDER_CREAT_DATE],
[PARENT_PATH],
[PATH],
[PRIORITY_INDEX_PHASE],
[PO_BUYER_ID],
[ITEM_BUYER_ID],
[WORK_ORDER_NUMBER],
[DUE_DATE],
[PROMISE_DATE],
[IMS_DATE],
[EST_COMPL_DATE],
[IN_INSPECTION]
,[LEVEL]
,[FULLPATH]
)
SELECT
S.[MODEL_SN],
S.[QTY],
S.[PARENT_ORDER_ID],
S.[PARENT_PART_ID],
S.[ORDER_ID],
S.[LINE_NUMBER],
S.[PART_ID],
S.[PART_TYPE],
S.[LOG],
S.[PHASE],
S.[LOG_JOIN],
S.[REQ_DATE],
S.[PART_DESCRIPTION],
S.[CLASS_CODE],
S.[SHORTAGE_DATE],
S.[ORDER_CREAT_DATE],
S.[PARENT_PATH],
S.[PATH],
S.[PRIORITY_INDEX_PHASE],
S.[PO_BUYER_ID],
S.[ITEM_BUYER_ID],
S.[WORK_ORDER_NUMBER],
S.[DUE_DATE],
S.[PROMISE_DATE],
S.[IMS_DATE],
S.[EST_COMPL_DATE],
S.[IN_INSPECTION]
,SCOPE_IDENTITY() + 1
,CONVERT(varchar(767), R.FULLPATH + MRP.DECIMAL2BASE26(S.PRIORITY_INDEX_PHASE / 9) COLLATE SQL_Latin1_General_CP1_CS_AS + '^' + S.MODEL_SN + '^' + S.[LOG] + '^' + LTRIM(S.ORDER_ID + '[' + CAST(S.LINE_NUMBER AS varchar(50)) + ']') ) AS [FULLPATH]
FROM #RESULTS AS R
JOIN MRP.V_MASTER_TEMP AS S
ON S.PARENT_PATH = R.[PATH] COLLATE SQL_Latin1_General_CP1_CS_AS
WHERE [LEVEL] = SCOPE_IDENTITY();
SET IDENTITY_INSERT #RESULTS OFF;
SELECT
[MODEL_SN],
[QTY],
[PARENT_ORDER_ID],
[PARENT_PART_ID],
[ORDER_ID],
[LINE_NUMBER],
[PART_ID],
[PART_TYPE],
[LOG],
[PHASE],
[LOG_JOIN],
[REQ_DATE],
[PART_DESCRIPTION],
[CLASS_CODE],
[SHORTAGE_DATE],
[ORDER_CREAT_DATE],
[PARENT_PATH],
[PATH],
[PRIORITY_INDEX_PHASE],
[PO_BUYER_ID],
[ITEM_BUYER_ID],
[WORK_ORDER_NUMBER],
[DUE_DATE],
[PROMISE_DATE],
[IMS_DATE],
[EST_COMPL_DATE],
[IN_INSPECTION]
,[LEVEL]
,[FULLPATH]
FROM #RESULTS
ORDER BY PARENT_PATH, [PATH]
RETURN
END
June 13, 2016 at 12:38 pm
Here is the call:
EXEC [MRP].[GetLoadMaster] --WITH RECOMPILE
WITH RESULT SETS
(
(
[MODEL_SN] [varchar](255) NULL,
[QTY] [numeric](12, 3) NULL,
[PARENT_ORDER_ID] [varchar](255) NULL,
[PARENT_PART_ID] [varchar](255) NULL,
[ORDER_ID] [varchar](255) NULL,
[LINE_NUMBER] [int] NULL,
[PART_ID] [varchar](255) NULL,
[PART_TYPE] [varchar](255) NULL,
[LOG] [varchar](255) NULL,
[PHASE] [varchar](3) NULL,
[LOG_JOIN] [varchar](255) NULL,
[REQ_DATE] [date] NULL,
[PART_DESCRIPTION] [varchar](255) NULL,
[CLASS_CODE] [varchar](255) NULL,
[SHORTAGE_DATE] [date] NULL,
[ORDER_CREAT_DATE] [date] NULL,
[PARENT_PATH] [varchar](767) NULL,
[PATH] [varchar](767) NULL,
[PRIORITY_INDEX_PHASE] [int] NULL,
[PO_BUYER_ID] [varchar](255) NULL,
[ITEM_BUYER_ID] [varchar](255) NULL,
[WORK_ORDER_NUMBER] [varchar](6) NULL,
[DUE_DATE] [date] NULL,
[PROMISE_DATE] [date] NULL,
[IMS_DATE] [date] NULL,
[EST_COMPL_DATE] [date] NULL,
[IN_INSPECTION] [varchar](1) NULL
,[LEVEL] INT NOT NULL
,[FULLPATH] VARCHAR(4000) NULL
)
)
June 13, 2016 at 1:20 pm
Wow. Not sure about the error, but I have some quick comments about the proc:
1) If you create the index after the first insert rather than before, it is likely to run a bit faster.
2) If you don't need the output to be sorted, remove the final order by.
3) The RETURN at the end is probably superfluous.
4) I'd add TRY/CATCH blocks.
5) Presumably you've tried to avoid the WHILE loop ... it will kill performance.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2016 at 2:10 pm
Phil,
I totally agree with points 3 & 4. With points 1 & 5, they are crucial to handle the recursion efficiently as coded. As for point 2, since this will be a SSIS Source, I will need the data sorted for downstream processing.
… AND now for the solution to the error message that I just stumbled upon. I was executing the stored procedure from SSMS and I had the Include Actual Execution Plan option selected for the query. As soon as I stopped requesting the Execution Plan, the error went away. I then tested execute with result sets from the OLE DB Source and it worked there too.
I very much appreciate your helping me work through this issue.
Michael
June 13, 2016 at 2:22 pm
michael.french 172 (6/13/2016)
Phil,I totally agree with points 3 & 4. With points 1 & 5, they are crucial to handle the recursion efficiently as coded. As for point 2, since this will be a SSIS Source, I will need the data sorted for downstream processing.
… AND now for the solution to the error message that I just stumbled upon. I was executing the stored procedure from SSMS and I had the Include Actual Execution Plan option selected for the query. As soon as I stopped requesting the Execution Plan, the error went away. I then tested execute with result sets from the OLE DB Source and it worked there too.
I very much appreciate your helping me work through this issue.
Michael
Glad I could help & well done for making it all work.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 13, 2016 at 4:40 pm
michael.french 172 (6/13/2016)
Phil,I totally agree with points 3 & 4. With points 1 & 5, they are crucial to handle the recursion efficiently as coded. As for point 2, since this will be a SSIS Source, I will need the data sorted for downstream processing.
… AND now for the solution to the error message that I just stumbled upon. I was executing the stored procedure from SSMS and I had the Include Actual Execution Plan option selected for the query. As soon as I stopped requesting the Execution Plan, the error went away. I then tested execute with result sets from the OLE DB Source and it worked there too.
I very much appreciate your helping me work through this issue.
Michael
This would appear to be an "Adjacency List" (parent/child) hierarchy problem. With that, how often does the actual data in the source table change? Probably not often but you continue to calculate the return instead of just returning it.
If the "Adjacency List" has no cyclic members, then consider building "Nested Sets" from the "Adjacency List" for search and display purposes. Keep the "Adjacency List" active because it's super easy to maintain. The "Nested Sets" are super easy and nasty fast to query. Both can exist at the same time and it only takes a minute to rebuild all 1.3 million rows when the "Adjacency List" changes.
Here's the link. I believe you'll find it well worth the read.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2016 at 9:37 am
michael.french 172 (6/13/2016)
Phil,I totally agree with points 3 & 4. With points 1 & 5, they are crucial to handle the recursion efficiently as coded. As for point 2, since this will be a SSIS Source, I will need the data sorted for downstream processing.
… AND now for the solution to the error message that I just stumbled upon. I was executing the stored procedure from SSMS and I had the Include Actual Execution Plan option selected for the query. As soon as I stopped requesting the Execution Plan, the error went away. I then tested execute with result sets from the OLE DB Source and it worked there too.
I very much appreciate your helping me work through this issue.
Michael
Hi Michael
Hope you don't mind me asking a supplemental question.
I'd like to know more about your response to my first point.
What I think you are saying is that the following
1) Create temp table
2) Create index on temp table
3) Insert data to temp table
4) ... more processing
Is markedly worse in performance from
1) Create temp table
2) Insert data to temp table
3) Create index on temp table
4) ... more processing
Presumably because the 'more processing' bit depends on the index having been created before the insert, rather than after.
But this is strange for me, as I would not expect the order of index creation to particularly affect subsequent processing. Can you please explain why?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 14, 2016 at 10:08 am
Phil,
In normal processing, I completely agree with you on loading the table and then creating an index. However in this case, there is an n-level recursion inserting data from the while loop that is then supported by the index created first on the temporary table. By the time the temporary is fully populated, there would be no need to create the index.
Michael
June 14, 2016 at 10:20 am
michael.french 172 (6/14/2016)
Phil,In normal processing, I completely agree with you on loading the table and then creating an index. However in this case, there is an n-level recursion inserting data from the while loop that is then supported by the index created first on the temporary table. By the time the temporary is fully populated, there would be no need to create the index.
Michael
Thanks, but the first insert to #Results is
SELECT ,,,,,
FROM MRP.V_MASTER_TEMP
WHERE PARENT_PATH IS NULL;
I do not see any recursion until after this insert.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply