Toad World® Forums

Cross Connection Query running ... and running and running

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.

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

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.

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

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

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