Issues with performance of XQuery on SQL Server 2005

Hi folks,

we are executing the following Xquery on SQLserver 2005.

select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable
where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1

Its taking 50 Secs to search from 10000 records{without indexes}

The table has 3 columns sno,policy_id,policy_xml.

We have primary index on policy_id field and 1 secondary index(path index) on the table.
When we enable the index the query takes 380 secs.

The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?

We have an urgent requirement to resolve this issue. Kindly let us know the resolution ASAP.

Please let us know if you require any other information in this regard.

Thanks,

Bhuvanesh

I am not a XML guru to exactly know where the problem might be but want to pass the following link

which talks of some performance techiniques.

http://msdn2.microsoft.com/en-us/library/ms345118(SQL.90).aspx

Regards

AK

|||

Hi Bhuvanesh,

From what you said here:

The table has 3 columns sno,policy_id,policy_xml.

We have primary index on policy_id field and 1 secondary index(path index) on the table.

I assume you haven't create XML index on column policy_xml, the DDL statement will look like this

Code Snippet

createprimaryxmlindex p_xml_idx

on policyTable(policy_xml)

go

It should definitely improve your query performance. Please let me know if otherwise.

|||

I've similar issue.

Table1 ( Xid , XML_data (xml)) where Xid is primary key and XML_data is of xml datatype.

I've set primary index on XML_data along with the three secondary indexes.

with fillfactor 90, padindex on and sort _in_tempdb is on.

xml_data stores 1000 xml's of size 150 kb each. i'm fetching single xml for particular value and it is taking 20 minutes on server with 2 gb ram.

the query is

SELECT
xml_data.query('/Info/PersonalInfo/Entity[1]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/Sno/text()'),

xml_data.query('/Info/PersonalInfo/Entity[2]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/Sno/text()')
FROM Table1

Looking out for valuable help.....

|||

Try following query to see if any improvement. I change query() to value() since you seems want to get scalar value, not xml.

Code Snippet

SELECT

x.value('(.[1]/LastName)[1]','varchar(100)'),

x.value('(.[1]/FirstName)[1]','varchar(100)'),

x.value('(.[1]/Sno)[1]','varchar(100)'),

x.value('(.[2]/LastName)[1]','varchar(100)'),

x.value('(.[2]/FirstName)[1]','varchar(100)'),

x.value('(.[2]/Sno)[1]','varchar(100)')

FROM Table1 crossapply xml_data.nodes('/Info/PersonalInfo/Entity')as t(x)

Issue with ReadNext, ReadPrev

I have an issue with the following sequence.

1) User select all recordsby executing a SqlCeResultSet ( using like % )

2) User selects "Last" by triggering the rset.readlast()

3) User now selects "Next" by triggering the rset.readnext()

4) User now select "Prev" by triggering rset.readprevious()

...

nothing happens...the textboxes do not update their values.

However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...

This behaviour is very annoying...any help here?

Thanks in advance!

I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/

To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.

|||

Ilya,

I've found a workaround in my project, without resoriting to enabled/disabled buttons.

I'll try to post the bug

Thanks for your coop.

Gus

|||

The bug 13334 in SQL Server CE has been filed to track this issue.

Hope the issue gets resolved soon.

Issue with ReadNext, ReadPrev

I have an issue with the following sequence.

1) User select all recordsby executing a SqlCeResultSet ( using like % )

2) User selects "Last" by triggering the rset.readlast()

3) User now selects "Next" by triggering the rset.readnext()

4) User now select "Prev" by triggering rset.readprevious()

...

nothing happens...the textboxes do not update their values.

However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...

This behaviour is very annoying...any help here?

Thanks in advance!

I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/

To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.

|||

Ilya,

I've found a workaround in my project, without resoriting to enabled/disabled buttons.

I'll try to post the bug

Thanks for your coop.

Gus

|||

The bug 13334 in SQL Server CE has been filed to track this issue.

Hope the issue gets resolved soon.

Issue with executing dynamic sql with inbound paramter as varchar striong

Hi All:

I am trying to execute a dynamic sql, the dynamic sql makes use of an inbound paramter defined as varchar.

When I try to execute it fails, because it does not plavce the inbound paramter in quotes.

Any help would be appreciated.

In the bound search as an eaxmple can be" 'NY'

@.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL

Here is the v_sql out put:

SELECT TOP 100 * FROM V$ZIPCODE_LOOKUP_ALL WHERE STATE = NY

As you can see the sql will fail because the NY is not in quotes.

I tried using '@.P_SEARCH_VALUE''' and other forms but could not get it work.

There are a couple of ways (and variations) to approach this issue.

First, and the most 'robust', is to use the capability of sp_executesql to handle parameters. For the best explanition and demonstration, see Erland's article here.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Otherwise, you have to devise some scheme to manage quotes. For example, in you code above you are not managing the quotes inside your string. To embed a single quote inside a string, you have to double it up. So your filter would be more like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

Handling the quotes can seem to get out of hand, and very confusing. For that reason, the first option is the best.

|||

Since you are using the sp_executesql, itself supports the parameterized quires. You need not to concatenate those values (contaminating values may cause sql injection).

You can achieve the same result using the following query,

Code Snippet

SET @.P_SEARCH_VALUE='NY'

SET @.V_SQL_FILTER = N' WHERE STATE = @.P_SEARCH_VALUE'

SET @.V_SQL = @.V_BASE_SQL+@.V_SQL_FILTER

SET @.V_PARAM = N'@.P_SEARCH_VALUE VARCHAR(100)'

EXEC sp_executesql @.V_SQL, @.V_PARAM , @.P_SEARCH_VALUE

Sample,

Code Snippet

Declare @.SQL as Nvarchar(1000);

Declare @.Value as varchar(100);

Declare @.Param as Nvarchar(100);

Set @.SQL = N'Select * from sysobjects where name=@.value'

Set @.Param = N'@.value varchar(100)'

Set @.Value = 'sysobjects'

exec sp_executesql @.SQL, @.Param, @.value

|||

For something like this:

SET @.V_SQL_FILTER = N' WHERE STATE = ''' + @.P_SEARCH_VALUE + ''''

You can use:

declare @.p_search_value nvarchar(20)

set @.p_search_value = 'This is a quote '''

select N' WHERE STATE = ' + quoteName(@.P_SEARCH_VALUE ,'''')

It is best to use sp_executeSQL, but if you have to work with quotes, this is the best tool for the job.|||try the following use three single quotes

@.P_SEARCH_VALUE='''NY'''

SET @.V_SQL_FILTER = N' WHERE STATE = '+@.P_SEARCH_VALUE

SET @.V_SQL=@.V_BASE_SQL+@.V_SQL_FILTER

EXEC sp_executesql @.V_SQL

Issue in Transaction

Dear All,

I'm very much new to SQL and right now working with basics before moving into complex SPs.

I'm facing issues in executing a simple transaction in sql. The following is the code:

"

BEGINTRANSACTION

INSERT INTO MyDatabase.dbo.Order (OrderID) values('1234')

INSERTINTO MyDatabase.dbo.MyTable (Age)values('we')

IF(@.@.ERROR<> 0)

BEGIN

PRINT('Exception Raised')

ROLLBACKTRANSACTION

END

COMMITTRANSACTION

"

The age column is of int data type and I'm trying to rollback transaction by passing char value to that column. On executing the above query, it does not go to rollback transaction part whereas it gives the error

"

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'we' to data type int.

"

Kindly request you all to help me in this regard.

Most conversion errors will fire a batch abort error, which will stop the batch from executing. If you are using SQL Server 2005, you can use the new error functionality which allows you to catch statement as well as batch abort errors.

Jens K. Suessmeyer.

http://www.sqlserver2005.de