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!