Bad query plan only on first execution, not in SSMS 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.
When application is run for the first time of day,then it is usually slow,so problem may be in Middleware logic also.

Like you said you are using Dataset,if your were using Generic collection like LIST and populate them with SqlDataReader,then you can see the performance improvement your self.

Second thing is How you are using those 2 ResultSet ?Are the subject to further filter between datatable. ?

As far as your query is visible, I see that you are querying Order Table twice using same predicate and Order by clause

your query can be rewritten like this,

Create TABLE #tempOrder(all required columns)
insert into #tempOrder
SELECT TOP (20) bunch of configurable columns, xml blob of customer preferences, ...
FROM ORDERS
INNER JOIN CUSTOMERS ON CUSTOMERS.ID = ORDERS.CUSTOMERID 
INNER JOIN CUST_PREFS ON CUSTOMERS.ID = CUST_PREFS.CUSTOMERID
WHERE ORDERS.DATE BETWEEN @from and @to
ORDER BY ORDERS.DATE

SELECT bunch of configurable columns, xml blob of customer preferences, ...
FROM
#tempOrder ORDERS
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 ...



SELECT ORDERID, orderdetails
FROM
    (SELECT ORDERID
     FROM #tempOrder ORDERS
INNER JOIN ORDERDETAILS ON ORDERDETAILS.ORDERID = ORDERS.ORDERID

Create Non Clustered index on ORDERS(DATE)


There is no point in creating index on #tempOrder since it will contain only 20 records always(Top 20 indicate) 


  bunch of configurable columns


It means that query must be dynamic so it all depend upon your real query.

Use SET NOCOUNT ON