Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a some data in one table which has rules about its validity stored in another table. The combinations leading to a valid / non valid bits and or dates being returned are quite complex but very slowly changing. I have created a view on the combined data. There are at least two options which I'm looking and need to check if there anyone knows of any gotchas:

  1. Create a complex case statement within the view and build an indexes on the resultant columns
  2. Create a computed columns on the view and build an indexes on the computed columns
  3. Create n inline functions within the view and build an index on the resultant columns

I inherently like 1 as it seems more explicit however there may be some under the hood reasons for the other options or indeed another approach.

Any views (ouch) on this please? Yes I know the answer may be do all three and compare results, but I'd like to get a line on how valid those tests might be.

share

migration rejected from serverfault.com Dec 16 '13 at 17:34

This question came from our site for system and network administrators. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Paul White, Kin, RolandoMySQLDBA, billinkc, bluefeet Dec 16 '13 at 17:34

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Can you add a (simplified) code example to your question to illustrate the three options? I'd like to help, but it's not quite clear to me at the moment what you're considering doing. –  Paul White Dec 12 '13 at 12:23

1 Answer 1

I like option 4:

  • Create a view that builds the result set you need (including evaluation of any special case rules, joins, comparisons, etc. that you might need to do).

  • Analyze the query plan that results from using that view.
    There's an old Stack Overflow answer about how to examine query plans in SQL server.
    There are probably posts about that on this site as well.

  • Optimize that query by creating necessary indexes on the underlying tables

share

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