2

I have to build an application that allows the end user to select what columns they want in their report.

For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns available that could potentially be selected.

I am worried that this will greatly harm the performance of the query. What are my options? Create indexes for each possible query is not really possible.

Would a columnstore index be beneficial?

Improve this question
3
  • well you can find a good information from erland sommerskog article here sommarskog.se/dyn-search-2008.html but its for version 2008 but a good one to start
    – Biju jose
    Commented Feb 23, 2014 at 15:52
  • Usually there are a few pieces of required information that helps the query most of the way. Could you edit the question to include a sample of the table schema and maybe more about the business requirements?
    – Jon Seigel
    Commented Feb 23, 2014 at 19:32
  • You mentioned dynamic stored procedures, do you mean a dynamic query? Or a dynamic query constructed in a stored procedure?
    – Michael J Swart
    Commented Feb 24, 2014 at 14:50

1 Answer 1

Reset to default
4

Dynamic SQL isn't in and of itself a performance problem. After all, the queries run against SQL Server regardless of how they were assembled, and still generate (and cache) the same execution plans as identical queries not constructed dynamic SQL. In ancient SQL Server versions, things like stored procedures were compiled as a whole, but SQL Server has been able to optimize at the statement level for a long time now.

What you need to be sure of is:

  • that the rows you are after can be seeked efficiently. You're never going to be able to create a covering index for all 60 columns, but if you can reduce the number of rows, any lookups or other mechanisms to get the columns not in the index shouldn't be too terrible. Hard to get into more specifics here, without any specifics to start with. I'm not sure if a columnstore index is going to give you what you need, especially because in SQL Server 2012 at least, there are a lot of limitations that most shops can't work around.

  • another option is to ensure that your index(es) used for searching to locate rows have all the output columns that aren't used as predicates as INCLUDE columns. These don't take up space in the index key but can help prevent lookups without creating a different index for every output combination. The trick here is to simply always return all columns, but move the logic of which columns to show to the reporting tool. Another nice thing about this is that if the user decides they want to add these three columns and hide those other two, the application should be able to do that without making another round-trip and re-querying all the data.

  • make sure you are using the optimize for ad hoc workloads server setting. This prevents single-use plans from occupying space in the plan cache wastefully.

Improve this answer
2
  • What are your thoughts on OPTION(RECOMPILE) instead of optimize for ad hoc workloads on a shared server? (It really bothers me that the latter is a server-wide setting, instead of a database setting.) Of course we could also get into results caching here, too.
    – Jon Seigel
    Commented Feb 24, 2014 at 14:43
  • 1
    @Jon I don't see any harm in setting it server-wide - I have yet to see a workload perform worse because of the one additional step when a plan is used a second time, vs. paying the recompile cost every single time.
    – Aaron Bertrand
    Commented Feb 24, 2014 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.