Query performance in Sql Server

Question

I have some tables in database. Table with objects named ObjectTbl and table with types named TypesTbl.

Thats looks like:

ObjectTbl                          TypesTbl

objId    | TypeId | objName |      typeId    | typeName |
--------------------------------   ----------------------
intObjId | typeId | objName |      intTypeId | typeName |

TypeId column in table ObjectTbl is a Foreign Key from TypesTbl. TypesTbl contains ~200 records, ObjectTbl ~1000000

When I execute query:

SELECT * FROM ObjectTbl obj
join TypesTbl t ON t.typeName='Type_Name'
WHERE obj.TypeId=t.typeId and (obj.objName like '%expression%' or obj.objName like   '%expression2%' or obj.objName like '%expression3%')

It's works more than 10 seconds. But when I use:

declare @typeId int

set @typeId=(select typeId from TypesTbl where typeName='Type_Name')

SELECT * FROM ObjectTbl obj
WHERE obj.TypeId=@typeId and (obj.objName like '%expression%' or obj.objName like   '%expression2%' or obj.objName like '%expression3%')

Thats works less than 1 second. Can anybody explain me why so?

What are your indexes? what does your query plan say? These will give you a good clue. — crthompson
– crthompson, Commented Nov 13, 2014 at 5:33
Thank you. In table ObjectTbl I have nonclustered index for columns objectId and TypeId. — Gleb
– Gleb, Commented Nov 13, 2014 at 5:55
But nothing in TypesTbl? Have a look at your query plan, find the places it says nasty things like Table Scan and put an index there. — crthompson
– crthompson, Commented Nov 13, 2014 at 5:58

Vasily · Accepted Answer · 2014-11-13 11:53:37Z

1

enter image description here

SELECT  *
FROM    ObjectTbl obj
        INNER   JOIN TypesTbl t ON obj.TypeId = t.typeId
                           AND t.typeName = 'Type_Name'
WHERE   ( obj.objName LIKE '%expression%'
          OR obj.objName LIKE '%expression2%'
          OR obj.objName LIKE '%expression3%'
        )

answered Nov 13, 2014 at 11:53

Vasily

5,7923 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Magnus Over a year ago

I doubt this will generate a different execution plan.

Vasily Over a year ago

I think that the execution plan will be changed, currently it looks like a cross join, because where condition works after join condition.

Magnus Over a year ago

Yes it looks like a cross join but will most likely be handled as an inner join by the query engine. Look at the following example: s7.postimg.org/s3a6tyad7/sql.png

Gleb Over a year ago

That's doesn't works. It's still executed more than 10 seconds.

Vasily Over a year ago

Try CTE ; WITH t AS ( SELECT * FROM TypesTbl WHERE typeName = 'Type_Name' ) SELECT * FROM ObjectTbl obj INNER JOIN t ON obj.TypeId = t.typeId WHERE ( obj.objName LIKE '%expression%' OR obj.objName LIKE '%expression2%' OR obj.objName LIKE '%expression3%' )

paparazzo · Accepted Answer · 2014-11-13 14:00:01Z

0

The problem is that this is a really big join

SELECT * 
  FROM ObjectTbl obj
  join TypesTbl t 
    ON t.typeName = 'Type_Name'

move conditions up into the join so the query optimizer can filter sooner

SELECT * 
  FROM ObjectTbl obj
  join TypesTbl t 
    on obj.TypeId = t.typeId
   and t.typeName = 'Type_Name'
   and (    obj.objName like '%expression%' 
        or  obj.objName like '%expression2%' 
        or  obj.objName like '%expression3%' )

answered Nov 13, 2014 at 14:00

paparazzo

45.2k24 gold badges110 silver badges180 bronze badges

Comments

Vasily · Accepted Answer · 2014-11-14 09:55:17Z

--try this
--a) variant with CTE

;
WITH    t AS ( SELECT   *
               FROM     TypesTbl
               WHERE    typeName = 'Type_Name'
             )
    SELECT  *
    FROM    ObjectTbl obj
            INNER   JOIN t ON obj.TypeId = t.typeId
    WHERE   ( obj.objName LIKE '%expression%'
              OR obj.objName LIKE '%expression2%'
              OR obj.objName LIKE '%expression3%'
            )

--b) variant with #temp table

IF OBJECT_ID('Tempdb..#t') IS NOT NULL
BEGIN   
    DROP TABLE #t
END 

SELECT  *
INTO    #t
FROM    TypesTbl
WHERE   typeName = 'Type_Name'

SELECT  *
FROM    ObjectTbl obj
        INNER   JOIN #t AS t ON obj.TypeId = t.typeId
WHERE   ( obj.objName LIKE '%expression%'
          OR obj.objName LIKE '%expression2%'
          OR obj.objName LIKE '%expression3%'
        )

--also you can try inner hash join https://ask.sqlservercentral.com/questions/17400/inner-join-vs-inner-hash-join.html

Collectives™ on Stack Overflow

Query performance in Sql Server

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related