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

Question

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

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

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

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 1

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!

Thanks.

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 2

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

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 3

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)).

Thanks.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

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.
Thanks

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 3 years, 3 months ago by homebrew01.

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 6

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.

Thanks.

P.S.

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.

SELECT
  CorporationID,
  IsArchived,
  Name
FROM (
  SELECT TOP 10000000
  C.CorporationID,
  EA.IsArchived,
  EN.Name
  FROM Corporation2 C
  LEFT JOIN EntityAttributes2 EA ON EA.EntityID = C.CorporationID
  OUTER APPLY (
    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

This reply was modified 3 years, 3 months ago by cmartel 20772.

Ken McKelvey SSCoach Points: 18859 More actions · Answer 7

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.

cmartel 20772 Ten Centuries Points: 1233 More actions · Answer 8

Ken,

Compatibility level is 150. QUERYTRACEON 9481 has no effect.

Thanks.