UNION or UNION ALL for data in two separate databases 3

[jchewsmith]

I have sales order line and header data in two different databases (not the same info but different customers) and I need to create a report on all of the data.

I have tried to do a UNION and a UNION ALL query but it is very slow.

In the end I want to use Crystal reports to allow users to run and I am currently trying to create the union query in a view.

Does anyone have any other suggestions?

 

[Olaf Doschke]

If these databases are not near to each other such queries can be slow.

Theres nothing making a query technically faster whithout changing the outset of bandwidth of connections.

The typical solution is pulling data into a data mart or data warehouse to centralize it, especially for BI purposes not needing all live data but monthly, quarterly, yearly reports, this is the typical concept to have such BI insights about data. This isn't only true for data scattered in multiple databases, as the strucutre of such data marts can be optimized for BI insights, too. While loading data from all necessary sources you do already transform it and build up your totals, sums, averages, distributions, standard deviations or whatever else is of interest in reports.

Bye, Olaf.

 

[johnherman]

You can bring the data from one of the distant databases to a local temporary table on the other database, then run your UNION queries.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[kjv1611]

Sorry posting so late - just saw the question.

If you're unable to go the data warehouse / datamart method, the next best method, I think, would be to pare down what data you're comparing. You can do this with CTEs or SubQueriess. You have to look at your data and what is being compared how. Sometimes, a temp table is the best method, sometimes a CTE or Subquery, and sometimes a Table Variable (though I think the latter is a much more rare case).

If you have large tables you're querying in the UNION query, it should pay off rather well to filter them first with CTEs or Subqueries and then write your UNION against the filtered results.

Something like:

[CODE SQL];WITH Table1 AS
(
SELECT The,Fields,I,Need FROM db1.dbo.Table1 WHERE DateAdded > GETDATE()-100
)

, Table2 AS
(
SELECT The,Fields,I,Need FROM db2.dbo.Table2 WHERE DateAdded > GETDATE()-100
)

SELECT * FROM Table1
UNION
SELECT * FROM Table2[/CODE]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[johnherman]

If this UNION query is something that you're going to do frequently, you might consider replication of one table to the other database. Using either built-in replication or a custom process to only retrieve the rows and columns you need via a SQL query that runs periodically. Cross platform queries, especially joins, are notoriously slow.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity