18

i have a query like this:

select * from (
    select * from TableX
    where col1 % 2 = 0
) subquery
where col1 % 4 = 0

The actual subquery is more complicated. when i execute the subquery alone it returns maybe 200rows quickly, but when i execute the whole query, it takes too long to wait.

I know sql server takes some optimization here and merge the where statement into the subquery, and produce the new execution plan which is not that efficient. Althought i can dive into the execution plan and analyze why, like index missing, statistics stale.

But i surely know that, my subquery which serves as a BaseTable will only return a small portion of data, so i want all further filtering or joining will only take place in these small portion of data.

My question is, can i force sql server to execute the subquery first, without caring about the outer where statement? (btw, TempTable is my last option, CTE is not working)

Narutokk
  • 964
  • 1
  • 8
  • 20
  • what about using a temporary table? – fnurglewitz Dec 10 '12 at 08:40
  • 1
    What about you explain the real problem - do not force, but check execution plan and look why SQL Server thinks it is smarter that way. Are you fully patched? Some issues were in the apst with query optimizer - long in the past, but some people still use sql server 2005. – TomTom Dec 10 '12 at 08:41
  • 1
    @TomTom These predicates are unsargable so it is quite likely the optimiser estimates will be off. It can't use statistics and will just have to guess. Maybe creating statistics on a computed column will help. – Martin Smith Dec 10 '12 at 08:49
  • So the idea is to locate that and find out why. – TomTom Dec 10 '12 at 08:52
  • 2
    For a single query (containing any number of subqueries and CTE references), there's *no* reliable means to force a particular evaluation order between all of the predicates. – Damien_The_Unbeliever Dec 10 '12 at 11:01
  • 1
    Related connect items. [Provide a hint to force intermediate materialization of CTEs or derived tables](http://connect.microsoft.com/SQLServer/feedback/details/218968/) and [SQL Server should not raise illogical errors](http://connect.microsoft.com/SQLServer/feedback/details/537419/sql-server-should-not-raise-illogical-errors) – Martin Smith Dec 10 '12 at 11:06
  • You are trying to make SQL Server create a temporary "worktable" and use this for the outer query. This seems to be hard or even impossible, so why not create one yourself? It does not have to be a #Table, you can also use a @TableVariable. – Wolfgang Kais Jul 09 '18 at 00:12
  • 1
    Look into the use of `OPTION (FORCE ORDER)` to for SQL to complete the sub-query before the outer query. – CoveGeek Jul 17 '18 at 16:58

4 Answers4

27

If you have a key column in your TableX you can use a Self Join:

select x1.* 
from TableX x1
inner join  (
    select x.IdColumn 
    from TableX x
    where x.Col1 % 2 = 0
) x2 on x1.IdColumn = x2.IdColumn
where x1.Col1 % 4 = 0

Sql server will have to execute the inner query before to match the second condition.

You could use also the TOP(MaxInt) trick:

select * 
from (
    select top (9223372036854775807) * 
    from TableX
    where Col1 % 2 = 0
) subquery
where Col1 % 4 = 0

It will force to get subquery before apply the outer WHERE filter

MtwStark
  • 3,866
  • 1
  • 18
  • 32
  • 3
    Beatiful hack :) – ghord Apr 09 '19 at 08:24
  • 6
    The `TOP` saved my life. I was getting nuts because my subquery was good but the `where` was ignored which resulted into a conversion error. – Martin Jun 18 '19 at 10:24
  • Thanks! I am running XML.value function in outer query and was trying to do it AFTER other WHERE selections in a sub query. TOP trick did it! – JERKER Apr 04 '22 at 07:26
  • Just wondering why top (9223372036854775807) aka 0x13000001FFFFFFFFFFFFFF7F? Is there some significance in this 12-byte / 96-bit number? – Reversed Engineer Sep 16 '22 at 06:51
  • @ReversedEngineer because `TOP` takes a `BIGINT` as parameter, and that is the biggest bigint you can have [See MS Docs](https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16). Using `PERCENT` is risky, you can not get all records. – MtwStark Sep 16 '22 at 13:19
  • @MtwStark Thanks for that! My mistake was to assume that SELECT CAST (9223372036854775807 AS VARBINARY) would display the integer value as binary or hex. However, it seems to assume that 9223372036854775807 is NUMERIC(19,0), not a BIGINT, so I got the internal representation of that number as a NUMERIC(19, 0) instead. – Reversed Engineer Sep 19 '22 at 08:46
-2

enter image description here

CREATE TABLE [dbo].[Performance](
    [Id1] [int] NOT NULL,
    [Id2] [int] NOT NULL,
    [Id3] [int] NOT NULL,
    [Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Performance] PRIMARY KEY CLUSTERED 
(
    [Id1] ASC,
    [Id2] ASC,
    [Id3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

select count(*) from [Performance] = 2.000.000

select
    a.*
from
    [Performance] a
    inner join (
        select Id1,Id2,Id3,Description from [Performance] where ( id1 % 2 = 0)
    ) b on ( a.id1 = b.id1 )
where
    ( b.Id3 % 3 = 0 )

Self join with derived table

Results returned pretty fast I would say (200.000 rows).

enter image description here

shadow
  • 1,883
  • 1
  • 16
  • 24
-2

Have you tried:

select * from ( select * from TableX as inner where inner.col1 % 2 = 0 ) as subquery where subquery.col1 % 4 = 0

I believe it has to execute the subquery first here because you're explicitly having the where condition on the aliased result of the subquery.

Ron Oudgenoeg
  • 57
  • 1
  • 3
-5

You can use cte for that:

with cte as(
 select * from TableX where col1 % 2 = 0 
)

select * from ( select * from cte ) subquery where col1 % 4 = 0
Mohit Pandey
  • 3,679
  • 7
  • 26
  • 38