optimizing sub queries

[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

 

[Dale Fye]

No matter what you do, these Like comparisons will take a lot of time.
However, My initial thought is that it would be much quicker if your two
tables were normalized:

Table1
Part Category Cat_Value Multiplier
ABC 1 123 1.1
ABC 2 XYZ 1.1
ABC 3 456 1.1
A* 1 123 1.5
A* 2 EFG 1.5
A* 3 * 1.5
A?C 1 * 1.9
A?C 2 * 1.9
A?C 3 * 1.9

Table 2
Part Category Cat_Value
AQC 1 123
AQC 2 EFG
AQC 3 654
....

Then, you could write a query that looks similar to the following. This
would work better if you actually change the structure of the two tables, but
you could create union queries to normalize the data before running this
query.

SELECT Query2.*, MIN(Table1.Multiplier) as MinMult
FROM Query2
LEFT JOIN Query1
ON Query2.Part Like Query1.Part
AND Query2.Category = Query2.Category
AND Query2.Cat_Value Like Query1.Cat_Value

You could normalize these tables by writing a union query and then using
that in a maketable query.

Query1:
SELECT Part, 1 as Category, Cat1 as Cat_Value, Multiplier
FROM Table1
UNION ALL
SELECT Part, 2 as Category, Cat2 as Cat_Value, Multiplier
FROM Table1
UNION ALL
SELECT Part, 3 as Category, Cat3 as Cat_Value, Multiplier
FROM Table1

Query2:
SELECT Part, 1 as Category, Cat1 as Cat_Value
FROM Table2
UNION ALL
SELECT Part, 2 as Category, Cat2 as Cat_Value
FROM Table2
UNION ALL
SELECT Part, 3 as Category, Cat3 as Cat_Value
FROM Table2

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Tasha]

Hi Dale,
Good response except I think I confused you a little bit by saying cat1,
cat2, cat3. Probably should have used field1, Field2, field3. These are
unrelated fields. one would be product group, the next could be price group,
the next is repair code. There are about 7 different fields per record that
determine the multiplier or some other value. The logic is most specific to
least specific. If all seven fields match pull multiplier else go to 6
fields, else go to 5 fields,.... The subquery goes pulls all matching records
sorted in descending by most important field to least important field then I
pull the top record since I can only have one multiplier per part and I want
to find the most specific match. Any other suggestions?

TIA,
Tasha

 

[Sylvain Lafontaine]

A good part of the problem might be the fact that you are using an
heterogenous query. You should consider the possibility of copying the
local table on the SQL-Server before making the join (this could be made on
a temporary table if necessary) or to copy the remote table locally.

Because of the Top 1, it's not easy to optimize this subquery. However, on
SQL, you can perform a join between two fields even when using a wild card
but I don't know if this will really help you. You should execute these two
versions on SQL-Server and take a look at the execution plans. Also, you
don't give any hint about the real size of the tables themselves and on the
average number of possible Joins between these two tables; so it's hard to
come out with any real idea about what to do.

You should also take a look with the SQL-Server Profiler to see what's going
on. Knowing that Access frequently retrieves data by group of ten, you
should take a look with a number of records superior to 10.

Finally, have you considered the possibility of storing the result
somewhere?

 

[Tasha]

Thanks Sylvain,

Copying the table to the SQL server isn't an option due to restrictions on
the server (corporate controlled) and appending the returned results to a
standard table would cause issues because multiple users would be trying to
do the same thing with different data. I would expect it to have some
conflicts. However automating the remote tables to append the current data
everytime the user logs on is an idea. It would cause a slow down in the
performance on signon but may help overall. I would have to see the
performance trade off. I would still have to do the subqueries because of the
join issue in Access but it may run quicker. This at least gives me some
ideas.

Thanks much!

PS. Table size is roughly 1000 records with 10 values to look up. 1500
records in each look up table. 8 different lookup tables. Minimum 5 fields to
compare against per lookup table. Application was not originally intended for
this many users with as many locations as they want to expand it to. I have
actually recommended .net front end instead. This is quicker and cheaper to
implement since it is already designed.

 

[Sylvain Lafontaine]

Even in an corporate environment, copying the table to SQL-Server is not
necessary a problem if you can copy it to a temporary table. It's very rare
when the permissions on a sql-server are so restricted that you cannot even
write to the temporary database. Using a standard table would also not be a
problem is you add a field to distinguish between each user or each
connection.

Finally, even if you want to work exclusively locally, you should consider
the possibility of installing SQL-Server Express 2008 onto your user's
machines. Even if SQL-Server is much slower than a standard edition of
SQL-Server because it's limited to a single CPU and one Gig of memory, it's
probably faster than JET at doing this.