MsSQL 2005 query performance

Posted by Max on Stack Overflow See other posts from Stack Overflow or by Max
Published on 2010-04-27T10:32:05Z Indexed on 2010/04/27 10:33 UTC
Read the original article Hit count: 370

I have the following query:

select .............    
from 
    //one table and about 20 left joins//
where
    (
        (
            this_.driverName like 'blah*' 
            or this_.renterName like 'blah*'
        ) 
        or exists (
            select
                this0__.id as y0_ 
            from
                ThirdParty this0__ 
            where
                this0__.name like 'blah*'
                and this0__.claim_id=this_.id
        )
    ) 
order by
    this_.id asc

And I have two environment: One with 175 000 records in table "this_" and second with 25 000 records in table "this_".

This query works right on 175k database and it works smth about 2 seconds, but on base with 25k this query freezes.

and if drop one the folloing item from where clause:

(
        this_.driverName like 'blah*' 
        or this_.renterName like 'blah*'
) 

or

exists (
        select
            this0__.id as y0_ 
        from
            ThirdParty this0__ 
        where
            this0__.name like 'blah*'
            and this0__.claim_id=this_.id
)

query runs normally.

How can I to increase performance of this query?

© Stack Overflow or respective owner

Related posts about query-performance

Related posts about sql-server-2005