Fastest way of searching for things in one of two different tables

Question

Some background - we're in the process of revamping the way that SQL Server Central's search and related content systems work. One part of this is that we need to perform fairly full extractions of the content in the database in order to index it. The version of the search that's currently running in development uses the following query to extract all the articles on the site:

SELECT ci.ContentItemID ,
  ci.Title ,
  ci.Description ,
  ci.Text
FROM dbo.ContentItems ci
INNER JOIN dbo.Articles a ON ci.ContentItemID = a.ContentItemID
WHERE ci.PublishingStatus = 30
-- Ignore some articles with certain tags
AND NOT EXISTS ( SELECT 1
  FROM dbo.TagMappings t
  WHERE t.ContentItemID = ci.ContentItemID
  AND t.TagID IN ( 1781, -- Editorial - IPOD
  1782, -- Editorial - MP3
  1821 ) -- Editorial - WMV
)
-- Ignore redirects
AND ci.ExternalURL IS NULL

We're about to add scripts into the search as well (previously these haven't been particularly well indexed, particularly by Google, so we're looking to improve that significantly). Scripts are also a subclass of ContentItem in the object structure, so are also stored in dbo.ContentItems. The way that we tell that they are scripts is very similar to articles - there is a dbo.Scripts table. If we want to return both articles, and scripts in the same query, what is the fastest way to do that? A couple of left outer joins, with null checks, or using a sub-query?

Answer 1

0

This looks like a matter where to get the best results in that particular case you will need to try it multiple ways and see which gives you the best results.

With that said, it seems like you could do it with two queries with a "Union ALL". UNION, which filters out duplicates tends to be slow, but UNION ALL is quite fast. I previously did some comparison testing and found that in some cases I got faster results using a UNION ALL with two queries than I did using an OR test in the Where clause.

Of course, I also did some other performance tests where the OR was faster depending on the table and the details of the query.

answered Nov 13 '09 at 02:27 PM

TimothyAWiseman
14.3k ● 16 ● 20 ● 29

I would see if I could do the UNION ALL in a sub-query first, and then apply the exclusions to the derived result set, personally...

Nov 13 '09 at 04:45 PM Matt Whitfield ♦♦

(comments are locked)

Fastest way of searching for things in one of two different tables

Follow this question

Related Questions