Search code examples
sqlsql-optimization

Optimization in sql server query

My Below query in my stored procedure takes more time.Is there any way to optimize this below query.Input parameters are Name and CA.

SELECT @E_ID=vb.e_id
FROM VALUES va, mappings im, VALUES va2
WHERE im.pv_name=vb.value
AND va.E_ID in (select e_id from listings where id = 200)  
AND va.f_id=100
AND va2.f_id=101 AND va.e_id=va2.e_id
AND im.w_i_name='"'+@NAME+'"' AND va2.value='["'+@CA+'"]'
ORDER BY va.e_id
Solution

  • We see now that contrary to the popular opinion, IN / EXISTS queries are not less efficient than a JOIN query in SQL Server. But JOIN queries are less efficient on non-indexed tables. I'm not sure, but you can modify as following this:

    SELECT @E_ID=vb.e_id
FROM VALUES va, mappings im, VALUES va2
join (select e_id from listings where id = 200) tmp on tmp.e_id = va.E_ID
WHERE im.pv_name=vb.value