Posted by Erland Sommarskog on 12/28/06 22:55

(StevePBurgess@gmail.com) writes:
> Is this possible in one SQL query. I have tried the following:
>
> SELECT DISTINCT Documents.Document, Documents.Title,Priority
>
> FROM Documents,
> Attachments,Keywords,LinkTableAttachments,LinkTableKeywordLink
>
> WHERE
> ((Documents.Document=LinkTableAttachments.DocumentID and
> LinkTableAttachments.AttachmentID=Attachments.Id) OR
> (Documents.Document=LinkTableKeywordLink.DocumentID and
> LinkTableKeywordLink.KeywordID=Keywords.Id))
>
> AND (Documents.Title Like '%SEARCHSTRING%' OR Documents.Author Like
> '%SEARCHSTRING%' OR Documents.Summary Like '%SEARCHSTRING%' OR
> Keywords.Keyword Like '%SEARCHSTRING%' OR DocType.DocType Like
> '%SEARCHSTRING%' OR Attachments.AttachmentTitle Like '%SEARCHSTRING%'
> OR Attachments.Path like '%SEARCHSTRING%' or Subjects.Subject Like
> '%SEARCHSTRING%')
>
> AND StartDate<=getDate() and ReviewDate>getDate() order by Title
>
>
> but this causes, perhaps understandably, a timeout error.

The most crucial is probably the date conditions. This is a difficult
one there are two dates. But a start at least is make sure that the
query is parameterised, so the last line:

AND StartDate <= @now and ReviewDate > @now order by Title

And with parameter I don't mean that you do "SELECT @now = getdate()"
just before the query. You need it to pass @now as a parameter to a
stored procedure or a parameterised query. Then the optimzer will
look at the value to determine how selective an index might be and
use it. If you use an unknown value, the optimizer will assume that 30%
of the rows will match, which is far too many,


If the search columns should are indexed, you should distinguish between
"starts with" and "contains" for the indexes to be useful. That is, only
include % first the search string, if the user actually wants "conatins",
because indexes are only good for starting values.

Adjust the application to have no timeout (= 0) or a very long timeout,
because in some cases the user may need to do searches that take a very long
time.

Finally, drop the DISTINCT, and use the EXISTS predicate to get from
the subtables. That is more efficient,since there no need to sort
the result to weed out the duplicates.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]