We have a very customizable report query system where the user can pick and chose which columns to include in the results as well as a lot of filters.
I'll be mapping what we're doing onto an example orders system. Customers, orders, line items of orders and the like. For purposes of the example problem also assume we've got a very free-form chunk of customer information like "wife's name", "anniversary", "names of pets", etc (stuff you might want around to personalize interactions with the customer)
To avoid massive denormalization of the order details in our orders report (when they select that output), we're populating a DataSet with 2 queries - the first query is for the orders that meet the filter criteria; the second is for the order details that match the output from the first query.
Our qa dept recently started running into some problems in the C# middleware because the output of the 2 queries is disjoint, even though the same filter criteria are used in both queries. Both queries start by asking for the top 20 orders that match the filter criteria.
Further complicating things, the qa automation suite does a lot of database restores, setting a few data conditions, running a test and then doing that again for the next test case. After a lot of back-and-forth, we found that they are only having the problem the first time this query is run after a restore. Every execution after the first one (with the same query) produces the correct results.
I even got their seed database and started doing the restores myself. If I took the SQL code and ran it in SSMS immediately after the restore, I couldn't reproduce the problem. I can only reproduce it if I run through the app code and ADO.NET to run the query.
I used Profiler to capture the query plan on that first execution and for reasons I can't fathom, only on that first execution it completely upends the query and starts by looking for that free-form blob of pet names as the seed kernel of the results. e.g.
SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ... FROM ORDERS INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID INNER JOIN (SELECT sums, avgs, etc FROM ORDERDETAILS GROUP BY ORDERDETAILS.ORDERID) orderavg on orderavg.ORDERID = ORDERS.ORDERID ... whatever other joins needed based on configuration ... INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID WHERE ORDERS.DATE BETWEEN @from and @to ORDER BY ORDERS.DATE SELECT ORDERID, orderdetails FROM (SELECT TOP(20) ORDERID FROM ORDERS WHERE ORDERS.DATE BETWEEN @from and @to ORDER BY ORDERS.DATE) ORDERS INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID
Seemed straightforward enough - start from the orders table and work your way down.
But for this case QA ran into, only on the first execution after a db restore, and only through ADO.NET (not SSMS), the query plan comes out starting the execution with the CUST_PREFS "find me the random customer notes blob" join (the 7th join in the query). Re-run the same query in the app again, and it goes back to starting with the root table (ORDERS).
A) I'm at a loss to figure out why it decides the 7th join is the right place to start immediately after a restore. The stats don't bear it out and it goes back to something more understandable on the 2nd execution.
B) I could try to encapsulate the core filter query in a subselect (like the 2nd query has) to make the filter evaluation more predictable and only join for detail after the right subset is selected, but that seems a lot of reshuffling.
C) I'm debating adding an OPTION (FORCE ORDER) hint to the first query to try and make this more predictable, but I thought I'd toss this out amongst a group with deeper knowledge than I before pulling on that thread.
Any pointers from those wiser than me would be appreciated.