Re: XML performance extremely slow for no obvious reason

On Sun, 04 Mar 2007 14:11:18 -0500, Steve Kass <skass@xxxxxxxx> wrote:

Thanks for the followup. This level of integration of XML into SQL Server
is new, and one hopes the folks at Microsoft will make progress so it's
more useful. As for the problem you're solving, XML may not be the
ideal solution because of some of these problems. While SQL Server
doesn't "have arrays," it sort of does, if you use a string as an array.
Now that the (max) types are available, it's even easier if the "arrays"
are long.

For an thorough treatment of the subject of arrays and SQL Server,
and examples of alternatives you may find faster, read Erland Sommarskog's
article here, which he literally just finished updating for SQL Server 2005
yesterday!

http://www.sommarskog.se/arrays-in-sql-2005.html

Yeah, it looks like XML is probably our best bet in this situation.

I guess it's not XML as such that's the problem, just the query optimizer
being stupid.

Given the three ways of writing the query:

subselect
temp table
XML

The first way is always slow (always creates millions of rows internally).
The second way is always fast (so far, at least).
The last way is fast when there are a few conditions, but slow if there
are lots of conditions.

And the last way can be made to always go fast, if you add "option
(recompile)" to the end of the query.

I'm pretty sure the query compiler/optimizer is actually doing something
wrong here, but using the temp table works, so we can go with that.
.

Relevant Pages

  • Re: XML performance extremely slow for no obvious reason
    ... on optimizing XQuery in SQL Server 2005. ... doesn't "have arrays," it sort of does, if you use a string as an array. ... it looks like XML is probably our best bet in this situation. ... I guess it's not XML as such that's the problem, just the query optimizer ...
    (microsoft.public.sqlserver.xml)
  • RE: Query producing XML appears to be cached
    ... By default the XMLDataSource control always caches its data, ... I have an app that executed a sql server query that produces an XML ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: DTS Global Variables + SQL query
    ... they query I'm trying to run isn't as straight forward as I originally ... >>I'm creating a new Transform Data Task in SQL Server 2000. ... >>'Creating an XML object to read the XML ... > Script Task to set the values to global variables makes sense. ...
    (microsoft.public.sqlserver.dts)
  • Query producing XML appears to be cached
    ... I have an app that executed a sql server query that produces an XML ... actually executing. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: OLEDB consumer and for xml auto SELECT
    ... Query Analyzer but when I try to get it from the OLEDB consumer, ... >> I have a SELECT statement which retrieve data in xml format (FOR XML ... >> for SQL Server to the ODBC provider but the results are still the same! ...
    (microsoft.public.data.oledb)