July 8, 2018 at 12:58 am
Jackie Lowery - Saturday, July 7, 2018 12:09 PMEirikur Eiriksson - Saturday, July 7, 2018 3:23 AMI would try to dump the sub-query into a temporary table with a conversion to XML for the DATA column and then execute the XQuery against it.
😎Ping back if you need any further help on this.
That was the answer i needed. I put the XML into a table variable and the query that returned 119 rows now only takes 125ms. I can pull a year of records for a customer in 5 sec.
Thanks so much man. Great work!
You are very welcome.
😎
Just a thought, if the initial XML result set is large, you will be better off using a temporary table rather than a table variable.
Another though is that you could add a persisted calculated column, here is an example
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_TEST_XML_CONVERT') IS NOT NULL DROP TABLE dbo.TBL_TEST_XML_CONVERT;
CREATE TABLE dbo.TBL_TEST_XML_CONVERT
(
TXC_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_XML_CONVERT_TXC_ID PRIMARY KEY CLUSTERED
,TXC_NTEXT NTEXT NOT NULL
,TXC_XML AS (CONVERT(XML,TXC_NTEXT,0)) PERSISTED
);
DECLARE @TXML NVARCHAR(MAX) =
N'<RESULTS>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[John]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[DK]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[Jane]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[SK]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="FIRSTNAME"><![CDATA[Joan]]></COLUMN>
<COLUMN NAME="LASTNAME"><![CDATA[Doe]]></COLUMN>
<COLUMN NAME="COUNTRY"><![CDATA[UK]]></COLUMN>
</ROW>
</RESULTS>';
INSERT INTO dbo.TBL_TEST_XML_CONVERT(TXC_NTEXT)
VALUES
(CONVERT(NTEXT,@TXML,0))
,(CONVERT(NTEXT,@TXML,0))
,(CONVERT(NTEXT,@TXML,0))
,(CONVERT(NTEXT,@TXML,0))
,(CONVERT(NTEXT,@TXML,0))
,(CONVERT(NTEXT,@TXML,0))
;
SELECT
TXC.TXC_ID
,TXC.TXC_NTEXT
,TXC.TXC_XML
FROM dbo.TBL_TEST_XML_CONVERT TXC;
July 8, 2018 at 4:23 pm
July 9, 2018 at 4:59 am
Jackie Lowery - Sunday, July 8, 2018 4:23 PMIs it possible to use a persisted calculated column when there are errors in some of the XML?
I can't use try_convert b/c the db compat level is server 2005. It's an old app, so not sure i can change that.
What is the actual version you are running on?
😎
You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.
July 9, 2018 at 5:04 am
Eirikur Eiriksson - Monday, July 9, 2018 4:59 AMWhat is the actual version you are running on?
😎You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.
11.0.7001.0
July 9, 2018 at 5:41 am
Jackie Lowery - Monday, July 9, 2018 5:04 AMEirikur Eiriksson - Monday, July 9, 2018 4:59 AMWhat is the actual version you are running on?
😎You can use features which are newer than the version set in the compatibility level, that is for backwards compatibility only and does not affect new features.
11.0.7001.0
That is 2012 SP4, you should have no problems implementing the calculate column on this version.
😎
July 9, 2018 at 9:26 am
I get this error:
Msg 195, Level 15, State 10, Line 1
'try_convert' is not a recognized built-in function name.
July 9, 2018 at 9:36 am
I changed the db compat level on our test server db to SQL 2012. The try_convert function works on it now. What things should i be concerned with when changing that in production?
July 10, 2018 at 6:28 am
I made the change to db compat level and created the XML computed column. Now the query takes 12 sec. to return 119 rows for a single CDA. I'm at a loss. Here is code and exec plan. Why would computed column be so much slower than the table variable? I need the computed column to work b/c of the WMS system i have to use the query in.
;with ctCdaDocTypes /* Get Doc Types (i.e. 812) for CDA to filter Misc table by DocType */
as
(
select Doc_ID
from EDIStdDocs
where DGID = 'CDA'
group by Doc_ID
)
, ctTP_PartIDs /* Get Partner IDs to filter the Misc table by TP_PartID */
as
(
SELECT TP_PartID, ShipTo_CustID Cus_No FROM edi_01..ShipTo UNION SELECT TP_PartID, TP_ID Cus_No FROM edi_01..Trade
)
select x.Misc_ID
, DateRecv
, [CD Flag] = BCD.DATA.value('(BCD05/@_)[1]','char(1)')
, [Doc Line] = _CDD.DATA.value('@docLine', 'int')
, [Cus Item Number] = LIN3.DATA.value('@_','char(30)')
, [Line Amt] = CDD4.DATA.value('@_','varchar(30)')
, [Reason Cd] = CDD1.DATA.value('@_','char(3)')
from (
select misc_id, DateRecv ,DocType, TP_PartID
from Misc
where Seg_ID = 1
AND DocType IN (select Doc_ID from ctCdaDocTypes) /* CDA doc type is usually 812 */
) m
join ctTP_PartIDs t on m.TP_PartID = t.TP_PartID
join XMLDoc x on x.Misc_ID = m.Misc_ID
cross apply
x.docXML.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_HEADER/BCD') BCD(DATA)
cross apply (
x.docXML.nodes('/_DOC/_INTERCHANGE/_GROUP/_TRANSACTION/_DETAIL/_CDD') _CDD(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD01') CDD1(DATA)
OUTER APPLY _CDD.DATA.nodes('CDD/CDD04') CDD4(DATA)
OUTER APPLY _CDD.DATA.nodes('LIN/LIN03') LIN3(DATA)
)
WHERE m.DateRecv >= '01/01/2018' AND m.DateRecv <= GETDATE()
AND m.Misc_ID = 249331 AND t.Cus_No IN ('WALM01')
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply