control when a certain filter is done | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

control when a certain filter is done

Hi If you have a look at this query: INSERT INTO @t_resmaster(outorder, masterrow, qty_todel, qty_res, qty_incres)
SELECT oor.outorder, oor.masterrow, SUM(oor.qty – oor.qty_del), SUM(oor.qty_res), SUM(oor.qty_incres)
[email protected]_reserve t
INNER LOOP JOIN outorderrow oor WITH (INDEX (s2_outorderrow), NOLOCK)
ONoor.comp_id = @cidOutorderrow
ANDoor.status < 60– Färdiglevererat
ANDoor.art_id = t.art_id
ANDoor.attr1_id = t.attr1_id
ANDoor.attr2_id = t.attr2_id
ANDoor.attr3_id = t.attr3_id
ANDoor.wareh_id = t.wareh_id
ANDoor.masterrow > 0
GROUP BY oor.outorder, oor.masterrow
(s2_outorderrow is on status, art_id, attr1_id, attr2_id, attr3_id, comp_id) In a sample session I have one row in @t_Reserve (it never have particularily many rows) and thus I belive that the optimal query plan would be if @t_reserve and outorderrow where LOOP JOINed after the index is seeked on status < 60. However, due to the ‘oor.masterrow’ (which I do not want to add to an index) part, it makes a query plan where it seeks for status < 60 and then bookmark lookup and filter on masterrow before it LOOP JOINs with @t_reserve. Is there any way to control this behaviour so that it does the masterrow > 0 part after it joined the tables? (less than splitting into two selects or something similar, of course) cheers /Linus —
http://anticAPSLOCK.com
Did you check if perhaps dropping the LOOP join hint results in a better execution plan?

Hi! It leads to different query plans, speed wise they are currently similar however. The LOOP was needed before INDEX was specified IIRC, but it seems like now they could be removed. Hoever, I still think they query plan could be better if the "masterrow > 0" was put later in the query (after joining against the art/attrX id’s) /linus —
http://anticAPSLOCK.com
Move the filter criteria out of the JOIN into a derived table statement, like this: INSERT INTO @t_resmaster(outorder, masterrow, qty_todel, qty_res, qty_incres)
SELECT oor.outorder, oor.masterrow, SUM(oor.qty – oor.qty_del), SUM(oor.qty_res), SUM(oor.qty_incres)
FROM @t_reserve t
INNER JOIN
(SELECT oor1.art_id, oor1.attr1_id, oor1.attr2_id, oor1.attr3_id, oor1.wareh_id, oor1.outorder, oor1.masterrow
FROM outorderrow oor1 WITH (INDEX (s2_outorderrow), NOLOCK)
WHERE oor1.status < 60 AND oor1.comp_id = @cidOutorderrow AND oor1.masterrow > 0) oor
ON oor.art_id = t.art_id
AND oor.attr1_id = t.attr1_id
AND oor.attr2_id = t.attr2_id
AND oor.attr3_id = t.attr3_id
AND oor.wareh_id = t.wareh_id
GROUP BY oor.outorder, oor.masterrow
I think adding option (force order) at the end of query and keeping index and loop join hint will do the trick.
Adriaan: Your sample doesent work, but perhaps a derived table in some way would, Ill have to experiment with this. mmarovic: I changed the query to
SELECT oor.outorder, oor.masterrow, SUM(oor.qty – oor.qty_del), SUM(oor.qty_res), SUM(oor.qty_incres)
FROM#t_reserve t
INNER LOOP JOIN outorderrow oor WITH (NOLOCK, INDEX (s2_outorderrow))
ONoor.status < 60– Färdiglevererat
ANDoor.art_id = t.art_id
ANDoor.attr1_id = t.attr1_id
ANDoor.attr2_id = t.attr2_id
ANDoor.attr3_id = t.attr3_id
ANDoor.comp_id = 0
WHERE/ANDoor.masterrow > 0
ANDoor.wareh_id = t.wareh_id
GROUP BY oor.outorder, oor.masterrow
OPTION (FORCE ORDER)
I’ve tried having the wareh_id and masterrow parts in the ON clause and in the WHERE, but both gives the same result, with or without FORCE ORDER. thanks both for the suggestions! /Linus —
http://anticAPSLOCK.com
Actually I misread what you think is optimal plan. I suggested force order since I think that gives you the best execution plan QO is able to produce. QO never (AFAIK) postpone resolution of filters on one table to do join first.
Something similar to what you want may be done using Adriaan’s approach, however I don’t know if it would be more efficient. I can try to write code if you let me know what is the primary key of OutOrderRow table.
]]>