subquery X temp table X dynamic sql X table valued function

Using sql-server: subquery X temp table X dynamic sql X table valued function on newest questions tagged sql-server – Stack Overflow

I have a view that summarizes a lot of info for my items. Let’s call it v_item_details.

This view is defined via recursive cte’s, and can become very slow if the right filtering is not applied. Basically, if I provide a list of item id’s, things run smoothly. So something like SELECT * FROM v_item_details WHERE item_id IN(1,2,3) runs in under 1 second.

Things get complicated when I try to fetch the item details for a group of items that is defined in another table. The query SELECT * FROM v_item_details WHERE item_id IN (SELECT item_id FROM items WHERE group_id = 1) will take over 1 minute, even though group_id is indexed and the subquery returns the same item_id’s as the previously query (1,2 and 3).

I tried to create a table variable and insert the results of the subquery in it, and then do a join on that, but it still took over 1 minute.

Next I tried to wrap my view with a table valued function that receives the item_id as the parameter and then do a cross apply, to try and force the execution of the same plan as the simple select, but that took over 4 minutes!

Right now I’m using a dynamic query that selects the ids and then executes the fast subquery. But that’s suboptimal, because now I’m stuck with stored procedures, and not views that I could manipulate further.

Any thoughts on how to force SQL to first select the values in the subquery and then run the fast subquery? I thought that using a LOOP join query hint would work, but it didn’t, because although it did do a loop starting with the subquery, the query plan for the view was not the same as the fast query, so I still had the performance issue.

Best regards,
Carlos Jourdan

source: http://stackoverflow.com/questions/11651726/subquery-x-temp-table-x-dynamic-sql-x-table-valued-function
Using sql-server: using-sql-server