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

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 ,
FROM dbo.ContentItems ci
INNER JOIN dbo.Articles a ON ci.ContentItemID = a.ContentItemID
WHERE ci.PublishingStatus = 30
-- Ignore some articles with certain tags
  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?

more ▼

asked Nov 13, 2009 at 01:26 PM in Default

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 41 26

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 13, 2009 at 02:27 PM

avatar image

15.6k 22 57 38

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, 2009 at 04:45 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 13, 2009 at 01:26 PM

Seen: 1114 times

Last Updated: Nov 13, 2009 at 01:26 PM

Copyright 2018 Redgate Software. Privacy Policy