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!
LVL 11
DotNetThinkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DotNetThinkerAuthor Commented:
Appreciate the help Brandon
0
Anthony PerkinsCommented:
Or you could index the Xml document, that way you are not duplicating data...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.