Using (or not) query HINTS to speed up slow statement

  • Frederico,

    I also think that SQL Server is being lousy in this particular case. I would not mind if my "unoptimized" statement would run twice or three times slower but we are talking 100 times slower here.

    I can only hope that the next version of SQL Server will do better!


  • Have you tried the options: optimize for unknown and recompile?

  • Jonathan,

    All reports are based on a "body" statement and the WHERE and ORDER BY clause is added according to the user choices. The resulting statement is always executed with OPTION (RECOMPILE).

    You can experiment with the simple script I posted that creates 4 tables and you will see that OPTIMIZE FOR UNKNOWN and RECOMPILE have no effect (but FORCE ORDER does make the statement run fast (but makes some statements run significantly slower)).


  • frederico_fonseca wrote:

    sql playing dumb on this case

    if your software can be set to do other types of joins/cross applies or even to, as you said, generate the isnull (which works on this case but may cause issues on other cases), then changing the outer apply to cross apply on the particular sample of data you supplied also generates a correct plan.

    Just based on your sample data adding different indexes does not seem to solve the issue.

    No, SQL is "playing dumb".

    SQL executes things in a specific order, based primarily on guaranteeing that it produces an accurate result.  The ONE thing SQL NEVER wants to do is give you a false result.

    In general, SQL processes the JOIN before the WHERE.  When it can do so, it may combine elements of the WHERE into the JOIN, but only if SQL can guarantee that will never produce an incorrect result.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • cmartel 20772 wrote:

    Hi Scott,

    like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both). Therefore, I wish to know if there is something I can do so the statement runs faster as is.


    How about .......Write results without the WHERE clause to a temp table, including the Active/Archived values. then run a query on the temp table using the WHERE clause to return the results ?

    • This reply was modified 1 year, 2 months ago by  homebrew01.
  • homebrew01,

    Of course I can fill a temp table and apply the WHERE clause on a "SELECT * FROM #TEMP" statement. This approach has been discussed in forums for ages. But why are we relying on SQL Server to produce good execution plans where we could just staple together a bunch of temp tables filled using cursors while being at it. Moreover, some third parties applications are only able to connect to SQL Server using views. In other words, it would be nice to tell SQL Server to materialize subquery X in: "SELECT X.Value1 FROM (SELECT Value1, Value2 FROM Table) X WHERE X.Value2 = 'F'". I would definitely prefer this over creating a temp table.

    Though I am not expecting SQL Server to produce the optimal execution plan of each statement, the actual case produces a plan that executes in 1/10 of a second or 80 seconds on my local SQL Server. I think that SQL Server can do better.



    Using a TOP <large number> forces the materialization of the subquery and results in a fast execution plan. That is a solution I favor over the use of a temporary table.

    FROM (
    SELECT TOP 10000000
    FROM Corporation2 C
    LEFT JOIN EntityAttributes2 EA ON EA.EntityID = C.CorporationID
    SELECT TOP 1 NameID FROM CorporationHistory2
    WHERE CorporationID = C.CorporationID AND EffectiveDate <= '2021-09-30'
    ORDER BY EffectiveDate DESC
    ) CH
    LEFT JOIN EntityName2 EN ON EN.EntityNameID = CH.NameID
    ) X
    WHERE IsArchived = 'F' AND Name IS NOT NULL
  • You have posted in a SQL2019 forum but do not say what COMPATIBILITY_LEVEL your database is using.

    If COMPATIBILITY_LEVEL >= 120 you could also try OPTION (QUERYTRACEON 9481)

    If COMPATIBILITY_LEVEL < 120 you could also try OPTION (QUERYTRACEON 2312)

    This would force the use of either the old or new cardinality estimator. One of them might work better than the default for your queries.


  • Ken,

    Compatibility level is 150. QUERYTRACEON 9481 has no effect.


Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply