Re: How to make it faster?

hugoj79 wrote:

> I have two tables containing varchar fields:
>
> tableA tableB
> A1 B1
> A2 B2
> subA1
> subA2
>
> I have a query with a correct function but it's too slow:
>
> SELECT B1, B2 FROM tableB
> WHERE B1 + B2 IN(
> SELECT DISTINCT subA1 + subA2 FROM tableA
> WHERE A1 + A2 not IN(
> SELECT B1 + B2 FROM tableB
> )
> )
>
> I have to concat two fields, because they are composed primary key.
> The problem is: This query is very slow (tableA is very big).
>
> When I run the query in Query Analyser i'ts fast, but when i have it in
> my stored procedure and run the stored procedure from Query Analyser,
> i'ts slow. (it becomes fast when I exclude B2, subA2 and A2 from the
> query so i'ts the combination of the two fields that makes it slow)
>
> I've tried different ways to make it faster. By replace the inner IN(
> function by a JOIN-function, it becomes 10% faster, but not fast enough.
> You can't replace both IN( functions with JOIN right?
>
> Best regards!
>
> --
> Posted via http://dbforums.com

Query Analyser? What product are you using? Oracle is NOT SQL Server.

But specifically ... no one can help you and here's why.

1. No platform and operating system
2. No product versions and editions
3. No SQL
4. No information on indexes.
5. If this is Oracle ... no explain plan

But most importantly ... what does "slow" mean? Slow is dependent on number of rows, number of transactions, and numerous other factors.

Daniel Morgan Received on Sat Mar 01 2003 - 16:21:41 CET