|
|
Posted by StevePBurgess on 12/29/06 12:45
Hi - and thanks for the feedback.
If I run the query with against just one of the 1-many-many-1 table
relationships it works perfectly. The issue, I think, is having two
OR-ed together. When running against one set (e.g. just keywords and
not attachments) the search is very quick.
Best wishes,
Steve
Erland Sommarskog wrote:
>
> 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
Navigation:
[Reply to this message]
|