Sargable query

Question

Sargable query

aureliu

SSC-Addicted

Points: 441
More actions
May 12, 2022 at 9:05 am

#4031675

Hello Guys,

I have a query and I wish to change into SARGABLE query :

select count(*) from sot_gps_dp.dwh_dm_wip_interflex_hopurs ddwih where asis is null and year(twdatum)=YEAR(getdate())

May you help me somebody with it?
Thanks in advance,
Regards,
Hadrian

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Doing it this way may help. It assumes that twdatum is a datetime column and that there is a suitable index in place (eg, on (twdatum, asis)).

DECLARE @StartAt DATETIME = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);
DECLARE @EndAt DATETIME = DATEADD(YEAR, 1, @StartAt);

SELECT COUNT(*)
FROM sot_gps_dp.dwh_dm_wip_interflex_hopurs ddwih
WHERE ddwih.asis IS NULL
      AND ddwih.twdatum >= @StartAt
      AND ddwih.twdatum < @EndAt;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You don't need to use variables. I also suggest sticking to the "best practice" technique for getting a specific date value.

An index would be best on ( asis, twdatum ); an index on ( twdatum, asis ) would have to read all asis values and check them at run time.

SELECT COUNT(*) AS current_year_count
FROM sot_gps_dp.dwh_dm_wip_interflex_hopurs ddwih 
WHERE ddwih.asis IS NULL AND 
    ddwih.twdatum >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND
    ddwih.twdatum <  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)

The best practice technique I was referring to is this pattern:

DATEADD(<datepart>, DATEDIFF(<datepart>, 0, GETDATE()), 0)

So, for example, if you want the start of the current month, you do:

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

This keeps the code consistent and the pattern becomes very easily recognizable later.

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

aureliu SSC-Addicted Points: 441 More actions · Answer 3

Thanks Scott for sending this approach.

One more thing the other query has this filter WHERE ISRECNUM % 20 = 5 , column iSRecnum is int .How can I switch to be sargable ,too.

Thanks again,

Regards,

Hadrian

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

You're not going to be able to this calculation:

ISRECNUM % 20 = 5

sargable. By definition, it just isn't.

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