Foreign Key

Hi,

Is sql server performance affected with 7 to 8 FK on a single master table.

Thanks
ShubhangiNot at all

for xml explicit or xsl

I need to create a big xml report (about 60 elements, 9 levels deep). The data is in a single sql server table. For this I have been given an xsd file that the report must match in format.
What is my best option. As far as I know, I need either a for xml explicit query or I need to create an xsl document. Is this correct?
Thanks for any advice
Asim.
At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
apply an XSLT stylesheet. I haven't got any performance data to back this
up, but that's what my gut instinct tells me.
Anyone else with any actual hard-evidence to confirm / refute this?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> I need to create a big xml report (about 60 elements, 9 levels deep). The
data is in a single sql server table. For this I have been given an xsd file
that the report must match in format.
> What is my best option. As far as I know, I need either a for xml explicit
query or I need to create an xsl document. Is this correct?
> Thanks for any advice
> Asim.
>
|||Thank you for the input.
Any pointers to where I could start creating a xslt file. Never did that. Any tools.
Can the xsd file be used in any way.
Asim.
|||Nine levels is actually not that bad for EXPLICIT mode queries from a perf
issue but hard to maintain.
Yukon's nesting capabilities would be better than either from a
programmabilty point of view.
Best regards
Michael
"Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
> At 9 levels deep, I'd be inclined to go for a FOR XML AUTO query and then
> apply an XSLT stylesheet. I haven't got any performance data to back this
> up, but that's what my gut instinct tells me.
> Anyone else with any actual hard-evidence to confirm / refute this?
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> "Asim" <anonymous@.discussions.microsoft.com> wrote in message
> news:32876616-7A58-44B9-886D-A27744EF991F@.microsoft.com...
> data is in a single sql server table. For this I have been given an xsd
> file
> that the report must match in format.
> query or I need to create an xsl document. Is this correct?
>
|||And to give some further perf information:
FOR XML AUTO may not provide you the right shape for postprocessing, but if
it does, the XSLT post processing will offload some of the shaping effort
from the server (thus may improve server-side throughput), but may be less
efficient end-to-end because of needing to serialize and reparse and reshape
the data (instead of shaping it directly using FOR XML explicit).
So from a performance point of view, I would think that FOR XML explicit is
in many cases more performant.
Best regards
Michael
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:e48WQumIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> Nine levels is actually not that bad for EXPLICIT mode queries from a perf
> issue but hard to maintain.
> Yukon's nesting capabilities would be better than either from a
> programmabilty point of view.
> Best regards
> Michael
> "Graeme Malcolm (Content Master Ltd.)" <graemem_cm@.hotmail.com> wrote in
> message news:uT3FR8kIEHA.3832@.TK2MSFTNGP12.phx.gbl...
>
|||Michael thats good to know that Yukon will have better nesting solution for xml. Maybe I should do this report using xml explicit and convert to Yukon as soon as the Beta is out. This report is part of a larger B2B project and doesn't go into production u
ntil August. When will the Yukon Beta be available to the general public.
Also, I have been thinking to write an "abstract" SP that can create the xmpl explicit query dynamically. Has something like this been done? Any samples.
|||You have been most helpful.
One more question. Is there a way to return multiple records from the root table in a single xml explicit query. Below is a sample script and output to illustrate what I mean. In the query, if I include a where clause (transactionID = @.ID) all is fine but
can I return multiple transactionIDs with correctly formed xml. Again thanks for your time.
create table tblXMLTest
(
transactionId int not null primary key,
quantity varchar(100) null,
)
INSERT tblXMLTest(transactionId, quantity)
select 1, '20'
INSERT tblXMLTest(transactionId, quantity)
select 2, '25'
dbcc traceon(257)
SELECT
1 as Tag,
null as Parent,
transactionId as [rootRecord!1!transactionId!element],
null as [baseSegment!2!quantity!element]
FROM tblXMLTest
union all
SELECT
2 as Tag,
1 as Parent,
null as [rootRecord!1!transactionId!element],
quantity as [baseSegment!2!quantity!element]
FROM tblXMLTest
for xml explicit
drop table tblXMLTest
The above produces the output:
<rootRecord><transactionId>1</transactionId></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>20</quantity></baseSegment><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
The desired output is:
<rootRecord><transactionId>1</transactionId><baseSegment><quantity>20</quantity></baseSegment></rootRecord><rootRecord><transactionId>2</transactionId><baseSegment><quantity>25</quantity></baseSegment></rootRecord>
Asim.
|||The Yukon Beta should be coming out this summer. The following webpage
should allow you to nominate yourself to the beta program.
Can you send me your email alias? I then can also ask internally.
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:367BA9FB-6018-4B61-BA0A-ED6D1A56D087@.microsoft.com...
> Michael thats good to know that Yukon will have better nesting solution
> for xml. Maybe I should do this report using xml explicit and convert to
> Yukon as soon as the Beta is out. This report is part of a larger B2B
> project and doesn't go into production until August. When will the Yukon
> Beta be available to the general public.
> Also, I have been thinking to write an "abstract" SP that can create the
> xmpl explicit query dynamically. Has something like this been done? Any
> samples.
|||My email address is: asim.ahmed@.etrade.com and I work as a DBA for Etrade Financial's professional Trading division.
It would be great if I can get the Yukon Beta. By the way, your message did not include the link.
Also, I wrote a little procedure to dynamically create and run a xml explicit query, so am not worried about management as much. The only limitation is the 8000 character limit for the xml query.
I can post the script if you are interested.
Asim.
|||Thanks for the address. And sorry for not pasting the address. Here it is:
http://www.microsoft.com/sql/evaluat...ominations.asp
Best regards
Michael
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:EDDFF680-C785-446E-BE87-AB93531A47B1@.microsoft.com...
> My email address is: asim.ahmed at etrade.com and I work as a DBA for
> Etrade Financial's professional Trading division.
> It would be great if I can get the Yukon Beta. By the way, your message
> did not include the link.
> Also, I wrote a little procedure to dynamically create and run a xml
> explicit query, so am not worried about management as much. The only
> limitation is the 8000 character limit for the xml query.
> I can post the script if you are interested.
> Asim.
>

