For various corporate and historical and replicating reasons, I have multiple databases that contain similar-structured tables, holding the main production data used as a source for other home-made applications and reports.

In a particular application, I created views that UNION all theses similar tables, adding a column to know the source of each row. The problem I have is that this view, as explained in Books Online and various other posts in various forums, cannot have any indexes (because of the UNION and the fact that the source is in another database, thus it cannot be SCHEMABOUND), and so is performing quite poorly.

The question is: if I need the data as live as possible, what other choices do I have to increase performance somewhat?

  • Database A, table X
  • Database B, table X
  • Database C, table X

Application S (in it's own database), containing view:

SELECT 'A' as source, * FROM A..X
SELECT 'B' as source, * FROM B..X
SELECT 'C' as source, * FROM C..X

I am using SQL Server 2012, if there are server-specific elements that could help me.

I am lucky, for now, all databases are on the same server, so I am not using linked servers. But application S can be using Synonyms to reference the various "table X" if needed.

Yes, I did a lot of searches. I got a lot of "it's impossible", but came up empty solution-wise, other than the idea of copying the tables locally on a schedule, something hard for us considering the amount of data that would need to be copied over, and how often it would need to be copied. I am looking for alternative ideas that could lurk in the corners.

The option of turning the view into a stored procedure could be considered.

Thanks in advance

  • 1
    The first thing I would do is change the UNION to a UNION ALL. The implicit DISTINCT in the UNION will cause performance hits. Second, make sure your tables are indexed effectively to begin with. Finally, you could try an SP and loads all the data into another table, one at a time. You could create an SSRS report that cached the results every 15 minutes. You could create an SSIS package that regularly updates a table. You could enable CDC on the tables and have them replicated to another DB. There are lots of options - you just have to experiment a little. – Steve Mangiameli Oct 28 '14 at 13:39
  • To add to @Steve's suggestions, you could also look into transactional replication. You would need to set up the publications for each db set up a distributor, and create the subscriptions to write into a new reporting db. This can all be done on the same system, but there is overhead associated with it. It is real time, but read up on it to see if it fits. -Chris – CleanFill Oct 28 '14 at 16:03
  • 2
    Why is the view so slow to begin with? The query optimizer does not even know what a view is. It inlines the definition into the query plan as if you had textually copied it into your query. Post the query plan that you want to go faster. – usr Oct 28 '14 at 17:36
  • Sorry for taking a long time to answer. I have been pulled on another urgent project that needed manpower, so this application got temporarely shelved. I will start with replacing the UNIONs with UNION ALLs and see how it goes. @usr:the Query Plan pointed me toward another reason as to why the SP using the view was taking a very long time (a weird SELECT in another table to add complementary data to the recordset). You have my thanks :-) – Philippe Nov 7 '14 at 16:10

Your Answer

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

Browse other questions tagged or ask your own question.