MS SQL XML - SQLXML for xml explicit

Author

SQLXML for xml explicit

Toby

2005-10-27, 9:26 am

Hi ppl,
I am a newbie,a rookie in to the database world.I've got my first job now
in a small company who couldnt offord a full fledged DBA.
We have a requirement in which we need to query a lot of tables and form
numerous XML with those data.
I succeeded in completing the query for sample format by using for xml
explicit ( sql server 2000).
i had to make three select statements and union all to create the universal
table.

Well i was proud of myself.untill the client called.
He dropped the bomb shell that we need to generate a big XML with lots of
child parent.... and on seeing the query that i've written started talking
abt the performance issues..... and has asked me to prepare a doc to give
the various options available and analysing each and choosing the best.

so My question to all you ppl is that what performance issues would i face
in using for XML explicit and how do i overcome it..

is there someother efficent way to get XMl out of the database...

here is the query that i wrote..( i may be needed to have 7 selects and say
10 or more Xmls are to be generated )

SELECT
1 AS tag,
0 AS parent,
y.coursetype AS [X!1!type],
x.coursetitle AS & #91;X!1!coursetitle]
,
x.courseid AS [X!1!courseid],
y.curriculumid AS & #91;X!1!curriculumid
],
y.coursehelptext AS [X!1!helptext],
y.mastery AS [X!1!mastery],
null AS [Y!2!type],
null AS [Y!2!name],
null AS [Y!2!helptext],
null AS [Z!3!type],
null AS [Z!3!name],
null AS [Z!3!location],
null AS [Z!3!active],
null AS [Z!3!duration],
null AS [Z!3!objective],
null AS & #91;Y!2!lessonid!hid
e],
null AS & #91;Z!3!topicid!hide
]
FROM
X x,
Y y

WHERE
x.courseid=y.courseid

UNION ALL

SELECT
2,
1,
y.coursetype,
x.coursetitle ,
x.courseid,
y.curriculumid,
y.coursehelptext,
y.mastery,
z.lessontype,
z.lessonname ,
z.lessonhelptext,
null,
null,
null,
null,
null,
null,
z.lessonid,
null

FROM
X x,
Y y,
Z z

WHERE
x.courseid = y.courseid AND
z.courseid = x.courseid

UNION ALL

SELECT
3,
2,
y.coursetype,
x.coursetitle ,
x.courseid,
y.curriculumid,
y.coursehelptext,
y.mastery,
z.lessontype ,
z.lessonname ,
z.lessonhelptext ,
a.topicquiztype,
a.topicquizname,
a.quizlocation,
a.active,
a.topicquizobjective,
a.topicquizduration,
z.lessonid,
a.topicquizid

FROM
X x,
Y y,
Z z,
A a

WHERE
x.courseid = y.courseid AND
z.courseid = x.courseid AND
a.courseid = x.courseid AND
a.lessonid = z.lessonid

ORDER BY [X!1!courseid],& #91;Y!node!2!lessoni
d!hide],& #91;Z!3!topicid!hide
]

FOR XML EXPLICIT

waiting eagerly ( scratching my head),
toby

Eugene Kogan [MSFT]

2005-10-27, 9:26 am

To make a FOR XML EXPLICIT query to perform the best you could create enough
indexes on the tables so that ORDER BY does not result in a SORT in the
query plan and the indexes are used by Query Processor to implement UNION
ALLs as MERGE UNIONs.
As you can see this is not a FOR XML specific advice and the general
indexing trade-off between query performance, DML performance and data size
should be applied.

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"Toby" <Toby@discussions.microsoft.com> wrote in message
news:64FA9411-ECB1-48BB-8A75- 2931BC78E994@microso
ft.com...
> Hi ppl,
> I am a newbie,a rookie in to the database world.I've got my first job now
> in a small company who couldnt offord a full fledged DBA.
> We have a requirement in which we need to query a lot of tables and form
> numerous XML with those data.
> I succeeded in completing the query for sample format by using for xml
> explicit ( sql server 2000).
> i had to make three select statements and union all to create the
> universal
> table.
>
> Well i was proud of myself.untill the client called.
> He dropped the bomb shell that we need to generate a big XML with lots of
> child parent.... and on seeing the query that i've written started talking
> abt the performance issues..... and has asked me to prepare a doc to give
> the various options available and analysing each and choosing the best.
>
> so My question to all you ppl is that what performance issues would i face
> in using for XML explicit and how do i overcome it..
>
> is there someother efficent way to get XMl out of the database...
>
>
> here is the query that i wrote..( i may be needed to have 7 selects and
> say
> 10 or more Xmls are to be generated )
>
> SELECT
> 1 AS tag,
> 0 AS parent,
> y.coursetype AS [X!1!type],
> x.coursetitle AS & #91;X!1!coursetitle]
,
> x.courseid AS [X!1!courseid],
> y.curriculumid AS & #91;X!1!curriculumid
],
> y.coursehelptext AS [X!1!helptext],
> y.mastery AS [X!1!mastery],
> null AS [Y!2!type],
> null AS [Y!2!name],
> null AS [Y!2!helptext],
> null AS [Z!3!type],
> null AS [Z!3!name],
> null AS [Z!3!location],
> null AS [Z!3!active],
> null AS [Z!3!duration],
> null AS [Z!3!objective],
> null AS & #91;Y!2!lessonid!hid
e],
> null AS & #91;Z!3!topicid!hide
]
> FROM
> X x,
> Y y
>
> WHERE
> x.courseid=y.courseid
>
> UNION ALL
>
> SELECT
> 2,
> 1,
> y.coursetype,
> x.coursetitle ,
> x.courseid,
> y.curriculumid,
> y.coursehelptext,
> y.mastery,
> z.lessontype,
> z.lessonname ,
> z.lessonhelptext,
> null,
> null,
> null,
> null,
> null,
> null,
> z.lessonid,
> null
>
> FROM
> X x,
> Y y,
> Z z
>
> WHERE
> x.courseid = y.courseid AND
> z.courseid = x.courseid
>
>
> UNION ALL
>
> SELECT
> 3,
> 2,
> y.coursetype,
> x.coursetitle ,
> x.courseid,
> y.curriculumid,
> y.coursehelptext,
> y.mastery,
> z.lessontype ,
> z.lessonname ,
> z.lessonhelptext ,
> a.topicquiztype,
> a.topicquizname,
> a.quizlocation,
> a.active,
> a.topicquizobjective,
> a.topicquizduration,
> z.lessonid,
> a.topicquizid
>
>
> FROM
> X x,
> Y y,
> Z z,
> A a
>
> WHERE
> x.courseid = y.courseid AND
> z.courseid = x.courseid AND
> a.courseid = x.courseid AND
> a.lessonid = z.lessonid
>
>
> ORDER BY [X!1!courseid],& #91;Y!node!2!lessoni
d!hide],& #91;Z!3!topicid!hide
]
>
> FOR XML EXPLICIT
>
> waiting eagerly ( scratching my head),
> toby
>
>
>
>
>
>

