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.medlems Nr) 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!