SQL Server Performance Forum – Threads Archive

Slow query, why?

I have the following query. It takes 20 secs, which is tooooo much: SELECT TOP 1 pa.IdPart
FROM Parts pa
INNER JOIN Projects pr ON pr.IdProject = pa.IdProject — All parts belong to a project
WHERE pr.StartDate IS NOT NULL AND — project started
pr.EndDate IS NULL AND — project not finished
pa.IdGeometry IS NULL AND — no geometry already
pa.IdDifficultyLevel > 1 AND — has to have a difficultyLevel
pa.IdSameAs NOT IN
(
SELECT IdSameAs
FROM Parts
WHERE IdSameAs > 0 AND
IdGeometry > 0 AND
IdSameAs <> 0
)
ORDER BY
pr.Priority desc,
pa.IdPriority asc,
pa.IdDifficultyLevel desc I have made a lot of indexes, but nothing seem to help. I am a newbee, so I do not know what to do. If I delete: IdSameAs NOT IN
(
SELECT IdSameAs
FROM Parts
WHERE IdSameAs > 0 AND
IdGeometry > 0 AND
IdSameAs <> 0
) AND FRom the query it takes less than a sec. If I delete : ORDER BY pr.Priority desc, pa.IdPriority asc, pa.IdDifficultyLevel desc it also takes less than a sec. All my indexes a single column indexes. What am I doing wrong here?

---

Try the not exists over the not in, it can be faster – in the subquery part (now the nto exits, you seem to repeat wheres on the IDSameAs field that are not really necessary – even less so with the not exists, so I took those out. SELECT TOP 1 pa.IdPart
FROM Parts pa
INNER JOIN Projects pr ON pr.IdProject = pa.IdProject — All parts belong to a project
WHERE pr.StartDate IS NOT NULL AND — project started
pr.EndDate IS NULL AND — project not finished
pa.IdGeometry IS NULL AND — no geometry already
pa.IdDifficultyLevel > 1 AND — has to have a difficultyLevel
not exists –pa.IdSameAs NOT IN
(
SELECT 1 FROM Parts p
WHERE p.IdGeometry > 0 AND
p.IDSameAs = pa.IdSameAs
)
ORDER BY
pr.Priority desc,
pa.IdPriority asc,
pa.IdDifficultyLevel desc

---

I have not seen that construct before. It looks excelent. I am not sure it gives me the right thing though. Let me tell you a little about the IdSameAs column: The column contains numbers, If two rows share the same IdSameAs and this number is greater than 0, the rows are connected. If one row contains IdGeometry, I am not able to select the other. But at the same time, I still need to be able select the rows with IdSameAs = 0. If it can be done faster with IdSameAs column 0 = null, then please tell me. I have played a little myself with this problem. If I do like this: CREATE TABLE #tt(IdSameAs BIGINT PRIMARY KEY)
insert into #tt select distinct IdSameAs From Parts WHERE IdGeometry IS NOT NULL
…
…
IdSameAs NOT IN(
SELECT IdSameAs FROM #tt) I get respons times on less than a sec…. I have no clue why this is happening??? It looks like the statistics are not up to date or something… …
…
… When having the IdSameAs = 0 your query gives me the wrong result. I changed the 0s to NULLs instead and then your query takes less than a sec – Amazing!

---

Did you run Reindex or/and update statistics as part of maintenance plan?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I have reindexed all columns in the table. I am not sure I have updated the statistics though.

---

When you reindex all columns in the table, statistics are updated too.
What about all tables?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Yes, actually what I wanted to write was that i reindexed all tables in all database as part of a "please-work-maintanence-plan" – he he. Nonetheless, it seems as if the solution posted by ChrisFretwell works. I just find it extremely strange since the temptable solution works…. it looks like that some sort of statistics are not up 2 date. Another problem could be (I have no clue) is that before this select statement, I make a start transaction (since this select statement is just a subset of what is happening). Perhaps some row locking is happening or something I am unaware of.

---

]]>