XPath Variable Query Performance using URL vs ADO

XPath Variable Query Performance using URL vs ADO

Post by Dave Gabel » Wed, 08 May 2002 01:01:55



I am trying to improve the performance of an xpath query
so I have created a template out of it and am passing it a
parameter on the URL line.  My first query takes about 2
minutes, but subsequent queries only take about 4 seconds
using IE 5.5 through my Personal Web Server.  When I try
the same query through vb using ADO, each query takes
about 2 minutes and I am not seeing the benefits of using
the parameter like I did through the URL.  Any ideas of
what I might be doing wrong?

Thanks,

Dave
----
xpath query:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:header>
        <sql:param name='orderNumber'>EDI1</sql:param>
    </sql:header>
    <sql:xpath-query mapping-
schema="c:\inetpub\wwwroot\stc\stc_2_order_dt.xsd">
            business_document[order_header
[order_number=$orderNumber]]
    </sql:xpath-query>
</ROOT>

 
 
 

XPath Variable Query Performance using URL vs ADO

Post by Amar Nalla [MS » Wed, 08 May 2002 05:01:04


Hi,
  I would guess that the later query through the URL is giving you cached
results. You can disable caching of results throught the IIS configuration
tool avaible via SQLXML 3.0. Let us know if you see any difference.

Thank you,
Amar Nalla

This posting is provided "AS IS" with no warranties and confers no rights.
--------------------

>Content-Class: urn:content-classes:message


>Subject: XPath Variable Query Performance using URL vs ADO
>Date: Mon, 6 May 2002 09:01:55 -0700
>Lines: 28

>MIME-Version: 1.0
>Content-Type: text/plain;
>    charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcH1F1fkqRgMrOhhS1yuKRO9De4cCw==
>Newsgroups: microsoft.public.sqlserver.xml
>NNTP-Posting-Host: TKMSFTNGXA10 10.201.226.38
>Path: cpmsftngxa08!cpmsftngxa07
>Xref: cpmsftngxa08 microsoft.public.sqlserver.xml:13001
>X-Tomcat-NG: microsoft.public.sqlserver.xml

>I am trying to improve the performance of an xpath query
>so I have created a template out of it and am passing it a
>parameter on the URL line.  My first query takes about 2
>minutes, but subsequent queries only take about 4 seconds
>using IE 5.5 through my Personal Web Server.  When I try
>the same query through vb using ADO, each query takes
>about 2 minutes and I am not seeing the benefits of using
>the parameter like I did through the URL.  Any ideas of
>what I might be doing wrong?

>Thanks,

>Dave
>----
>xpath query:

><ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
>    <sql:header>
>        <sql:param name='orderNumber'>EDI1</sql:param>
>    </sql:header>
>    <sql:xpath-query mapping-
>schema="c:\inetpub\wwwroot\stc\stc_2_order_dt.xsd">
>        business_document[order_header
>[order_number=$orderNumber]]
>    </sql:xpath-query>
></ROOT>


 
 
 

XPath Variable Query Performance using URL vs ADO

Post by Dave Gabel » Fri, 10 May 2002 21:49:17


Amar,

Disabling the caching of the template/schema does have a
big effect.  (Sorry, I misread your first response.  I was
looking for an option regarding caching the actual
results.) Based on that, how can I cache the
template/schema with ADO to get the performance benefits?

Regards,

Dave

>-----Original Message-----
>Dave,
>  Caching can be prevented using the Config tool. It is
available in the
>Advanced tab.  As for the difference you are seeing, let
me investigate and
>I will let you know.

>Thank you,
>Amar Nalla

>This posting is provided "AS IS" with no warranties and
confers no rights

>--------------------
>>Content-Class: urn:content-classes:message


>>References: <0bf701c1f517$57e4cd30


- Show quoted text -


>>Subject: RE: XPath Variable Query Performance using URL
vs ADO
>>Date: Tue, 7 May 2002 05:27:12 -0700
>>Lines: 90

>>MIME-Version: 1.0
>>Content-Type: text/plain;
>>        charset="iso-8859-1"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>>Thread-Index: AcH1woOoIkMgVQtJQbeC1nNnejhMNw==
>>Newsgroups: microsoft.public.sqlserver.xml
>>Path: cpmsftngxa08
>>Xref: cpmsftngxa08 microsoft.public.sqlserver.xml:13020
>>NNTP-Posting-Host: TKMSFTNGXA10 10.201.226.38
>>X-Tomcat-NG: microsoft.public.sqlserver.xml

>>Amar,

>>I do not see an option for caching results under the IIS
>>config tool, however, each time I go through the URL, I
do
>>see the transaction appear in SQL Profiler, so I believe
>>it is hitting the database.  Also, I have a parameter
that
>>I am passing in that is giving me data for different
>>orders (so I believe the results are not cached at the
IIS
>>level).  Unfortunately, such a large query is being
>>generated I cannot trap the whole statement in SQL
>>Profiler (if I click on TextData profiler just beeps
>>rather than display the data in the lower section of the
>>screen), since my first way of attacking this was to
>>compare what was being generated via the url and compare
>>it against what ADO was generating.

>>Thanks,

>>Dave
>>>-----Original Message-----
>>>Hi,
>>>  I would guess that the later query through the URL is
>>giving you cached
>>>results. You can disable caching of results throught
the
>>IIS configuration
>>>tool avaible via SQLXML 3.0. Let us know if you see any
>>difference.

>>>Thank you,
>>>Amar Nalla

>>>This posting is provided "AS IS" with no warranties and
>>confers no rights.
>>>--------------------
>>>>Content-Class: urn:content-classes:message


>>>>Subject: XPath Variable Query Performance using URL vs
>>ADO
>>>>Date: Mon, 6 May 2002 09:01:55 -0700
>>>>Lines: 28
>>>>Message-ID: <0bf701c1f517$57e4cd30


- Show quoted text -

Quote:>>>>MIME-Version: 1.0
>>>>Content-Type: text/plain;
>>>>        charset="iso-8859-1"
>>>>Content-Transfer-Encoding: 7bit
>>>>X-Newsreader: Microsoft CDO for Windows 2000
>>>>X-MimeOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>>>Thread-Index: AcH1F1fkqRgMrOhhS1yuKRO9De4cCw==
>>>>Newsgroups: microsoft.public.sqlserver.xml
>>>>NNTP-Posting-Host: TKMSFTNGXA10 10.201.226.38
>>>>Path: cpmsftngxa08!cpmsftngxa07
>>>>Xref: cpmsftngxa08 microsoft.public.sqlserver.xml:13001
>>>>X-Tomcat-NG: microsoft.public.sqlserver.xml

>>>>I am trying to improve the performance of an xpath
query
>>>>so { w ,O% ,?  tCk  c I have created a template

out of it and am passing it

- Show quoted text -

Quote:>>a
>>>>parameter on the URL line.  My first query takes about
2
>>>>minutes, but subsequent queries only take about 4
>>seconds
>>>>using IE 5.5 through my Personal Web Server.  When I
try
>>>>the same query through vb usin