4

The view I'm building is getting more complicated and bigger every week. The idea for it was to have a source for a number of multiple reports for management, including ad-hoc queries done by some of them (Accountants - what can I say). I'm not worried about the load on the server - this one is dedicated just for that, so it will be a while until any serious resources got used. But it's taking longer and longer to do anything on it.

It's about 30% calculated fields, has at the moment with 25 joins of which I can drop maybe 8, but there will be more added at some point. No way to make it indexed, as it's spanning 4 different databases (for now), so performance is tortoisery at best.

So I'm thinking to dump the contents of the view into a table on schedule and the question is: is there a better way? And if not, what obstacles to look out for?

EDIT: Staleness of data: up to previous day. Data range: all rows, unfortunately, with current count 3.2 million. Also, there is the issue of the development phase - right now we're working out what we need, want and desire, so the performance tuning is pointless ATT. Indexing would be a good start, but not right now on this view. Split into multiple views/queries sounds good, but will not cut down execution time in any meaningful sense (down 20 minutes sounds great until you realize that starting point is 75 minutes).

Data I'm using is already copied from other databases(and servers) via SSIS package. Reworking it to consolidate data in one database might be doable, but that's not my jurisdiction. This may involve financial costs and until all the dev work is finalized will be rejected by management.

All I know that I need to speed things up considerably and fast. May be dirty. Unless I'll spend more time waiting for queries to execute than on development proper.

  • Have you thought about using Analysis Services? If your users are willing to not have real-time data then it sounds like you are almost there. But as spaghettidba suggests in his answer, having a one size fits all query is just not going to perform as well as having multiple targeted queries. – Jonathan Fite Mar 13 '17 at 12:32
  • My gut instinct is that you need an ETL to load your data into a denormalized, star-schema reporting database. – jpmc26 Mar 13 '17 at 18:50
18

I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over.

Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason.

A more sensible approach would be to have a separate, optimized, query for each report and have the bare minimum tables involved. I know that this sounds like a lot of work, but code reuse is really difficult to achieve in T-SQL without giving up performance.

That said, if all your reports are based on more or less the same data, an indexed view or a materialized intermediate table could be a viable solution. Indexed views have some limitations that make them impractical in some contexts (no self joins, no outer joins, no UNIONs etc...) and may introduce an unacceptable write overhead, but are really powerful when used correctly.

  • Indexing view is out of the question - multiple databases to pull data from. But your answer gave me some insight into the problem which might help. Simplicity might be the solution. So +1 but no green tick, sorry ;) – AcePL Mar 13 '17 at 11:32
  • 1
    "code reuse is really difficult to achieve in T-SQL without giving up performance" It's not just "difficult to achieve." It's a poor design decision. There's no reason to reuse a single query unless two things need the exact same data. – jpmc26 Mar 13 '17 at 18:10
  • The best way to reuse code (I am aware of) would probably be Table-Valued Functions, which are sort of expanded as a part of the query. (So the body of the TFV is involved in query plan, optimization etc.) – Kuba Wyrostek Mar 13 '17 at 18:20
5

So while periodically dumping the results of the view into a table might be easy to do (and may be worth a test, if you already have everything else built), I'd suggest pulling (filtered) rows from the source tables that are currently spread across multiple database into a single database, and building your new view(s) from those.

Advantages:

  • You can exclude columns or rows of those large source tables, or do the calculations/summaries when you copy the data over
  • You can add appropriate indexes to the staging tables, specifically crafted for the views/queries/reports, without impacting the other systems
  • Since all tables will be in the same database, you could make indexed views (assuming other prohibitions don't apply)
  • You could do some basic joins/lookups when you copy the data, to reduce the total number of tables joined in the final query or queries

Disadvantages:

  • You'll have to figure out how to copy those source tables over, either using custom routines (maybe an SSIS package), or using SQL replication. Depending on your business requirements, this may be complicated (for example, how quickly do changes in the source systems need to be reflected in the reports?)
  • If the source tables are prohibitively large (and you need all rows), this may not be feasible

I actually concur with spaghettidba that building individual queries/view for different reports has many advantages (or at least more than one view, if you have some very similar reports that could use the same source) - my recommendation doesn't conflict with that, you could copy the source tables over and build separate queries/views.

  • 1
    This is exactly what we do, and in fact we find that the "disadvantage" of cloning the data to be a bonus: we can use different tools/policy/procedures for different bits of data. EG we have some data we only have to load once every 6ish months via a SSIS XML load, and we have some data that pulls from linked DBs every 15 minutes. The related bit of multiple views/stored procedures for the reports is also mirrored by ours. (EG a 'common' view, then others JOIN to that one for the extra data) – admalledd Mar 13 '17 at 19:56
3

If they are willing to work with day old data then a table of the view updated nightly is an option. 3.2 million roww is a lot but unless very wide then manageable.

In a transaction

  • take a table lock
  • truncate table
  • disable all indexes other than PK (clustered)
  • insert data
  • enable indexes (this will rebuild)

Still give them access to the View for those that need current data and are willing to pay the price.

1

3m rows is not really a lot to pull in daily, that can be just one daily feed in a data warehouse. And with a traditional DW extraction of just the new and changed rows, it wouldn't be a significant load to bring into a datawarehouse on a daily if not hourly basis.

And a (dimensional) data warehouse schema is ideal for running a lot of ad hoc queries where data is handled different ways. I would definitely advocate for remodeling your data into a dimensional model and start bringing those disparate data sources into a data warehouse. You're already pulling from different servers and you're already expecting data to only be as of last day, so this is the perfect opportunity to standardize and start your data warehouse.

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.