T
Tasha
Is there a way to optimize a query that uses subqueries? Here is the situation
table 1 lookup table
Part cat1 cat2 cat3 multiplier
ABC 123 XYZ 456 1.1
A* 123 EFG * 1.5
A?C * * * 1.9
Table 2
Part Cat1 Cat2 Cat3
AQC 123 EFG 654
ABC 123 XYZ 456
AFC 549 WXG 325
I want to look up the most specific multiplier. For part AQC, I want 1.5.
For part ABC, I want 1.1. For AFC, I want 1.9.
Here is my query:
Select *, (Select top 1 Multiplier from (Select multiplier from Table 1
where Table2.part like Table1.part and Table2.Cat1 like Table1.Cat1 and
Table2.Cat2 like Table1.Cat2 and Table2.Cat3 like Table1.Cat3 Order by
table1.part desc, table1.cat1 desc, table1.cat2 desc, table1.cat3 desc)) AS
Mulitplier from Table 2.
I can't do a join on the fields between table 1 and table 2 due to the wild
cards. The query works fine. The issue is performance. Multiply the number of
lookups to 10 and the number of records in table 2 by 1000 and the query
performs extremely slowly. Also Table 2 is a local table. Table 1 is stored
on an SQL server. This application retrieves data from 1 system, applies
derivation and returns results to input into another system. It has about 50
users. Each user can request a different set of data from the external system
(data is inputed into table 2 for local use) but all users need to apply the
same derivation (hence why table 1 is SQL server table). They are wanting to
expand the app but I am concerned due to this performance issue. Any ideas on
how to optimize this to improve performance? Thought about stored procedure
or pass through query but not sure how since table 2 is local.
TIA
Tasha
table 1 lookup table
Part cat1 cat2 cat3 multiplier
ABC 123 XYZ 456 1.1
A* 123 EFG * 1.5
A?C * * * 1.9
Table 2
Part Cat1 Cat2 Cat3
AQC 123 EFG 654
ABC 123 XYZ 456
AFC 549 WXG 325
I want to look up the most specific multiplier. For part AQC, I want 1.5.
For part ABC, I want 1.1. For AFC, I want 1.9.
Here is my query:
Select *, (Select top 1 Multiplier from (Select multiplier from Table 1
where Table2.part like Table1.part and Table2.Cat1 like Table1.Cat1 and
Table2.Cat2 like Table1.Cat2 and Table2.Cat3 like Table1.Cat3 Order by
table1.part desc, table1.cat1 desc, table1.cat2 desc, table1.cat3 desc)) AS
Mulitplier from Table 2.
I can't do a join on the fields between table 1 and table 2 due to the wild
cards. The query works fine. The issue is performance. Multiply the number of
lookups to 10 and the number of records in table 2 by 1000 and the query
performs extremely slowly. Also Table 2 is a local table. Table 1 is stored
on an SQL server. This application retrieves data from 1 system, applies
derivation and returns results to input into another system. It has about 50
users. Each user can request a different set of data from the external system
(data is inputed into table 2 for local use) but all users need to apply the
same derivation (hence why table 1 is SQL server table). They are wanting to
expand the app but I am concerned due to this performance issue. Any ideas on
how to optimize this to improve performance? Thought about stored procedure
or pass through query but not sure how since table 2 is local.
TIA
Tasha