Fine tuning this sql-query. help!

[majsen]

Hi, I have problem running this query. It will time out for me...
My database are small just about 200 members.

I have a site for swaping appartments (rental). my query should look
for match
in a triangle. Like this member A -> B->C
A give his appartment to B. B gives his appartment to C and finally
C gives his appartment to A

Soo my query looks for matching parameters like rooms, location, size
and
soo on..

I have one table for existing appartments and one for "whanted
appartments"
and 1 table called "intresse" where members can store "yes" or "no" if
they are interessted in a appartment.

I also have a table called "omrade" to store locations of interest.

Hope you can helpe me with some tip soo i can run this query in a few
seconds instead of 20-30 sec

Thanks M


SELECT

F.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadress
AS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek AS
storlek,
F.hyra AS hyra, count(F.medlemsNr) As hits

FROM

medlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,
lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,
lagenhetO08 I
WHERE

D.rum >= I.rumMin AND D.rum <= I.rumMax AND
D.storlek >= I.storlekMin AND D.storlek <= I.storlekMax AND
I.hyraMax = 0" & " OR D.hyra <= I.hyraMax) AND
I.balkong = '" & "" & "' OR D.balkong = I.balkong) AND
(I.badkar = '" & "" & "' OR D.badkar = I.badkar) AND
(I.bredband = '" & "" & "' OR D.bredband = I.bredband) AND
(I.hiss = '" & "" & "' OR D.hiss = I.hiss) AND
(I.spis = '" & "" & "' OR D.spis = I.spis) AND
(I.brf = '" & "" & "' OR D.brf = I.brf) AND

D.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) AND
F.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=A.medlemsNr) AND

H.rum >= G.rumMin AND H.rum <= G.rumMax AND
H.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND
(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND
(G.balkong = '" & "" & "' OR H.balkong = G.balkong) AND
(G.badkar = '" & "" & "' OR H.badkar = G.badkar) AND
(G.bredband = '" & "" & "' OR H.bredband = G.bredband) AND
(G.spis = '" & "" & "' OR H.spis = G.spis) AND
(G.brf = '" & "" & "' OR H.brf = G.brf) AND

H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) AND

F.rum >= E.rumMin AND F.rum <= E.rumMax AND
F.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND
(E.hyraMax = 0" & " OR F.hyra <= E.hyraMax) AND
(E.balkong = '" & "" & "' OR F.balkong = E.balkong) AND
(E.badkar = '" & "" & "' OR F.badkar = E.badkar) AND
(E.bredband = '" & "" & "' OR F.bredband = E.bredband) AND
(E.hiss = '" & "" & "' OR F.hiss = E.hiss) AND
(E.spis = '" & "" & "' OR F.spis = E.spis) AND
(E.brf = '" & "" & "' OR F.brf = E.brf) AND
F.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) AND

A.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr AND

B.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr AND
B.medlemsNr<>C.medlemsNr AND
B.sparr<>1 AND C.sparr<>1 AND

A.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " AND
B.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr AND
B.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_
C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr AND
C.typ=11 group by F.lfId, F.medlemsNr,F.ort,
F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra

[Erland Sommarskog]

majsen (mi****@home.staertesjoen.se) writes:

Hi, I have problem running this query. It will time out for me...
My database are small just about 200 members.

I have a site for swaping appartments (rental). my query should look
for match
in a triangle. Like this member A -> B->C
A give his appartment to B. B gives his appartment to C and finally
C gives his appartment to A

Soo my query looks for matching parameters like rooms, location, size
and
soo on..

I have one table for existing appartments and one for "whanted
appartments"
and 1 table called "intresse" where members can store "yes" or "no" if
they are interessted in a appartment.

I also have a table called "omrade" to store locations of interest.

Hope you can helpe me with some tip soo i can run this query in a few
seconds instead of 20-30 sec

Without knowledge of the tables, their indexes, and the number of rows
in each table, there is not much advice I can give. There query is complex,
and all the conditions like:

(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND

may make indexes difficult to use anyway. Did I understand that
medlem08 only had 200 rows. How many apartments are involved? And
what is the difference between lagenhetF08 and lagenhet008? (And
why all this 08? Can only Stockholmers apply? :-) And why all these
" & " in the middle of it all? Why they make no damage, the query
was already difficult to read.

Once advice is to use ANSI-join instead of old-stlye joins. This should
have no impact on peformance, but it could make the query easier to
read, since you separate join conditions and filter conditions. An
example of this syntax:
SELECT o.orderid, o.orderdate, od.qty, od.product
FROM orders o
JOIN orderdetails o ON o.orderid = od.orderid
WHERE o.customerid = 12
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mikael Hellström]

