psingla (5/6/2014)

---

I have complex view and try to fetch data from it.

In following code query 2 takes 1 sec to execute and query 1 keeps on running for hours.

I tried to reduce the time of query 1 to same as query 2 but no luck.I tried force order,hash join,creating clustered on temp table,type conversion etc.

This is one of the most complicated behavior I have seen in SQL server.any idea why it is happening so

SET statistics io ON

SET TRANSACTION isolation level READ uncommitted

DROP TABLE tempdb..#ps_temp

CREATE TABLE #ps_temp

(

a numeric(13,0),

b numeric(3,0)

)

DECLARE @a numeric(13,0),

@b-2 numeric(3,0)

---fetches only 1 record

INSERT INTO #ps_temp

(a,

b)

SELECT a,

b

FROM tbl_x

WHERE d = 'L140502021514'

SELECT @a = a,

@b-2 = b

FROM #ps_temp

-----------------query 1---------------------------

SELECT t.a,

t.b

FROM #ps_temp d

inner JOIN dbo.vw_complex AS t -- (hash)

ON t.a = d.a

AND t.b = d.b

-- option (force order)

-----------------query 2---------------------------

SELECT t.a,

t.b

FROM #ps_temp d

inner JOIN dbo.vw_complex AS t -- (hash)

ON t.a = @a

AND t.b = d.b