Performance and Scalability of SqlXml

Hi
I have noticed 2 issues with SqlXml, which I am kind of worried about,
since I have already implemented some Business-Logic on SqlXml and am
now considering reverting back to other techniques:
1st) The Framework objects Microsoft.Data.SqlXml seem to be very slow,
compared to retrieving the same data using SqlDataAdapter.
Here my results for retrieving a rather complex schema involving data
from 9 tables (I ran the test twice):
TEST 1
through MS.Net Framework)
SqlCommand object)
is returned)
TEST 2
Now this tells me that IIS is almost always faster, because the
Connection to the DB is aready open (?). Calling this test from my
workstation across the network shows slow-downs of the MS.Net query of
up to 200% versus the IIS query.
Loading the same data into a DataSet is 3x faster!! (See Schema and
SQL statements below)
2nd) Once SqlXml through IIS looses connection to the DB, it seems to
hang and not try to reconnect. I have a proxy running to which I point
all db-connections and it decides to which SQL-Server the request is
sent, but this only works for direct calls within my code, IIS seems
to not retry to connect.
Here the Schema for the test:
-- cut --
<?xml version="1.0"?>
<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:ms="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<ms:relationship name="Pr_Hr" parent="ShopView_Produkte"
parent-key="Hersteller_ID" child="ShopView_Hersteller" child-key="ID"
/>
<ms:relationship name="Pr_Pri" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Kundengruppen_Preise"
child-key="Produkte_ID" />
<ms:relationship name="Pr_StP" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Stacks" child-key="stackParent"
/>
<ms:relationship name="Pr_StC" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Stacks" child-key="stackChild"
/>
<ms:relationship name="Pr_Op" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Optionen_Map" child-key="Produkte_ID"
/>
<ms:relationship name="OP_MAP" parent="ShopView_Optionen_Map"
parent-key="ID" child="ShopView_Optionen_Data" child-key="MAP_ID" />
<ms:relationship name="Pr_IE" parent="ShopView_Produkte"
parent-key="Inhalt_Einheit_ID" child="ShopView_Inhalt_Einheit_DATA"
child-key="Map_ID" />
<ms:relationship name="Pr_Xt" parent="ShopView_Produkte"
parent-key="ArtikelNr" child="ShopView_Produkte_Extras"
child-key="ArtikelNr" />
<ms:relationship name="Pr_Be" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_Produktbeschreibung_MAP"
child-key="Produkte_ID" />
<ms:relationship name="Be_MAP"
parent="ShopView_Produktbeschreibung_MAP" parent-key="ID"
child="ShopView_Produktbeschreibung_Data" child-key="MAP_ID" />
<ms:relationship name="Pr_EG" parent="ShopView_Produkte"
parent-key="ID" child="ShopView_ProdESGruppen" child-key="Produkte_ID"
/>
<ms:relationship name="EG_Es" parent="ShopView_ProdESGruppen"
parent-key="ID Sprachen_ID" child="ShopView_ProdEigenschaften"
child-key="Gruppe Sprachen_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="Produkt" ms:relation="ShopView_Produkte"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Extras" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Extra"
ms:relation="ShopView_Produkte_Extras" ms:relationship="Pr_Xt"
ms:key-fields="ArtikelNr">
<xs:complexType>
<xs:attribute name="Typ" ms:field="Type"/>
<xs:attribute name="Daten" ms:field="Data"/>
<xs:attribute name="Erstellt" ms:field="DC"/>
<xs:attribute name="Positionen" ms:field="Positions"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="StackKinder" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Produkt" ms:relation="ShopView_Stacks"
ms:relationship="Pr_StP" ms:key-fields="stackParent">
<xs:complexType>
<xs:attribute name="Name" ms:field="PName_Child"/>
<xs:attribute name="ArtikelNr" ms:field="stackChild"/>
<xs:attribute name="Einstellungen"
ms:field="settings"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="InStacks" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Produkt" ms:relation="ShopView_Stacks"
ms:relationship="Pr_StC" ms:key-fields="stackChild">
<xs:complexType>
<xs:attribute name="Name" ms:field="PName_Parent"/>
<xs:attribute name="ArtikelNr" ms:field="stackParent"/>
<xs:attribute name="Einstellungen"
ms:field="settings"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Preise" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Preis"
ms:relation="ShopView_Kundengruppen_Preise" ms:relationship="Pr_Pri"
ms:key-fields="Kundengruppen_ID Produkte_ID">
<xs:complexType>
<xs:attribute name="Gruppe"
ms:field="Kundengruppen_ID"/>
<xs:attribute name="Preis" ms:field="Preis_EURO"/>
<xs:attribute name="USt" ms:field="USt_Satz"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Texte"
ms:relation="ShopView_Produktbeschreibung_MAP" ms:relationship="Pr_Be"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Text"
ms:relation="ShopView_Produktbeschreibung_Data"
ms:relationship="Be_MAP" ms:key-fields="MAP_ID">
<xs:complexType>
<!--
<xs:attribute name="Kurz" ms:field="Kurzbeschreibung"/>
-->
<xs:attribute name="Lang" ms:field="Beschreibung"/>
<xs:attribute name="Einnahme" ms:field="Einnahme"/>
<xs:attribute name="Warenhinweise"
ms:field="Warenhinweise"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Optionen" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Option"
ms:relation="ShopView_Optionen_Map" ms:relationship="Pr_Op"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Item"
ms:relation="ShopView_Optionen_Data" ms:relationship="OP_MAP"
ms:key-fields="MAP_ID">
<xs:complexType>
<xs:attribute name="Name"
ms:field="Bezeichnung"/>
<xs:attribute name="Sprache"
ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ID" ms:field="ID"/>
<xs:attribute name="ArtikelNR" ms:field="ArtikelNR"/>
<xs:attribute name="Bestellbar"
ms:field="istBestellbar"/>
<xs:attribute name="AutoLager"
ms:field="Lagerstand_Auto"/>
<xs:attribute name="Lagerstand" ms:field="ImLager"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="EGruppen"
ms:relation="ShopView_ProdESGruppen" ms:relationship="Pr_EG"
ms:key-fields="ID">
<xs:complexType>
<xs:sequence>
<xs:element name="Eigenschaft"
ms:relation="ShopView_ProdEigenschaften" ms:relationship="EG_Es">
<xs:complexType>
<xs:attribute name="Name" ms:field="Eigenschaft"/>
<xs:attribute name="Wert1" ms:field="Wert1"/>
<xs:attribute name="Wert2" ms:field="Wert2"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Name" ms:field="Name"/>
<xs:attribute name="Spalte1" ms:field="Beschreibung_Wert1"/>
<xs:attribute name="Spalte2" ms:field="Beschreibung_Wert2"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
<xs:attribute name="Schlusstext" ms:field="Schlusstext"/>
</xs:complexType>
</xs:element>
<xs:element name="Einheit" ms:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element name="Einheit_Item"
ms:relation="ShopView_Inhalt_Einheit_DATA" ms:relationship="Pr_IE"
ms:key-fields="Map_ID">
<xs:complexType>
<xs:attribute name="Name" ms:field="Beschreibung"/>
<xs:attribute name="Sprache" ms:field="Sprachen_ID"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Hersteller" ms:field="Name"
ms:relation="ShopView_Hersteller" ms:relationship="Pr_Hr" />
<xs:attribute name="ID" type="xs:decimal" ms:field="ID" />
<xs:attribute name="Name" ms:field="Name"/>
<xs:attribute name="Menge" ms:field="Inhalt_Menge"/>
<xs:attribute name="ArtikelNR" ms:field="ArtikelNR"/>
<xs:attribute name="GewichtKG" ms:field="GewichtKG"/>
<xs:attribute name="Lagerstand" ms:field="ImLager"/>
<xs:attribute name="Bestellbar" ms:field="IstBestellbar"/>
<xs:attribute name="Aktiv" ms:field="IstAktiv"/>
<xs:attribute name="NichtBestText" ms:field="NichtBestText"/>
<xs:attribute name="Highlight" ms:field="Highlighted"/>
<xs:attribute name="Rabattfaehig" ms:field="Rabattfhig"/>
</xs:complexType>
</xs:element>
</xs:schema>
-- cut --
And here the SQL Statements equivilent to the schema above
-- cut --
"SELECT "+
"H.Name, P.ID, P.Name, P.Inhalt_Menge, P.ArtikelNR, P.GewichtKG,
P.ImLager, P.IstBestellbar, P.IstAktiv, P.NichtBestText,
P.Highlighted, P.Rabattfhig "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Hersteller H ON P.Hersteller_ID = H.ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"X.Type, X.Data, X.DC, X.Positions "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Produkte_Extras X ON P.ArtikelNr = X.ArtikelNr "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"StP.PName_Child, StP.stackChild, StP.settings "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Stacks StP ON P.ArtikelNr = StP.stackParent "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"StC.PName_Parent, StC.stackParent, StC.settings "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Stacks StC ON P.ArtikelNr = StC.stackChild "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"Pr.Kundengruppen_ID, Pr.Preis_EURO, Pr.USt_Satz "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Kundengruppen_Preise Pr ON P.ID = Pr.Produkte_ID
"+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"TXD.Beschreibung, TXD.Einnahme, TXD.Warenhinweise, TXD.Sprachen_ID "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Produktbeschreibung_MAP TXM ON P.ID =
TXM.Produkte_ID "+
"LEFT JOIN ShopView_Produktbeschreibung_Data TXD ON TXM.ID =
TXD.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"ED.Beschreibung, ED.Sprachen_ID "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Inhalt_Einheit_DATA ED ON P.Inhalt_Einheit_ID =
ED.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"OD.Bezeichnung, OD.Sprachen_ID, "+
"OM.ArtikelNR, OM.istBestellbar, OM.Lagerstand_Auto, OM.ImLager "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_Optionen_Map OM ON P.ID = OM.Produkte_ID "+
"LEFT JOIN ShopView_Optionen_Data OD ON OM.ID = OD.MAP_ID "+
"WHERE (P.ArtikelNR = @.ArtNr); "+
"SELECT "+
"Gr.Beschreibung_Wert1, Gr.Beschreibung_Wert2, Gr.Sprachen_ID,
Gr.Schlusstext, "+
"ES.Eigenschaft, ES.Wert1, ES.Wert2 "+
"FROM ShopView_Produkte P "+
"LEFT JOIN ShopView_ProdESGruppen Gr ON P.ID = Gr.Produkte_ID "+
"LEFT JOIN ShopView_ProdEigenschaften ES ON Gr.ID = ES.Gruppe AND
Gr.Sprachen_ID = ES.Sprachen_ID "+
"WHERE (P.ArtikelNR = @.ArtNr);"
-- cut --
regards, Tilli
Noone has anything to say? Noone tested performance?
Any replies would be apprechiated!
regards, Tilli