Hi,

my tables

lagenhetF08 ->existing appartment 200 posts
lagenhetO08->whanted appartment 200 posts
member08 ->the member 200 posts
Intresse08->intresst yes or no ? posts
omrade08->location 30 + posts

One member can have 1 or 2 appartment and
look for 1 or 2 appartments

about the " & " signs...i forgott to remove them :-)

I have used the tool tracking sql-server and applied
my workload to the index tuning wizard. But it only
slowed down the query even more...

Mabe i could not use selfjoin?
What do u say about my subquerys. Do they slow down
the process?
Regards Mikael


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

mikael Hellström (mi****@home.staertesjoen.se) writes:

lagenhetF08 ->existing appartment 200 posts
lagenhetO08->whanted appartment 200 posts
member08 ->the member 200 posts
Intresse08->intresst yes or no ? posts
omrade08->location 30 + posts
Not an astonish lot. Then again, let's see, you have nine tables
in triplets of three. I have not been able to get through your
query entirely (have you considered rewriting in ANSI joins yet?),
but give the requirements, I guess you will have to cross-join
each triplet with each other. Since:
One member can have 1 or 2 appartment and
look for 1 or 2 appartments
We could assume that each triplet returns, say, 250 rows. That's
250^3 rows, which is over 15 million! Suddently 20-30 seconds starts
to feel quite reasonable....

So what is happening - basically - is that SQL Server first has
to construct all those 15 million rows, and then apply the filter
conditions to remove the pity handful few that matches.

Now, in fact for one of the triplets you are providing a key value,
which should reduces the number of rows to filter to 62500. Then again,
if your site grows, and you day have 5000 users, you have 5000^2 rows.

I know too little about this, to say anything for sure, but it looks
like just submitting a naïve query is not likely to cut it. You may
have to compute matches in advance.
What do u say about my subquerys. Do they slow down
the process?

They are certainly not speed boosters...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mikael Hellström]

Hi again,

Here are my query rewrited...

this is what my querty look like when sql-server rewrite
my question on the new ANSI standard...
It look insane. I now can se the amount of timeconsuming
operations it have to execute to answer my question.
It work all right. This query deliver 1 (one)
matching result :-)
it takes about 30 sec + to execute.

My question are ... How can i rewrite the question
to improve performence.

I have tried to split the question into 3 parts without
any sucesss.

Regards Mikael

'--------------------------------------------------------
SELECT F.medlemsNr AS medlemsNr, F.lfId AS lfId, F.ort
AS ort, F.gatuAdress AS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,
F.storlek AS storlek,
F.hyra AS hyra, COUNT(F.medlemsNr) AS hits

FROM lagenhetF08 H INNER JOIN
lagenhetO08 G ON H.rum >= G.rumMin AND H.rum <= G.rumMax AND H.storlek

= G.storlekMin AND H.storlek <= G.storlekMax INNER JOIN lagenhetF08 F INNER JOIN
lagenhetO08 E ON F.rum >= E.rumMin AND F.rum <= E.rumMax AND F.storlek= E.storlekMin AND F.storlek <= E.storlekMax INNER JOIN

