# Cross Connection Query running ... and running and running

**URL:** <https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179>\
**Category:** Toad Data Point & Toad Intelligence Central\
**Created:** [March 23, 2012, 7:36am UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179 "2012-03-23T07:36:20Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![adayers\_453](https://avatars.discourse-cdn.com/v4/letter/a/ed655f/32.png) [@adayers\_453](https://forums.toadworld.com/u/adayers_453)\
**Post date:** [March 23, 2012, 7:36am UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/1 "2012-03-23T07:36:20Z")

</div>

I am extremely new to TDA, got it yesterday. I saw a demo and thought there are some really good features in this. So I took one of my queries I am running in Rapid SQL (cross connection type) and thought it should be easy to run in TDA. I changed all the table names to include connection and schema. I ran it… it just keeps going never finishes. in Rapid SQL it takes about 12sec and returns about 12,000 rows. Do I need to do something else?

I want to share the SQL but due to privacy issues cannot. basically

Select \*  
from con1.schema1.table1 t1  
join con1.schema1.table2 t2  
on t1.key = t2.key  
join con1.schema2.table3 t3  
on t3.key = t2.key  
join con2.schema1.table4 t4  
on t4.key = t3.key  
where criteria.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 23, 2012, 8:14pm UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/2 "2012-03-23T20:14:18Z")

</div>

Are you using the Base edition or Pro? The base edition of Cross-Connection uses Access to perform the execution. Access is not very extensible and very slow. I recommend using the Cross-Execution engine in Pro. This is written by Quest and is much more effecient. Also, with that engine you can use subqueries to optimize even further.

Debbie

---

<div class="post-metadata">

**Author:** ![adayers\_453](https://avatars.discourse-cdn.com/v4/letter/a/ed655f/32.png) [@adayers\_453](https://forums.toadworld.com/u/adayers_453)\
**Post date:** [March 26, 2012, 9:44am UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/3 "2012-03-26T09:44:47Z")

</div>

Right now I am using the downloaded Pro Trial version. It may also have been UIW because I thought that for me to go from one Oracle DB to antoher I had to use Cross Connection, when I realized after I did not.

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 26, 2012, 2:14pm UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/4 "2012-03-26T14:14:48Z")

</div>

If you have two connections to two different Oracle instances and you want to join tables from each database in the same query, you would need to use Cross-Conneciton. Is that what you want to do? Or are you using one connection at a time and need to know how to change the windows to each connection?

Debbie

---

<div class="post-metadata">

**Author:** ![adayers\_453](https://avatars.discourse-cdn.com/v4/letter/a/ed655f/32.png) [@adayers\_453](https://forums.toadworld.com/u/adayers_453)\
**Post date:** [March 29, 2012, 8:25am UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/5 "2012-03-29T08:25:57Z")

</div>

the Cross reference between connection is @CrossDB so I can use a single query. First Query Oracle looks after if the Cross Connection is defined.

Select \*  
from SomeTable@mainDB st  
join AnotherTable@anotherDB at

Rather than

Select \*  
from Connection1.SomeTable st  
join Connection2.AnotherTable at

---

<div class="post-metadata">

**Author:** ![Debbie\_Peabody](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/debbie_peabody/32/18_2.png) [@Debbie\_Peabody](https://forums.toadworld.com/u/Debbie_Peabody)\
**Post date:** [March 29, 2012, 2:59pm UTC](https://forums.toadworld.com/t/cross-connection-query-running-and-running-and-running/7179/6 "2012-03-29T14:59:57Z")

</div>

That is an Oracle db linked query. Oracle would be the one handling the optimization on that join.

I would try using our Pro version and using our Cross-Connection. Do a benchmark test and see which way is faster. Do you have the ability to connect directly to the linked dbs? You should, so connect to both and then go to teh Query builder and drag the two tables onto the diagramer and execute your query. I would also suggest to select specific columns and add a where condition if possible.

Debbie