Re: How to make it faster?

From: nobody <nobody_at_nowhere.com>
Date: Sat, 01 Mar 2003 18:26:53 GMT
Message-ID: <NX68a.24929$em1.199_at_news04.bloor.is.net.cable.rogers.com>


looks like a minus to be

You want the data that A that not in table B

Select b1,b2 from table a
minus
select b1,b2 from table b

"hugoj79" <member25771_at_dbforums.com> wrote in message news:2588288.1046449177_at_dbforums.com...
>
> 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
Received on Sat Mar 01 2003 - 19:26:53 CET

Original text of this message