medlem08 A INNER JOIN
lagenhetO08 I INNER JOIN
lagenhetF08 D ON I.rumMin <= D.rum AND I.rumMax >= D.rum AND
I.storlekMin <= D.storlek AND I.storlekMax >= D.storlek ON
A.medlemsNr = D.medlemsNr ON E.medlemsNr = A.medlemsNr INNER JOIN
medlem08 B ON A.medlemsNr <> B.medlemsNr AND F.medlemsNr = B.medlemsNr
INNER JOIN
medlem08 C ON A.medlemsNr <> C.medlemsNr AND B.medlemsNr <> C.medlemsNr
AND I.medlemsNr = C.medlemsNr ON
G.medlemsNr = B.medlemsNr AND H.medlemsNr = C.medlemsNr
WHERE (I.hyraMax = 0) AND (I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.hyraMax = 0) AND (G.balkong
= ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.hyraMax = 0) AND (E.balkong = ' '
OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) OR
(I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.hyraMax = 0) AND (G.balkong
= ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.hyraMax = 0) AND (E.balkong = ' '
OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (D.hyra
<= I.hyraMax) OR
(I.hyraMax = 0) AND (I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.balkong = ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.hyraMax = 0) AND (E.balkong = ' '
OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (H.hyra
<= G.hyraMax) OR
(I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.balkong = ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.hyraMax = 0) AND (E.balkong = ' '
OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (D.hyra
<= I.hyraMax) AND (H.hyra <= G.hyraMax) OR
(I.hyraMax = 0) AND (I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.hyraMax = 0) AND (G.balkong
= ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.balkong = ' ' OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (F.hyra
<= E.hyraMax) OR
(I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.hyraMax = 0) AND (G.balkong
= ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.balkong = ' ' OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (D.hyra
<= I.hyraMax) AND (F.hyra <= E.hyraMax) OR
(I.hyraMax = 0) AND (I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.balkong = ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.balkong = ' ' OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (H.hyra
<= G.hyraMax) AND (F.hyra <= E.hyraMax) OR
(I.balkong = ' ' OR
D.balkong = I.balkong) AND (I.badkar = ' ' OR
D.badkar = I.badkar) AND (I.bredband = ' ' OR
D.bredband = I.bredband) AND (I.hiss = ' ' OR
D.hiss = I.hiss) AND (I.spis = ' ' OR
D.spis = I.spis) AND (I.brf = ' ' OR
D.brf = I.brf) AND (D.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = I.loId)) AND (F.medlemsNr NOT IN
(SELECT medlemsNr2
FROM INTRESSE08
WHERE medlemsNr1 = A.medlemsNr)) AND (G.balkong = ' ' OR
H.balkong = G.balkong) AND (G.badkar = ' ' OR
H.badkar = G.badkar) AND (G.bredband = ' ' OR
H.bredband = G.bredband) AND (G.hiss = ' ' OR
H.hiss = G.hiss) AND (G.spis = ' ' OR
H.spis = G.spis) AND (G.brf = ' ' OR
H.brf = G.brf) AND (H.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = G.loId)) AND (E.balkong = ' ' OR
F.balkong = E.balkong) AND (E.badkar = ' ' OR
F.badkar = E.badkar) AND (E.bredband = ' ' OR
F.bredband = E.bredband) AND (E.hiss = ' ' OR
F.hiss = E.hiss) AND (E.spis = ' ' OR
F.spis = E.spis) AND (E.brf = ' ' OR
F.brf = E.brf) AND (F.postNr IN
(SELECT postNr
FROM ONSKEMAL08
WHERE loId = E.loId)) AND (B.sparr <> 1) AND (C.sparr <> 1) AND
(A.typ = 11) AND (A.medlemsNr = '100255') AND (B.typ = 11) AND
(A.triangel = 1)
AND (B.triangel = 1) AND (C.triangel = 1) AND (C.typ = 11) AND (D.hyra
<= I.hyraMax) AND (H.hyra <= G.hyraMax) AND (F.hyra <= E.hyraMax)
GROUP BY F.lfId, F.medlemsNr, F.ort, F.gatuAdress, F.gatuNr, F.rum,
F.storlek, F.hyra

'-------------------------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

mikael Hellström (mi****@home.staertesjoen.se) writes:

Here are my query rewrited...

this is what my querty look like when sql-server rewrite
my question on the new ANSI standard...
It look insane. I now can se the amount of timeconsuming
operations it have to execute to answer my question.
It work all right. This query deliver 1 (one)
matching result :-)
it takes about 30 sec + to execute.

My question are ... How can i rewrite the question
to improve performence.

I have tried to split the question into 3 parts without
any sucesss.

I don't know what happened during that rewrite, but I had to give up going
through the query. Your original query was some 50-60 lines, but this
one is over 300 lines. I think managed to include some duplicate conditions.

Rewriting an old-style join into ANSI join takes some exercice. Conditions
like A.MedlemsID <> B.MedlemsID are not really join conditions, but rather
filter. I think the right answer is that you don't really have any join
conditions between the members - it's a cross join.

I'm a afraid that whole thing is too complex to cover in a newsgroup post.
To fully attack it, I would have to have the CREATE TABLE statements
for the tables, as well as sample data in some form. I have to admit that
at this point I don't much idea of where to start. I can see what the
problem comes from - all the cross joins. But I know too little about
the tables and the business rules to say what to do about it.

Maybe some partial attack is good. If I understood things right, one
member may have one or two apartments. Maybe it's a good idea, to find
the inquiring member's apartment, get that data, and insert that into
the query. That does at least reduce the complexity of the query.

It might even be that you should first match that member against the rest
for possible matches, and then take those matches to a triangle test.

And, no, this is by no means a trivial problem, but appears to be a
tough challenge even for a seasoned SQL programmer.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mikael Hellström]

Hi again,
and thank you for looking into my problem...

I have thought of 2 things to do

1) mabe i could skip the table 'omrade' and store
the data in lagenhetO08 (appartment whanted). This
should speed up the query but it would not be
a very good databas design thou. It would be
a nvarchar string 120, 121 and so on... And i will
use 'Like' to search thru the string for match.
Seems wierd...but it may speed up the process?

2) I have tried to split my query (as you se below) into
tre parts. The first 2 question run very fast...1-3 sec
but the last query slowes it down to 30 sec...again.

