0

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.

  • Thanks for the article pointers. Some of the articles say ARITHABORT doesn't have any impact itself but changing it changes which cached plan you use (or generate fresh). The MS page hints darkly that it can lead to radically different plan optimizations, but that its main function is just what happens with math errors. I should have mentioned in the main post, that I also experimented with SentryOne Plan Explorer right after restore - it behaved like SSMS. – user1664043 Jan 30 at 22:48
  • The parameters/variables passed are the same, though I imagine that a dynamic sql statement with a SqlParameter collection may be treated through ADO.Net as an ad hoc stored procedure while the collected statement in SSMS they would look like variables instead of parameters. The articles hint that may impact optimization choices. – user1664043 Jan 30 at 22:49
  • I tried adding SET ARITHABORT ON to the top of the query; didn't change how the first execution got its plan. – user1664043 Jan 31 at 17:39
  • I added OPTION (FORCE ORDER) and that did fix the issue, as clunky as it seems. – user1664043 Feb 1 at 15:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.