0

I have a long query that returns 2000 rows in less than 2 seconds:

SELECT  target.Id, target.Decimals
FROM    dbo.t1
        JOIN dbo.t2 on ...
        JOIN dbo.t3 on ...
        JOIN dbo.t4 on ...
        JOIN dbo.target on ...
WHERE   t1.Id = ...
        AND t2.CreatedAt >= ''
        AND t2.Type = ''
        AND target.Id NOT IN (
            SELECT  s1.Id
            FROM    dbo.s1
                    JOIN dbo.s2
            WHERE   s1.Type = '' AND s2.Type = ''
        )

This is all good until I add one more condition to filter out a decimal column (NOT the sub-query):

        AND target.count = 0

The query than run really slow (> 1 min). If I select all 2000 rows, copy paste them on excel and filter manually, then return to SQL, the query would still running.

My table target has over 140k rows. I tried with another DB (different data set) with ~40k rows and it just need 1 second to filter all 6000 rows returned by the initial query.

In addition, changing the filter value would also affect the performance. The bigger it is (less likely to exist), the faster the query run. Ex: AND target.count = 100000 would return immediately.

I am not able to share any execution plans.

  • If you look at the query execution plan before / after you add the decimal column filter, you should immediately find the difference. One thing I'd say is that you may create an index on that decimal column to see the difference again. – jyao Sep 12 '18 at 20:06
  • Sadly I don't have permission. If the slow performance is because of large data set and lack of indexing, can I instruct SQL to just apply the addition filter on the 2000 rows selected by the initial query? – Hp93 Sep 16 '18 at 8:37
1

You didn't actually provide a question to answer, so I will assume that you're looking for a way to improve the performance of your query. People have written books on that topic so it's difficult to give an answer that fits in the site's format when an execution plan cannot be provided. With that said, the strategy that I use most often is to figure out why a query is slow. Comparing the actual plans between a fast and slow version of similar queries can be very helpful. For a query like yours, the issue might boil down to a missing index or a cardinality estimate issue.

If you want an approach that's nearly guaranteed to work, trying using a temp table. You can insert the unfiltered 2k rows into a temp table in a few seconds and it'll take less than a second to apply the filtering to the temp table with a second SELECT query. Using a temp table here avoids diagnosing the root cause of the issue, but there's nothing wrong with using a temp table to aid the performance of a complex query, or really any query.

In the comments, you asked the following question:

[C]an I instruct SQL to just apply the addition filter on the 2000 rows selected by the initial query?

This cannot be done directly but you can achieve the desired behavior with careful placement of TOP. Adding a TOP N with an extremely large N won't change the results of the query but it can change optimizer behavior. The optimizer must guarantee correctness in whatever plan it creates. You and I may know that taking the first 987,654,321,987,654,321 rows will never change the results but the optimizer will implement and honor that check because it cannot guarantee that it wouldn't change the results. For a query like the following:

SELECT q.Id, q.Decimals
FROM
(
    SELECT TOP (987654321987654321) target.Id, target.Decimals, target.count
    FROM    dbo.t1
            JOIN dbo.t2 on ...
            JOIN dbo.t3 on ...
            JOIN dbo.t4 on ...
            JOIN dbo.target on ...
    WHERE   t1.Id = ...
            AND t2.CreatedAt >= ''
            AND t2.Type = ''
            AND target.Id NOT IN (
                SELECT  s1.Id
                FROM    dbo.s1
                        JOIN dbo.s2
                WHERE   s1.Type = '' AND s2.Type = ''
            )
) q
WHERE q.count = 0;

The optimizer will not give you a plan that pushes down the count filter into the derived table. That means that it's pretty likely that you'll get the same query plan that finishes in a few seconds for the derived table, and overall performance should be roughly on par with that query. I expect that the slow query plan that runs for longer than a minute would be avoided.

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.