can I get SQL Server to choose between a table and a view?

Question

Our reports need to be live and quick - don't they all? The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys. Our tables are quite large but our daily INSERTs are relatively few. ie a table may have over 35,000 rows but only 400 INSERTs a day.

The data structures off which our reports are written are views into the live application database (technically a replicated copy).

What would be swell is to, effectively, make copies of these views every night and save those copies in the reporting database. Then have SQL Server magically determine if the table and/or the view is required to handle the reporting query. So if the query is looking at last year, it uses the table - if it's the last hour, it uses today.

It is fairly easy for me to write a view that unions the table and the "today data" that is not in the table, thanks to the incrementing PKs. But the query plan shows the view is always run, even when it's not required.

I hope that's enough detail to highlight what I am trying to accomplish. Is this possible in SQL Server? Any other creative ideas to handle this?

Thanks!!

Answer 1

Not suggesting a better way to run your queries, but hopefully answering the question. You're asking if SQL SErver can ignore the view when you're running queries, but presumably, you're writing the query against the view. In which case, no, SQL Server can't ignore the view.

However, there is a process within the optimizer called simplification. Given enough time (more on that in a moment), SQL Server can recognize which parts of a view you're using or not using and then eliminate tables from the execution plan that are not needed to satisfy the query. But, it has to have enough time to do that. Since you're working with nested views, a major coding issue, you're not generally going to have enough time for the optimizer to get a good execution plan, let alone perform simplification and eliminate unnecessary queries.

Short answer, no. There's not short cut open to you. You need to rearchitect the queries to eliminate nested views. Any other tuning you do will be, at best, perephiral to the problems you're experiencing.

Answer 2

I may be misunderstanding your requirements, but how about:

• Make an archive table that you update nightly (as you describe). You could potentially partition this on your date field.
• Alter your view to be along the lines of:

.

SELECT <fields>
FROM <tables>
WHERE datefield > DATEADD(second, -1, CAST(CAST(GETDATE() as DATE) as smalldatetime))
UNION ALL
SELECT <fields>
FROM <archivetable>