Advantages / Disadvantages of OPENQUERY versus performing operations on code level

What are the primary advantages and disadvantages of using OPENQUERY to combine 2 databases in one sql query when the same end result could be attained by performing 2 separate queries to different databases and performing the operations on the code level? I have to do just that and I have both options available to me. Thanks.

EDIT

To further clarify, the process using the OPENQUERY sql would run throughout the day to maintain a product catalog and join data from 2 different databases. One datab开发者_Go百科ase has the real time data I need and the other has the descriptive information I need.

Personally, if it is possible, I would use a single query - it cuts down on bandwidth and lag between you and the servers (servers being the faster parts of the whole picture)

It also depends on what you are doing with OPENQUERY. Are you combining data from the two queries? Are they correlated? If they are - you should really be linking them up in a JOIN across OPENQUERY rather than attempting to perform complex joining at the front end.

When you combine two datasets using OPENQUERY, you let SQL Server choose the join algorithm, do the fetches, allocate and free the memory and temporary disk space, present the results in tabular form and do a thousand other little things.

When you combine them in your code, you should implement all this yourself.

Disadvantages are that implementing these things from scratch requires lots of time and effort.

Benefits are that you are probably able to do this more efficiently than Microsoft developers.

It can be easily seen that the disadvantages are certain and the benefits are vaguely possible.

If this was run only occasionally, I'd tend to OPENQUERY to let the RDBMS work things out (as per Quassnoi's answer). For example, overight batches or reporting.

Given potential (not actual) issues with OPENQUERY though, I'd be wary about running it often or at peak times. In this case, I'd also consider staging the OPENQUERY data into a local table on a regular basis and use this local table.

"It depends":

• frequency of use
• performance of the OPENQUERY
• data volatility
• ...