Make slow query faster through joins

[Not Me]

Hi,

In the past I seem to have been able to speed up queries by making them a
join rather than ... not a join :o)

An example would be

select * from x where a in (select a from y)

would be something like... select x.* from x inner join y on x.a = y.a

Now I have a more complicated query, which seems to drag for hours, and I
wondered if it could be made faster by the use of joins.

SELECT *
FROM addresses
WHERE [conditionX] = true
and personref not in (select personref from addresses where [conditionY] =
true);

The personref is not unique! So I want addresses where conditionX is true,
and the refno has no other addresses associated that have conditionY true.

Thanks in advance
Chris

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Query speed is dependent on various things: indexes on criteria
columns, number of rows in tables, number of columns in tables.

Using a join sometimes can increase the speed of a query, sometimes
NOT. Experimentation can be instructive.

Your query doesn't need joins it just needs a better WHERE clause:

SELECT *
FROM addresses
WHERE conditionX = true
AND conditionY = False
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6FlR4echKqOuFEgEQK9hACgg2aVjXTFsK0742xmjLlDlh n47hkAn0q9
v6nJVMY5+B8L0K1h1u/ye3LF
=nmmW
-----END PGP SIGNATURE-----
Not Me wrote:

Hi,

In the past I seem to have been able to speed up queries by making them a
join rather than ... not a join :o)

An example would be

select * from x where a in (select a from y)

would be something like... select x.* from x inner join y on x.a = y.a

Now I have a more complicated query, which seems to drag for hours, and I
wondered if it could be made faster by the use of joins.

SELECT *
FROM addresses
WHERE [conditionX] = true
and personref not in (select personref from addresses where [conditionY] =
true);

The personref is not unique! So I want addresses where conditionX is true,
and the refno has no other addresses associated that have conditionY true.

Thanks in advance
Chris

[MeadeR]

Query performance is an 'experimental' process for the most part. Speed depends on
# of records, indexes, etc.

Perhaps if you create indexes on 'personref and conditionx' as one index and
another on 'personref and conditiony' as another index. Also in the subquery
if you perform a group by - this would require less searching...

"Not Me" <su**@sumwhere.fake> wrote in message news:<bn**********@ucsnew1.ncl.ac.uk>...

Hi,

In the past I seem to have been able to speed up queries by making them a
join rather than ... not a join :o)

An example would be

select * from x where a in (select a from y)

would be something like... select x.* from x inner join y on x.a = y.a

Now I have a more complicated query, which seems to drag for hours, and I
wondered if it could be made faster by the use of joins.

SELECT *
FROM addresses
WHERE [conditionX] = true
and personref not in (select personref from addresses where [conditionY] =
true);

The personref is not unique! So I want addresses where conditionX is true,
and the refno has no other addresses associated that have conditionY true.

Thanks in advance
Chris

[Not Me]

"MGFoster" <[e-mail]> wrote in message
news:0z*****************@newsread4.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query doesn't need joins it just needs a better WHERE clause:

SELECT *
FROM addresses
WHERE conditionX = true
AND conditionY = False

Thanks, but that won't work as it only compares records individually, I want
X to be true on one record and Y false on all other records with the same
refno.

I think grouping might work, as MeadeR suggested.

Thanks again,
Chris