Sorry, got inundated yesterday, but wanted to catch up with this. As mentioned, we really need everything for tuning assistance. The article describes the easiest ways to get it for us.
jrestuccio (4/9/2013)
Let me describe a little bit about why the data is deleted so regularly and why it's important to not slow down insert speeds.
Even with a clustered index you won't see a significant difference in insert speeds. Start doing indexed views and triggers and that's a different story, or overloading the non-clustered indexing.
Imagine 1000 searches being performed at one time, and as soon as the search is done, it collects the data and inserts the data into the TFX_v2_unfilteredrates table.
Not really an issue. I/O is I/O. You might even get better performance with a split index to avoid hotspotting and using a really loose fillfactor, but that's a different story.
The reason we need this to happen quickly is because we have a massive amount of searches to complete, and since the data needs to be FRESH it needs to be collected within 1 day.
The reason for deleteing the data is merely to keep the size of the table down to a workable size. Since I believe our queries are poorly written........
It's hard not to have poorly written queries when those queries have no indexes to work from. Get indexes into place and you may find you can actually keep your archive.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA