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.
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!
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!
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
LVL 39
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.
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).
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).
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.
So more for 'descriptive' information or information that need to be flexible or extendable ... but then not trustworthy for querying.
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.
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.
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.
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.
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 trialIt'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.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - PowerPoint 2007… 229 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Outlook 2007 Ba… 238 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Word 2010 Inter… 173 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.