Cross Connection Query - Looking for Better Performance

Toad Data Point & Toad Intelligence Central
1

I am new to Toad (less than year with it), as we are gradually migrating away from using Hyperion (Brio). I was given a list of 21,539 part numbers and asked to return one metric on each of them (quantity sold in the last 12 months). We store this value in the INVENTORY table, so it seems to be a simple scenario. The steps that I took:

  1. Imported the list of parts from a .csv file into my Toad Local Storage Connection.
  2. Connect to the Oracle database.
  3. Setup a cross connection query:
  4. Bring in the table from local storage (TinaR_20170222)
  5. Bring in the INVENTORY table from Oracle
  6. Join on part number
  7. Select the part number from the local storage table.
  8. Select the qty RYTD from the INVENTORY table.
  9. Execute.
  10. Duration: 2 hours 2 minutes.
    Query:

SELECT TinaR_20170222.SKU ID

, INVENTORY.QTY_ROLL_YTD_SALES_NET_BALANCE

FROM

Local Storage Connection.ToadMain.TinaR_20170222 TinaR_20170222

INNER JOIN DWH_LIVE_SREDWH_OC.DW_ORDER.INVENTORY INVENTORY

ON (TinaR_20170222.SKU ID = INVENTORY.PART_NUMBER)

I grew curious while the query was processing so I tried the same steps with Hyperion; duration: less than 2 minutes.

How can this task be completed quicker with Toad Data Point 4.0.1.775?

2

Hi InfoMan,

Thank you for sharing your scenario with us.

Generally speaking, the cross-query solutions will always take longer time comparing to a solution with the same provider type (eg. source and destination = oracle). Have you tried to import the inventory table into oracle too?

Also could you please be more specific about 4.0.1.775? How much time did it take in this version? Are you sure it’s exactly the same tables and queries?

Could you please provide us with table structure of both oracle and local storage tables including row count in each? I assume the oracle table is much bigger.

Martin

3

as a general rule I stay away from cross connection queries for performance reasons. Most times I can start a cross connection query and while it is running, create a new import wizzard, load the data into the same database as my main query, run the sql and get the final results before the cross connection query is half way done.

My advice, get access to a database on the same server as your main query. Cross database connections fly (same database connections also). If your DBA does not want to give you write access to the main database have them create a new database for you on the same server. Stay away from cross server connections and using linked servers in a join clause, they are slow too. Better to pull what you need from a linked server into a temp table and then join to the temp table (sound like it would take longer but most often is faster).