Retrieve Data from Another Table That Has No Composite Key to Join to

I have the following query shown below which runs fine the way it is currently written. I am trying to make a modification to this query to pull the Base_Amount, System info and Period columns from the GLTRANS table. What I did was join the GLAMOUNTS table to the GLTRANS table by Acct_Unit and Account (inner join) as there is no real composite key to join to other than these two. When I do this the query takes forever because the GLTRANS table is a historical table with many rows. I think it is cycling through this entire table and the query seems to go on forever. I am not sure how to structure the query so that I don't compromise the efficiency of the eixisting query by trying to include these additional columns from the GLTRANS table.

Any suggestions on how to go about this. Can this be accomplished with SQL?

Apparently, then, you have the "multiple rows, different (base_amount, system_info, period)" situation which is a many-to-many relation, as you noted.
I'm intrigued by your additional requirement description: "In order to calculate the additions to PPE I need to restrict it to certain system codes only and adding the base amount for these transactions would yeild the net additions to PPE for the month."
This makes me think that there are filter predicates to be applied to the GLTRANS table ("certain system codes only") and an aggregation function to be applied as well ("adding the base amount for these transactions would yield the net additions to PPE for the month").
When you apply the filter predicate, you reduce the rows to be included, scanned, etc.
When you apply the aggregation function, you reduce the rows to one.
So -- (this is the "Aha!" moment) -- you can convert your "multiple rows, different(base_amount, system_info, period)" to the nearly optimal "one row per (acct_unit, account)" by filtering and aggregating the table. For example:

select acct_unit, account,
sum(base_amount) as net_additions
from GLTRANS
where system_info in
(... list of certain system codes...)
group by acct_unit, account

You may have to add a predicate on "period" to limit it to periods that are "for the month".

This is a non-correlated subquery that reduces the GLTRANS table to one summarized value for each (acct_unit, account).
This again eliminates the slow-moving correlated subquery, replacing it with a join on the subset that is defined by the subquery.
Does this help?
--bryan

There are many options, depending on which of these conditions apply to the GLTRANS table.

One row per (acct_unit, account):
If you have only one row in GLTRANS for each (acct_unit, account) pair, there is not much you can do other than to build an index on GLTRANS (acct_unit, account). That would replace the table scan that is probably being done on GLTRANS for each qualified GLAMOUNTS row.

Multiple rows per (acct_unit, account):
Do you have multiple rows in GLTRANS for the same (acct_unit, account) pair? If you do, then you will be generating more rows in the result set, compared to the original query. Is that what you want to do? Multiple rows, different (base_amount, system_info, period):If you have multiple rows for the same (acct_unit, account) pair, and they have different (Base_Amount, System info, Period) values, there is not much you can do to keep the number of rows the same. If that's the case, and that's a correct case, then your only real option is to add indexes to the GLTRANS table.

Multiple rows, same (base_amount, system_info, period):
If you have multiple rows for the same (acct_unit, account) pair, do they all have the same (Base_Amount, System info, Period) value? If that is the case, then you should reduce the GLTRANS table to a single row per (acct_unit, account, Base_Amount, System info, Period) tuple before you do the join with the GLAMOUNTS table. This will reduce the number of rows in the inner table, reducing the number of rows involved in matching to the outer table.
You can do this reduction either to a temp table or in a non-correlated subquery, using a DISTINCT select. The temp table might be constructed to have key columns which would speed up the join.
The non-correlated subquery will not have an index (obviously). My hope (without any basis) is that the reduced set of distinct GLTRANS rows will be small enough to fit into memory. An in-memory table will improve the join performance significantly. You might want to do a study of GLTRANS to get a count of the distinct (acct_unit, account) pairs. A count under, say, 10,000 or even 100,000 might perform quite well, depending on how much memory resource you have available to you. You can experiment with the subquery method to see roughly where the breakeven point is between using a subquery and using a temp table.

Thanks Bryan for the clarification. What I have in the GLTRANS table is multiple accounting units and accounts with different base amounts, system code, and period. I need these three columns in my query. I have only read access to these tables and trying to index the GLTRANS table is something I can't do. I don't know if joining the GLAMOUNTS table to the GLTRANS table by accounting unit and account would yeild correct data being tranferred due to the many to many relationship between these two tables.
I can try to see if I can write a correlated subquery to bring in these three fields. Like you mentioned I need to confirm that there aren't too many records being retrieved from the GLTRANS table creating additional overhead.
I am creating the statement of cash flow in Crystal. In order to calculate the additions to PPE I need to restrict it to certain system codes only and adding the base amount for these transactions would yeild the net additions to PPE for the month.
I will keep toying with it to see what I can come up with.
Thanks,
Imty.

Thanks Bryan, I can do the aggregation on the GLTRANS table as you have suggested but the question is can I then join this aggregated table to the GLAMOUNTS table by ACCT_UNIT AND ACCOUNT. I cannot use the GLTRANS table by itself, I need to somehow join the aggregated table to the GLAMOUNTS table because in my report I am using fields from the GLAMOUNTS table.
I have not tried this as yet, but do you think this would work since I still have many ACCT_UNIT'S AND ACCOUNT combinations on the GLAMOUNTS side but aggregated totals by ACCT_UNIT /ACCOUNT combination on the aggregated table.

Put the query inside the parentheses, where "...your subquery..." is. The "SQ" after the closing parenthesis provides a correlation name (an alias) for the subquery -- therefore, it is treated as a (virtual) table, created at execution time, named "SQ", with the column names that are in the column select list (acct_unit, account, net_additions).

The subquery will produce only one row for each (acct_unit, account) pair, because of the GROUP BY clause.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.