0

I am using Sql server 2008 R2. To speed up the query execution in sql i have changed xquery part of it.I Have used cast method to query xml column and i got following results.

DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`' OR [xml_column_name].exist('for $x in /Root/Row where( ($x/xml_column_name[fn:contains(upper-case(.),fn:upper-case("94484"))]/text()) )  return $x')>0 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:

                                Trail4   Trial 3  Trial2    Trial1        Average(ms)
  Client processing time            2       3        3          4           3.0000
  Total execution time              2059    1657    1745        1608        1767.2500
  Wait time on server replies       2057    1654    1742        1604        1764.2500

New Query:

DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT [col1] as [C Sl No.] ,[Table].[Customer_Name],[sl_no] as [T Sl No.] ,[Name2] as [Coll_Name] ,[Collection_Remarks] as [Coll_Remarks]  FROM [Db].dbo.[Table] WHERE ( [Db].dbo.[Table].[Main_Phone_no] LIKE '%94484%'  Escape '`'
 OR CAST (xml_column_name as varchar(max)) like '%94484%'
 OR [Db].dbo.[Table].[KeyPhNo] = '94484' )

Result:

                               Trail4   Trial 3   Trial2    Trial1     Average(ms)
  Client processing time            4       3        2          3       3.0000
  Total execution time              314     371      230        479     348.5000
  Wait time on server replies       310     368      228        476     345.5000

Whether it is a proper way to speedup query? If not, is there any other method to improve the execution speed?

0

This is another example that demonstrates how Relational Databases are really good at finding lots of small bits of data and putting them together, but quite rubbish at taking large slabs of data and pulling them apart again.

Xml querying functions go some way towards improving this but by far the best way is to move the data that you'll be filtering by (i.e. that appears in where clauses in your queries) into old-fashion table fields and properly index them. You already have a separate Phone Number field - make sure that your application code updates that when storing this data. You might also consider triggers if you're worried about people and/or other applications changing the data by "other means".

Casting the field from XML into text completely defeats the point of holding this data as XML at all. If that sequence of character were to appear anywhere in the entire XML structure then that record would be returned - potentially lots of "false positives".

Why is it faster with the cast? The DBMS doesn't have to go through all the [relatively slow] XML processing and can "just" scan through the entire table, doing a [relatively quick] "string" search on the cast'ed field.

Why a table scan? Well, you have a leading wildcard on that criteria, which will effectively prevent the DBMS from using any indexing on that field. Expect your query performance to degrade horribly as the table grows in size.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.