Performance in SQL Server

Question

I have a problem in a Program that runs queries on a mssql 2008 database. I did some research in the query analyzer. There is a strange issue with the following two queries:

SELECT  BOLD_ID ,
        BOLD_TYPE
FROM    ZusatzKlasseBase
WHERE   ( bold_id IN ( SELECT   usereintrag
                       FROM     zusatzfeld
                       WHERE    metazusatzfeld = 5211309
                                AND wertobject = 1298369 )
          AND bold_id IN ( SELECT   usereintrag
                           FROM     zusatzfeld
                           WHERE    metazusatzfeld = 5207783
                                    AND wertdatum = '20130415' )
        )

Runtime vor this query is about one second

SELECT  BOLD_ID ,
        BOLD_TYPE
FROM    ZusatzKlasseBase
WHERE   ( BOLD_TYPE IN ( 336 ) )
        AND ( bold_id IN ( SELECT   usereintrag
                           FROM     zusatzfeld
                           WHERE    metazusatzfeld = 5211309
                                    AND wertobject = 1298369 )
              AND bold_id IN ( SELECT   usereintrag
                               FROM     zusatzfeld
                               WHERE    metazusatzfeld = 5207783
                                        AND wertdatum = '20130415' )
            )

The runtime of this query is one minute.

I have no explanation for this, the field BOLD_TYPE is indexed. Can anyone guess why the more specific query is slower?

Thanks for all answers in advance.

Answer 1

I would change the index on bold_id (I assume there is an index on bold_id) to this:

CREATE INDEX whatever ON dbo.ZusatzKlasseBase(bold_id, bold_type)
  WITH (DROP_EXISTING = ON);

Then I would re-write the query slightly:

SELECT  BOLD_ID,
        BOLD_TYPE
FROM dbo.ZusatzKlasseBase AS b -- always use schema prefix
WHERE
    ( BOLD_TYPE IN ( 336 ) )
    AND EXISTS 
    (
      SELECT 1 FROM dbo.zusatzfeld
      WHERE    metazusatzfeld = 5211309
               AND wertobject = 1298369 
               AND usereintrag = b.bold_id
    )
    AND EXISTS
    ( 
       SELECT 1 FROM dbo.zusatzfeld
       WHERE    metazusatzfeld = 5207783
                AND wertdatum = '20130415' 
                AND usereintrag = b.bold_id
    );