SQL Server Query optimisation question

Question

I have the following queries, and I would love to find a better way to do this as it doesn't seem right the way I am doing it...

EDIT

Sorry I did not specify that I only want to return adverts that actually match all amenities!

SELECT TOP 50 Advert.Id
FROM Advert 
WHERE Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 1   AND Advert_Id = Id)
  AND Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 3   AND Advert_Id = Id)
  AND Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 5   AND Advert_Id = Id)  

-- OR --

SELECT TOP 50 Advert.Id
FROM Advert 
JOIN AdvertsToAmenities a on Advert.Id = a.Advert_id
JOIN AdvertsToAmenities b on Advert.Id = b.Advert_id
JOIN AdvertsToAmenities c on Advert.Id = c.Advert_id
WHERE a.Amenity_id = 1 
  AND b.Amenity_id = 3 
  AND c.Amenity_id = 5

I would love to find out how to optimize these queries!

"to do this" - Can you give us a better problem description instead of having everyone reading the question parse the queries? — Oded
– Oded, Commented Apr 19, 2011 at 15:24
It seems that your question is worded differently from your comment about not seeming right. Remember that the execution path that SQL Server derives from your query is highly optimised despite the manner in which you declare the order of sql. It might be worth checking the actual execution plan to determine if there is any need for further optimisation. If you are referring to making the sql more readable / understandable then there's obviously room for improvement. I'll try and come up with an example. — Brian Scott
– Brian Scott, Commented Apr 19, 2011 at 15:27
Also, you may find that decent indexes / keys on your tables will have a much more dramatic "optimisation" outcome than rewording your query. — Brian Scott
– Brian Scott, Commented Apr 19, 2011 at 15:30

Heinzi · Accepted Answer · 2011-04-19 15:27:04Z

3

Your queries look fine to me. Another alternative would be to use something like this:

SELECT TOP 50 Advert.Id
  FROM Advert JOIN AdvertsToAmenities a ON Advert.Id = a.Advert_id
 WHERE a.Amenity_id = 1 
    OR a.Amenity_id = 3 
    OR a.Amenity_id = 5
 GROUP BY Advert.Id
HAVING COUNT(DISTINCT a.Amenity_id) = 3

If the (Advert_Id, Amenity_id) pairs are unique, you can drop the DISTINCT.

answered Apr 19, 2011 at 15:27

Heinzi

173k61 gold badges386 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Scott · Accepted Answer · 2011-04-19 15:35:16Z

0

How about the following restructure of your query which allows you to simply create a comma seperated list of specific amenity IDs?

SELECT TOP 50 
a.[ID]
FROM [Advert] a
INNER JOIN 
(
    SELECT ata.[Advert_ID]
    FROM [AdvertsToAmenities] ata
    WHERE ata.[Amenity_ID] 
    IN (1, 3, 4, 5) -- Your list of amenity IDs
) as ata
ON a.[ID] = ata.[Advert_ID]

This uses a dynamic table statement to prefilter only those applicable amenities before joining back against the list of adverts. Note, Sql Server will heavily optimise the sub query before execution. This should mean that you'll get the benefit of a more maintainable statement with all the optimisations that Sql Server provides.

answered Apr 19, 2011 at 15:35

Brian Scott

9,3697 gold badges50 silver badges70 bronze badges

1 Comment

superlogical Over a year ago

I only want to return adverts where they have all of the amenities in the list. In your query it will return adverts that have at least one of the amenities in the list.

edgarmtze · Accepted Answer · 2011-08-20 17:41:45Z

0

* Try this simple *

SELECT TOP 50 a.Id
FROM Advert a, AdvertsToAmenities b 
WHERE a.Amenity_id in ('1','3','5')
and a.Id = b.Amenity_id

edited Aug 20, 2011 at 17:41

edgarmtze

25.2k82 gold badges248 silver badges393 bronze badges

answered Apr 20, 2011 at 14:29

jack.mike.info

1283 bronze badges

Collectives™ on Stack Overflow

SQL Server Query optimisation question

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related