Speed Up Cross Apply Without Index Hint

I have a very small table with 12 rows in it that can be created with the following statement:

CREATE TABLE dbo.SmallTable(ScoreMonth tinyint NOT NULL PRIMARY KEY,
                            ScoreGoal float NOT NULL
                           );

I have another table with ≈100M rows in it that can be created with the following statments:

CREATE TABLE dbo.SlowCrossApply(RecordKey nvarchar(12) NOT NULL,
                                Score1 decimal(3, 2) NOT NULL,
                                Score2 decimal(3, 2) NOT NULL,
                                Score3 decimal(3, 2) NOT NULL,
                                Score4 decimal(3, 2) NOT NULL,
                                Score5 decimal(3, 2) NOT NULL,
                                Score6 decimal(3, 2) NOT NULL,
                                FromToday bit NOT NULL
                               );

ALTER TABLE dbo.SlowCrossApply ADD CONSTRAINT i01PK PRIMARY KEY CLUSTERED(RecordKey ASC)
    WITH(FILLFACTOR = 90, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

CREATE NONCLUSTERED INDEX i02TodayRecords ON dbo.SlowCrossApply(FromToday)
    INCLUDE (Score1, Score2, Score3, Score4, Score5, Score6)
    WHERE FromToday = 1
    WITH(FILLFACTOR = 100, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
         DATA_COMPRESSION = PAGE
        );

i02TodayRecords has ≈1M rows in it. When I run the following query—I struggled formatting it to both look clean and prevent a horizontal scrollbar—it takes over 5 minutes to finish:

```
SELECT b.RecordKey,
COALESCE(NULLIF(ROUND(((0.95 * (ROW_NUMBER() OVER(PARTITION BY a.Prefix
ORDER BY b.Score6 ASC
) - 1
)
)
/ COALESCE(NULLIF(COUNT(*) OVER(PARTITION BY a.Prefix) - 1, 0

The problem you're likely facing is around SARGability, namely using the LEFT function in your WHERE clause:

LEFT(s2.RecordKey, 2) = a.Prefix

With that in there, you're stuck running the function for every row and then comparing it. You can't index for that, as-is. Putting the transformation into a CTE, view, or derived table wouldn't help, nor would writing a function to perform the manipulation.

One way around that is to create and index a computed column:

ALTER TABLE dbo.SlowCrossApply ADD LeftTwoPrefix AS LEFT(RecordKey, 2);

Which can be indexed. I'm also changing your index definition a bit:

CREATE NONCLUSTERED INDEX TodayRecords_Filtered ON 
dbo.SlowCrossApply(LeftTwoPrefix, FromToday, Score6)
INCLUDE (Score1, Score2, Score3, Score4, Score5)
WHERE FromToday = 1

Another alternative would be to dump the results of your CROSS JOIN into a temp table:

SELECT     LEFT(s.RecordKey, 2) AS Prefix, CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS INT) AS Quant
INTO       #yourmom
FROM       dbo.SlowCrossApply AS s
CROSS JOIN dbo.SmallTable AS sm
WHERE      s.FromToday = 1
AND        sm.ScoreMonth = MONTH(GETDATE())
GROUP BY   LEFT(s.RecordKey, 2), sm.ScoreGoal;

You followed up with:


... do you agree with the FromToday [column] being the index key in
i02TodayRecords instead of RecordKey, ignoring the specifics of the
query I used here?

In this case, it doesn't matter much. Non-unique nonclustered indexes store clustered index key columns in all levels of the nonclustered index. See my post here: Where Clustered Index Keys Dare.

And:


Furthermore, since RecordKey is "random", there would likely be a
great deal of fragmentation that would occur if it were the index key
as opposed to FromToday which will always be 1. Do those points sound
valid?

I generally don't worry about index fragmentation, so no. I aim to create indexes to help queries. A fragmented index is much more helpful than a non-existent index, and in many circumstances you'll never notice the fragmentation.

Hope this helps!

StackExchange Database Administrators Q#208822, answer score: 4