2

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!!

|improve this question
  • 3
    FYI most folks on this site will never consider 35k rows "quite large" – JNK Mar 12 '12 at 17:26
  • Great point. That was the easiest one for me to count - and I was hoping to illustrate the low percentage of change. I should have mentioned to 1 million-ish row table that grows by about 1k a day... :) – JHFB Mar 12 '12 at 17:29
  • 1m is still pushing it tbh, but I don't think it's that relevant. Are you actually having performance issues now or are you optimizing before you have problems? – JNK Mar 12 '12 at 17:30
  • 1
    We are having performance issues because we have views on top of views on top of views. We cannot index them because of their complexity and because we cannot use schema binding (don't get me started). I'm working on tackling the issues from a number of different angles (indexes, most efficient SQL, etc) but my question here would be the "quickest win" if it were possible. – JHFB Mar 12 '12 at 17:39
  • A view is a table. I suggest you use the term "base table" and "view" (or "viewed table is you want to be really strict :) – onedaywhen Mar 13 '12 at 10:56
3

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.

|improve this answer
  • Excellent, Grant - you are absolutely correct... I ran a few quick tests and validated your comments. I'm going to have to do some more thinking on this to see if I can leverage simplification anywhere. I appreciate your help! – JHFB Mar 12 '12 at 18:34
  • 2
    @JHFB, views calling views that call views is one of the biggest performance killers out there. They are also extremely hard to maintain (so much so that you usually end up adding another view on top to get the additional field you need from the bottom view but don't want to trudge down 15 layers of complex SQl to find where you need to add the column). I see no way to get a quick win from this design except to take the poorest performers and replace the code for them first. – HLGEM Mar 13 '12 at 18:05
  • @HLGEM - preaching to the choir. :) Working on simplifying them has been the main focus of my March thus far. – JHFB Mar 13 '12 at 18:34
0

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>
|improve this answer
  • Thanks - that is exactly what I've done. But what I'm wondering/hoping is that SQL Server could decide to not run the first part of that statement (the SELECT <fields> FROM <tables>) when it doesn't need to. I am finding modest efficiency gains with the UNION ALL - just looking to see if I can make it even faster. – JHFB Mar 12 '12 at 17:50
  • If you specify a date that is outside that range then it should get optimized out. – JNK Mar 12 '12 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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