Michael Rys [MSFT]

2005-10-27, 9:26 am

In addition to Eugene's recommendation, I would also like to point out what
performance issues you have to compare against the alternative of doing the
XML-ification on the client:

Unless some of the XML that you need is static (such so-called wrapper
elements such as the root element or the orders element containing all order
elements), doing the joins and unions on the server normally is better since
it avoids multiple client-server roundtrips, joins can be optimized by the
server optimizer and less data may be transported between the server and the
client.

Best regards
Michael

"Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
news:uNnOPYQ2FHA.3596@TK2MSFTNGP12.phx.gbl...
> To make a FOR XML EXPLICIT query to perform the best you could create
> enough indexes on the tables so that ORDER BY does not result in a SORT in
> the query plan and the indexes are used by Query Processor to implement
> UNION ALLs as MERGE UNIONs.
> As you can see this is not a FOR XML specific advice and the general
> indexing trade-off between query performance, DML performance and data
> size should be applied.
>
> Best regards,
> Eugene
> ---
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Toby" <Toby@discussions.microsoft.com> wrote in message
> news:64FA9411-ECB1-48BB-8A75- 2931BC78E994@microso
ft.com...
>
>

Toby

2005-10-27, 9:26 am

Thanks michael and eugene. Yes i would add indexes to the table.

i'd be given a course id..
from that i'd be required to generate a xml for that course
containing lesson details..

then from those lessonids i need to form another xml containing topic ids.

then from those topic ids i need to form another xml containing page details..

So as you see that would be a lot of XML say 20 or more ..

as you can see it would result in executing the complex 'FOR XML EXPLICIT'
everytime i need to generate the XML (20 times).
once this is done the process gets over.

If I am going to use the sql server 2000's 'FOR XML EXPLICIT' it'd take some
time.

So Is this the best way to go for such a requirement. OR should the
developers (Java :( ) develop a code to generate their own XML.

or Is there any other efficient way to do. ( oh no dont say 2005 we wont be
able to upgrade at this point of time)..

waiting eagerly for ur invaluable suggestions,
toby

"Michael Rys [MSFT]" wrote:
[color=darkred]
> In addition to Eugene's recommendation, I would also like to point out what
> performance issues you have to compare against the alternative of doing the
> XML-ification on the client:
>
> Unless some of the XML that you need is static (such so-called wrapper
> elements such as the root element or the orders element containing all order
> elements), doing the joins and unions on the server normally is better since
> it avoids multiple client-server roundtrips, joins can be optimized by the
> server optimizer and less data may be transported between the server and the
> client.
>
> Best regards
> Michael
>
> "Eugene Kogan [MSFT]" <ekogan@online.microsoft.com> wrote in message
> news:uNnOPYQ2FHA.3596@TK2MSFTNGP12.phx.gbl...

Michael Rys [MSFT]

2005-10-29, 8:23 pm

In most cases using FOR XML EXPLICIT will transport less data from the
server to the client and will perform better joins than doing it in Java
code. You can either return everything in a single XML document, or do one
per lesson details, topics, page details (and use joins).

Best regards
Michael

"Toby" <Toby@discussions.microsoft.com> wrote in message
news:BB39B69C-B8B0-48A9-B0F2- AB72468431F6@microso
ft.com...
> Thanks michael and eugene. Yes i would add indexes to the table.
>
> i'd be given a course id..
> from that i'd be required to generate a xml for that course
> containing lesson details..
>
> then from those lessonids i need to form another xml containing topic ids.
>
> then from those topic ids i need to form another xml containing page
> details..
>
> So as you see that would be a lot of XML say 20 or more ..
>
> as you can see it would result in executing the complex 'FOR XML EXPLICIT'
> everytime i need to generate the XML (20 times).
> once this is done the process gets over.
>
> If I am going to use the sql server 2000's 'FOR XML EXPLICIT' it'd take
> some
> time.
>
> So Is this the best way to go for such a requirement. OR should the
> developers (Java :( ) develop a code to generate their own XML.
>
> or Is there any other efficient way to do. ( oh no dont say 2005 we wont
> be
> able to upgrade at this point of time)..
>
>
>
> waiting eagerly for ur invaluable suggestions,
> toby
>
>
>
>
> "Michael Rys [MSFT]" wrote:
>
>

Sponsored Links