first query: who whant my appartment (who match)?
second query: witch apartment do i want? (who match)?
third query: search matches from result 1 and 2

This should end up in a triangle match.

i create two new tables to store the result
the key 'medlemsNr'. I thought to use not permanent
tables if this would work. Just session tables.

Soo...my problem is ... can i speed up query 3 in some way?
Should i change my database design? And skip the table
Omrade?
Regards Mikael Hellström

-- Hämta medlemmar som är intresserade av min lägenhet--

strSQL = SELECT H.medlemsNr as medlemsNr
INTO INTRESSERADE_AV_MIN
FROM medlem08 A, medlem08 C, lagenhetF08 D, lagenhetO08 I , lagenhetF08
H

WHERE
D.rum >= I.rumMin AND D.rum <= I.rumMax AND
D.storlek >= I.storlekMin AND D.storlek <= I.storlekMax AND
(I.hyraMax = 0 OR D.hyra <= I.hyraMax) AND
(I.balkong = ' ' OR D.balkong = I.balkong) AND
(I.badkar = ' ' OR D.badkar = I.badkar) AND
(I.bredband = ' ' OR D.bredband = I.bredband) AND
(I.hiss = ' ' OR D.hiss = I.hiss) AND
(I.spis = ' ' OR D.spis = I.spis) AND
(I.brf = ' ' OR D.brf = I.brf) AND
D.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) AND
I.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=A.medlemsNr) AND
A.medlemsNr=100255 AND C.triangel=1 AND A.medlemsNr=D.medlemsNr AND
H.medlemsNr=C.medlemsNr AND C.medlemsNr=I.medlemsNr AND
C.typ=11 order by medlemsNr
'- Hämta medlemmar som jag är intresserad av -----

strSQL2 = SELECT G.medlemsNr as medlemsNr
INTO INTRESSE_FOR_MIG
FROM medlem08 A, medlem08 B, lagenhetF08 F, lagenhetO08 G , lagenhetO08
E

WHERE
F.rum >= E.rumMin AND F.rum <= E.rumMax AND
F.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND
(E.hyraMax = 0 OR F.hyra <= E.hyraMax) AND
(E.balkong = ' ' OR F.balkong = E.balkong) AND
(E.badkar = ' ' OR F.badkar = E.badkar) AND
(E.bredband = ' ' OR F.bredband = E.bredband) AND
(E.hiss = ' ' OR F.hiss = E.hiss) AND
(E.spis = ' ' OR F.spis = E.spis) AND
(E.brf = ' ' OR F.brf = E.brf) AND
F.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) AND
E.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=A.medlemsNr) AND
A.medlemsNr=100255 AND B.triangel=1 AND A.medlemsNr=E.medlemsNr AND
G.medlemsNr=B.medlemsNr AND B.medlemsNr=F.medlemsNr AND
B.typ=11 order by medlemsNr

'-- Är de intresserade av varandras objekt ---------------

strSQL3 = SELECT DISTINCT G.medlemsNr as medlemsNr

FROM medlem08 B, medlem08 C, lagenhetF08 H, lagenhetO08 G, lagenhetF08
F,
INTRESSE_FOR_MIG M, INTRESSERADE_AV_MIN J
WHERE
H.rum >= G.rumMin AND H.rum <= G.rumMax AND
H.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND
(G.hyraMax = 0 OR H.hyra <= G.hyraMax) AND
(G.balkong = ' ' OR H.balkong = G.balkong) AND
(G.badkar = ' ' OR H.badkar = G.badkar) AND
(G.bredband = ' ' OR H.bredband = G.bredband) AND
(G.hiss = ' ' OR H.hiss = G.hiss) AND
(G.spis = ' ' OR H.spis = G.spis) AND
(G.brf = ' ' OR H.brf = G.brf) AND
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) AND
G.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=B.medlemsNr) AND
B.medlemsNr=G.medlemsNr AND C.medlemsNr=H.medlemsNr AND
F.medlemsNr=G.medlemsNr AND H.medlemsNr=C.medlemsNr AND B.triangel=1 AND
G.medlemsNr=J.medlemsNr AND
H.medlemsNr=M.medlemsNr AND B.typ=11 AND C.typ=11
order by medlemsNr

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

mikael Hellström (mi****@home.staertesjoen.se) writes:

