[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

SQL Server XML Type Performance

I work for an insurance company and we store most of our policy information in an xml field in our policy table.
Every night a job is run that creates new renewal quotes and inserts them into our table.
On our front end we allow users to search for renewal quotes by policy number (stored in the xml).
The problem I've run into is that for the first hour or so of the business day the front end is timing out because it takes anywhere from a minute and a half to 4 minutes for the query to run. My thought is that it has something to do with the index on the xml field because after running the query in Management Studio each subsequent query takes about 10 seconds. Could that be caused by the fact that every morning (3am) there are 100-300 records added to the table? It's been a couple days and I'm at a total loss. Thanks in advance for your help!
0
DotNetThinker
Asked:
DotNetThinker
  • 3
  • 3
  • 2
  • +1
1 Solution
 
BrandonGalderisiCommented:
Depends on the index structure.  I have never been a fan of storing native XML in SQL Server.  We typically use it for storing meta data and keep it in an nvarchar(max) column.  You will not likely get the performance out of the XML datatype that you will with a regular column.  Perhaps you should consider denormalizing the policy number into it's own column.  It can still be in the XML, but keeping it in it's own column will allow for faster searching.
0
 
jogosCommented:
Could be various things: statistics, 'in memory' ... But I also am not such a fan of XML  just for things like performance, ability for constraints....

You can try to shedule that query before office hours for the time being.  But I suggest to look where the time is going (profiler...) when it's slow and try to get that out.
Don't forget that there could be another process taking resources that ends about the same time you run your query. (don't know how exact your evaluation is).

0
 
jogosCommented:
XML is good thoug for things where no filtering, sorting (-> indexes), referential integrity, ...  
So more for 'descriptive' information or information that need to be flexible or extendable ... but then not trustworthy for querying.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
DotNetThinkerAuthor Commented:
Brandon:
My thoughts exactly. Unfortunately I'm not the decision maker and we went live with it as is so it's not really a feasible solution at this time. However, I do agree that it's probably the best solution.

jogos:
Ran profiler against the query and the xquery was definitely taking the most time, as expected, but the % of time didn't seem to change between the first execution and subsequent executions.
0
 
BrandonGalderisiCommented:
Are your saves managed through stored procedure calls at least?
0
 
DotNetThinkerAuthor Commented:
Yeah everything's done through stored procedures
0
 
BrandonGalderisiCommented:
Well you can always....

add the column in (nullable to start).  
Modify the existing save procedure to pull the policy number out on save with xQuery (better per record on save than looking in all record son search).
Pull the policy numbers out of the existing XML.
Make the column non-nullable so no backend process can NOT populate it.
Index the new column.
Update your search procedure to search policy number based on the new column.
0
 
DotNetThinkerAuthor Commented:
Appreciate the help Brandon
0
 
Anthony PerkinsCommented:
Or you could index the Xml document, that way you are not duplicating data...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now