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 12»»

XML Workshop XI - Default Namespaces Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2007 12:06 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item XML Workshop XI - Default Namespaces

.
Post #424437
Posted Monday, April 12, 2010 2:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
Hello,

Thanks for your post. It is really useful.

Currently I am facing an issue related to performace if XML is huge (nearly about 1 MB in size - 200 records). I am using SQL Server 2008.

Our application is executing the stored procedure by passing XML as a input parameter. XML contains namespaces so I am using "WITH XMLNAMESPACES" command and use "FROM nodes" method - "T.C.value" to retrieve the information from XML file. I am parsing it and return the result set.

Can you please guide me how can I improve the performance as well as steps needs to be taken to improve the performace?

Thanks, Hardik


Thanks
Post #901416
Posted Monday, April 12, 2010 4:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Two quick suggestions that I have are the following: (1) Try using TYPED XML (2) Try using XML Indexes.


.
Post #901455
Posted Monday, April 12, 2010 5:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
Thanks Jacob for your quick reply.

I have checked that query is running perfectly fine and it is retrieving the data within 3 seconds but when I am using the function (XML data as input parameter) to retrieve the data, it is taking around 1 minute (I used same query in the function to parse the XML).

For Example below query returns the data within 3 seconds:
declare @XMLdata XML
SET @XMLdata =
'<XmlStart>..</XmlEnd>'

; WITH XMLNAMESPACES ( DEFAULT
'http://abc.com',
'http://hd.com' AS a
'http://www.w3.org/2001/XMLSchema-instance' AS i)
select
T.C.value('a:data1[1]', 'smallint') AS Column1
, T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2
from @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

But, when I use the same query in function & try to retrieve the data as below:
declare @XMLdata XML
SET @XMLdata =
'<XmlStart>..</XmlEnd>'
select * from hdFunction(@XMLdata)

It is taking 1 minute or more than it.

I need to use the function because lots of stored procedures are using the same XML. So, parsing code needs to be at one place. If is there any change in the business rule, it is easy to change at once place only.

Please guide me to slove the issue.


Thanks
Post #901487
Posted Monday, April 12, 2010 6:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Is this an INLINE Function? Can you post the create script (generate it using SSMS) of this function?

.
Post #901518
Posted Monday, April 12, 2010 6:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
Yes it is inline function. Function returns the table.

CREATE FUNCTION hdFunction ( @XMLdata XML )
RETURNS @Datatable TABLE
(
Col1 SMALLINT
, Col2 NVARCHAR(12)
)
AS BEGIN

WITH XMLNAMESPACES ( DEFAULT 'http://abc.com', 'http://hd.com' AS a,
'http://www.w3.org/2001/XMLSchema-instance' AS i )

INSERT INTO @Datatable
SELECT T.C.value('a:data1[1]', 'smallint') AS Column1
, T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2
FROM @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

RETURN

END


Thanks
Post #901538
Posted Monday, April 12, 2010 6:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Can you also post a sample XML document? I see you posted something, but the code-formatting must have eaten that. Try putting it between
 and 

blocks.


.
Post #901545
Posted Monday, April 12, 2010 6:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
Jacob, please find sample XML & its parsing function:



declare @XMLdata XML
SET @XMLdata =
'<XmlStart xmlns="http://abc.com" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="http://hd.com">
<data>
<a:Col1>
<a:col1Data>
<a:data1>5</a:data1>
<a:data2>
<a:data3>Hello</a:data3>
</a:data2>
</a:col1Data>
</a:Col1>
</data>
</XmlStart>'

CREATE FUNCTION hdFunction ( @XMLdata XML )
RETURNS @Datatable TABLE
(
Col1 SMALLINT
, Col2 NVARCHAR(12)
)
AS BEGIN

WITH XMLNAMESPACES ( DEFAULT 'http://abc.com', 'http://hd.com' AS a,
'http://www.w3.org/2001/XMLSchema-instance' AS i )

INSERT INTO @Datatable
SELECT T.C.value('a:data1[1]', 'smallint') AS Column1
, T.C.value('(a:data2/a:data3)[1]', 'nvarchar(12)') AS Column2
FROM @XMLdata.nodes('/XmlStart/Data/a:col1/a:col1Data') T ( C )

RETURN

END




Thanks
Post #901566
Posted Monday, April 12, 2010 11:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Hmm..Just ran it and it worked well.
 SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table '#3E52440B'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.



.
Post #901788
Posted Monday, April 12, 2010 10:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
That was a sample XML and function which I am using to parse the XML and return the data.

When XML has small numbers of data (suppose 2 to 3 records), it is returning data fast. But, when XML has around 175 to 200 records then it is taking lots of time to return - around 1.30 minutes.

Please guide me to solve the issue.


Thanks
Post #902123
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse