Improving query plan compilation/caching

I have a pretty basic positional inverted index, in which I store a lot of words (search terms) and I use this to implement an efficient general purpose sea开发者_JS百科rch.

My problem is that the query plan compilation is actually taking notably longer than the execution itself, I wondered if there's something that can be done about that.

1. I'm using dynamic T-SQL (building up the query from strings)
2. I'm using a lot of CTEs
3. There's a bunch of filter check boxes that depend on the initial search result for population (take the search result and get me the count of some property of some entity). e.g. for each person found by the search text give me the distinct number of organizations involved and their respective frequency (count). These needs to be reevaluated a lot.
4. I've done parameterization (given them default sizes, not some constants though, that should be fine eh?) and qualified all tables, I rely on views where possible.

The query structurally changes every time I apply a new filter or change the number of search terms which necessitates recompilation and takes time, other than that the query plan works really well.

The thing is these CTEs and filter box results are virtually the same or near identical even if they are not structurally equivalent, I'm wondering if there's anything that can be done to improve the compilation time.

If you wanna see the T-SQL I can provide samples, it's just that it's big, it's roughly 100 lines of T-SQL per search. I thought I'd ask first, before we go down that road, maybe the solution is a lot simpler that I believe it to be?

Have you considered applying the OPTIMIZE FOR query hint?

If you can split the large query into smaller parameterised stored procedures and combine their results, they are more likely to be cached.

There is also the option of optimizing for ad hoc workloads in SQL Server 2008 (although this might be a last resort):

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO