Although XML has good support in SQL 2005 I haven't used it heavily 'till today. I had to optimize an application that uses a table with two XML columns. These columns were of type text and filter operations against them was costly.
Let's say query like this one takes about 2 secs for execute (in table with only 2000 rows):
SELECT
*
FROM tableName
WHERE xmlColumn like '<AccountID>23</AccountID>'
Although this is not the best way to acheve it it worked... slowly :) So it worths to invest some time to make the changes.
I know the columns should be converted to type XML but I didn't know if this would speed up the process either how to manipulate xml from T-SQL.
in order to explain better how to work with XML we will use the following XML:
<
root>
<node id="1" value="test1" />
<node id="2" value="test2" />
<AccountID>23</AccountID>
</root>
Step 1 - Manipulate with XML from T-SQL
There are some good pages in SQL 2005 Books Online called xml Data Type Methods. They explain well how to work with xml.. So briefly:
in order to get value as result from query you'll need query like this one
SELECT
xmlCollumn.query('data(/root/node/@id)[1]')
FROM tableName
In order to get value from XML we need two thigs- XQuery that specifies which nodes to be returned (@id specify the attribute) and in square brackets which value to be returned if there are more than one nodes with same name (as in our case).
For filtering by value within XML this query would help
SELECT
*
FROM tableName
WHERE xmlCollumn.value('data(/root/AccountID)[1]', 'int') = 23
Note: By changing type from text to xml and modifying SQL statements I was able to execute same query for 0.0001 secs instead of 2 seconds. Seems like good progress...
Step 2 - Using it from .NET Application
So far so good. Let's use it in .NET Application. This one seems easy
1: string strXml="";
2: SqlConnection cn = new SqlConnection("connection strin here"); 3: SqlCommand cmd = new SqlCommand(@"SELECT *
4: FROM tableName
5: WHERE xmlColumn.value('data(/root/AccountID)[1]', 'int') = 2", cn); 6: cn.Open();
7: try
8: { 9: SqlDataReader rd = cmd.ExecuteReader();
10: if (rd.Read())
11: { 12: strXml = rd[1].ToString();
13: }
14: rd.Close();
15: }
16: catch (SqlException ex)
17: { 18: MessageBox.Show(ex.Message);
19: }
20: finally
21: { 22: cn.Close();
23: }
24: this.txtContent.Text = strXml;
I expected this to be all but no luck... I got funny exception:
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
So including "SET ARITHABORT ON;" above every SQL statement used in the code solved the issue.
Overal speed-up in the application was 19 seconds. I started from 20 secs for performing certain operation and same operation was executed for 1 sec. And this was just with converting columns (and queries) to facilitate XML in SQL side... Seems like a good progress
Hope this helps