INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

UNION or UNION ALL for data in two separate databases

UNION or UNION ALL for data in two separate databases

(OP)
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?

RE: UNION or UNION ALL for data in two separate databases

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.

RE: UNION or UNION ALL for data in two separate databases

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


RE: UNION or UNION ALL for data in two separate databases

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 

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

RE: UNION or UNION ALL for data in two separate databases

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close