Post reply

Sargable query

  • 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

  • May 12, 2022 at 9:14 am

    #4031678

    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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 12, 2022 at 3:25 pm

    #4031874

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • May 13, 2022 at 7:29 am

    #4032251

    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

  • May 13, 2022 at 1:54 pm

    #4032415

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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