For XML Explicit

I am trying to retrieve data from a single table as an xml document
and am having difficultes getting it to work...
The data is stored in a single table and each row can relate to
another row in the same table (see example below)...
ID ParentID Description
1 null Test one
2 1 Test two
3 1 Test three
4 2 Test four
Please can someone detail the SQL (FOR XML EXPLICIT) required to
retrieve this data in a hierarchical xml document.
Many Thanks in advance.
Hi,
if i have understand, you need to generate a hierarchical xml from
father->son relationship ?!
the left join help me to build a hierarchical xml with for xml auto, in this
case i now the max level number.
if you have the necessary time, try this approach.
i find a other exemple with for xml explicit and i send it to group
i hope to help you
Boss Hog
--Look, i have choose this methode in replacement of for XML explicit
select 1 as VISIBLE,
LEVEL0.CODE as ULID, LEVEL0.ISSHEET, LEVEL0.[DESCRIPTION] as LABEL,
LEVEL0.CCODE, LEVEL0.CCDESC, LEVEL0.HIERARCHIE, LEVEL0.NBCCODE,
LEVEL0.NBLABEL,
LEVEL1.CODE as ULID, LEVEL1.ISSHEET, LEVEL1.[DESCRIPTION] as LABEL,
LEVEL1.CCODE, LEVEL1.CCDESC, LEVEL1.HIERARCHIE, LEVEL1.NBCCODE,
LEVEL1.NBLABEL,
LEVEL2.CODE as ULID, LEVEL2.ISSHEET, LEVEL2.[CODEDESCRIPTION] as LABEL,
LEVEL2.CCODE, LEVEL2.CCDESC, LEVEL2.HIERARCHIE, LEVEL2.NBCCODE,
LEVEL2.NBLABEL,
LEVEL3.CODE as ULID, LEVEL3.ISSHEET, LEVEL3.[CODEDESCRIPTION] as LABEL,
LEVEL3.CCODE, LEVEL3.CCDESC, LEVEL3.HIERARCHIE, LEVEL3.NBCCODE,
LEVEL3.NBLABEL,
LEVEL4.CODE as ULID, LEVEL4.ISSHEET, LEVEL4.[CODEDESCRIPTION] as LABEL,
LEVEL4.CCODE, LEVEL4.CCDESC, LEVEL4.HIERARCHIE, LEVEL4.NBCCODE,
LEVEL4.NBLABEL,
LEVEL5.CODE as ULID, LEVEL5.ISSHEET, LEVEL5.[CODEDESCRIPTION] as LABEL,
LEVEL5.CCODE, LEVEL5.CCDESC, LEVEL5.HIERARCHIE, LEVEL5.NBCCODE,
LEVEL5.NBLABEL
from #TMPTBL LEVEL0
left join #TMPTBL LEVEL1
on LEVEL1.FATHER_ID=LEVEL0.HCODE
left join #TMPTBL LEVEL2
on LEVEL2.FATHER_ID=LEVEL1.HCODE
left join #TMPTBL LEVEL3
on LEVEL3.FATHER_ID=LEVEL2.HCODE
left join #TMPTBL LEVEL4
on LEVEL4.FATHER_ID=LEVEL3.HCODE
left join #TMPTBL LEVEL5
on LEVEL5.FATHER_ID=LEVEL4.HCODE
where LEVEL0.FATHER_ID is null
order by
LEVEL0.ISSHEET, LEVEL0.CODE,
LEVEL1.ISSHEET, LEVEL1.CODE,
LEVEL2.ISSHEET, LEVEL2.CODE,
LEVEL3.ISSHEET, LEVEL3.CODE,
LEVEL4.ISSHEET, LEVEL4.CODE,
LEVEL5.ISSHEET, LEVEL5.CODE
for xml auto
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||Dan,
FOR XML does not support building recursively nested XML of arbitrary depth
out of a self-referenced table.
Support for this feature is being evaluated for SQL 2005.
Your a few options.
If your hierarchy depth is known you can use:
- FOR XML EXPLICIT
- SQLXML mapping schema.
Otherwise your can extract your data as XML without recursive nesting and
reshape it with XSLT on the client side/middle-tier.
If you want to use FOR XML EXPLICIT you need:
1) Use T-SQL WHILE loop or a query with CTE in SQL 2005 to move your data
into a temp table or table variable adding integer column with level in the
hierarchy;
2) Do as many self-concatenations of the table (UNION ALL) with filtering by
appropriate level each time and using proper ordering.
Here's FOR XML EXPLICIT example for two level hierarchy (using SQL 2005
CommonTableExpression to get Level column, with SQL 2000 you can use WHILE
loop to build a temp table or a table variable):
WITH SelfRefTableWithLevel(ParentID, ID, Description, Level) AS
(
SELECT ParentID, ID, Description, 0 AS Level
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, t1.Description, Level + 1
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevel t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description]
FROM SelfRefTableWithLevel
WHERE Level = 1
ORDER BY [emp!1!ID], [emp!2!ID]
FOR XML EXPLICIT
GO
Hope it works for you.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Dan Coates" <danielcoates@.yahoo.com> wrote in message
news:53c81c7f.0408030812.5e4dac1b@.posting.google.c om...
> I am trying to retrieve data from a single table as an xml document
> and am having difficultes getting it to work...
> The data is stored in a single table and each row can relate to
> another row in the same table (see example below)...
> ID ParentID Description
> 1 null Test one
> 2 1 Test two
> 3 1 Test three
> 4 2 Test four
> Please can someone detail the SQL (FOR XML EXPLICIT) required to
> retrieve this data in a hierarchical xml document.
> Many Thanks in advance.
|||I must mention that I oversimplified ordering problem with my example.
For proper ordering when your hierarchy is more than two level deep you
either need to do multiple self joins in SELECT for each level (number of
self joins would be the level each time) or construct an additional ordering
column.
Since the ordering column trick is more efficient and less obvious I will
give you a 3 level hierarchy example with a path ordering key.
WITH SelfRefTableWithLevelAndPath(ParentID, ID, Description, Level,Path) AS
(
SELECT ParentID, ID, 0 AS Level, convert(varchar(8000),ID) as Path
FROM SelfRefTable
WHERE ParentID IS NULL
UNION ALL
SELECT t1.ParentID, t1.ID, Level + 1,
Path+'/'+convert(varchar(8000),t1.ID)
FROM SelfRefTable t1
INNER JOIN SelfRefTableWithLevelAndPath t2
ON t1.ParentID = t2.ID
)
SELECT
1 Tag,
NULL Parent,
Path [emp!1!!hide],
ParentID [emp!1!ParentID],
ID [emp!1!ID],
Description [emp!1!Description],
NULL [emp!2!ParentID],
NULL [emp!2!ID],
NULL [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 0
UNION ALL
SELECT
2 Tag,
1 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
ParentID [emp!1!ID],
NULL [emp!1!Description],
ParentID [emp!2!ParentID],
ID [emp!2!ID],
Description [emp!2!Description],
NULL [emp!3!ParentID],
NULL [emp!3!ID],
NULL [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 1
UNION ALL
SELECT
3 Tag,
2 Parent,
Path [emp!1!!hide],
NULL [emp!1!ParentID],
NULL [emp!1!ID],
NULL [emp!1!Description],
NULL [emp!2!ParentID],
ParentID [emp!2!ID],
NULL [emp!2!Description],
ParentID [emp!3!ParentID],
ID [emp!3!ID],
Description [emp!3!Description]
FROM SelfRefTableWithLevelAndPath
WHERE Level = 2
ORDER BY Path
for xml explicit
GO
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.