By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Make slow query faster through joins

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----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


Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
"MGFoster" <me@privacy.com> 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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.