Soo...my problem is ... can i speed up query 3 in some way?
Should i change my database design? And skip the table
Omrade?
Changing the database design is indeed an option worth considering.
Just please don't ask me how. As I've said, this issue is a little
too complex to be easily handled in a newsgroup post, since it requires
detailed knowledge about the business problem.

But would you could try is to take out the subqueries and the DISTINCT
from that last query, only see the effect.

Still, though, your query:
strSQL3 = SELECT DISTINCT G.medlemsNr as medlemsNr

FROM medlem08 B, medlem08 C, lagenhetF08 H, lagenhetO08 G, lagenhetF08
F,
INTRESSE_FOR_MIG M, INTRESSERADE_AV_MIN J
WHERE
H.rum >= G.rumMin AND H.rum <= G.rumMax AND
H.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND
(G.hyraMax = 0 OR H.hyra <= G.hyraMax) AND
(G.balkong = ' ' OR H.balkong = G.balkong) AND
(G.badkar = ' ' OR H.badkar = G.badkar) AND
(G.bredband = ' ' OR H.bredband = G.bredband) AND
(G.hiss = ' ' OR H.hiss = G.hiss) AND
(G.spis = ' ' OR H.spis = G.spis) AND
(G.brf = ' ' OR H.brf = G.brf) AND
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) AND
G.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=B.medlemsNr) AND
B.medlemsNr=G.medlemsNr AND C.medlemsNr=H.medlemsNr AND
F.medlemsNr=G.medlemsNr AND H.medlemsNr=C.medlemsNr AND B.triangel=1 AND
G.medlemsNr=J.medlemsNr AND
H.medlemsNr=M.medlemsNr AND B.typ=11 AND C.typ=11
order by medlemsNr

Is effectively a cross join between B-G-F-J and C-H-M, which means
that there are many combiantions that SQL Server will have to consider.

In the above, only F, M and J only contributes with MedlemsID. It could
be worth to test by moving them into EXISTS clauses, as demonstrated

strSQL3 = SELECT DISTINCT G.medlemsNr as medlemsNr

FROM medlem08 B, medlem08 C, lagenhetF08 H, lagenhetO08 G, lagenhetF08
F,
INTRESSE_FOR_MIG M
WHERE
H.rum >= G.rumMin AND H.rum <= G.rumMax AND
H.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND
(G.hyraMax = 0 OR H.hyra <= G.hyraMax) AND
(G.balkong = ' ' OR H.balkong = G.balkong) AND
(G.badkar = ' ' OR H.badkar = G.badkar) AND
(G.bredband = ' ' OR H.bredband = G.bredband) AND
(G.hiss = ' ' OR H.hiss = G.hiss) AND
(G.spis = ' ' OR H.spis = G.spis) AND
(G.brf = ' ' OR H.brf = G.brf) AND
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) AND
G.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where
medlemsNr1=B.medlemsNr) AND
B.medlemsNr=G.medlemsNr AND C.medlemsNr=H.medlemsNr AND
F.medlemsNr=G.medlemsNr AND H.medlemsNr=C.medlemsNr AND B.triangel=1 AND
H.medlemsNr=M.medlemsNr AND B.typ=11 AND C.typ=11
AND EXISTS (SELECT *
FROM INTRESSERADE_AV_MIN J
WHERE J.MedlemsID = G.MedlemsID)
order by medlemsNr

You might even go as far as trying this with all tables but G and H.

No promises for success, though.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[mikael Hellström]

Hi,
i start to get somewere now.

I replaced all sub querys

from
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId)

to
"H.postNr = L.postNr AND L.loId=G.loId
And i replaced

G.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 where

medlemsNr1=B.medlemsNr)

to

not exists(select medlemsNr2 from INTRESSE08 where medlemsNr1=" &
session("medlemsNr") & " AND medlemsNr2=F.medlemsNr AND bytestyp='T')
That change and the run of 'index tuning wisard' make
the query execute in 1-2 seconds compare to 30 sec.

Cool...so the strange thing now is that my query is
very slow 10 sec + at my webbhotel. That must have to do
with the fine tuning on my index that i perform on my
local server and i will do that at my webbhotell to and
it should work out fine.

I would like to thank you for taking time to help me
with this problem.

If you would like to swap your appartment, give me a call,
i will add you to my memberlist for free :-)
Thanks again
Regards Mikael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

mikael Hellström (mi****@home.staertesjoen.se) writes:

from
H.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId)

to
"H.postNr = L.postNr AND L.loId=G.loId
Hm, this could possibly generate duplicates in the output. But you
could address that with a DISTINCT before the column list.
That change and the run of 'index tuning wisard' make
the query execute in 1-2 seconds compare to 30 sec.

Great to hear that you got working better!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp