SQL Server: query optimization

Question

I have the following query which takes around 4 minutes to execute.

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND h.holdinNo NOT IN (SELECT holdingNo
                              FROM   [db_land].[dbo].tbl_bill
                              WHERE  year(date_month) = YEAR(@tdate)
                                     AND MONTH(date_month) = MONTH(@tdate)
                                     AND ( update_by IS NOT NULL
                                            OR ispay = 1 ))

I found the inner join takes only few seconds.

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'

It's the NOT IN checking which takes a lot of time. How I can optimize this query? For me it's needed to execute the query at least with in minute.

You have three different guesses as to what the issue is. Upload the XML for the actual execution plan so we can see precisely where the issue is — Martin Smith
– Martin Smith, Commented Sep 8, 2019 at 11:13

Dan Guzman · Accepted Answer · 2019-09-08 11:14:48Z

2

Make sure the WHERE and JOIN clause predicates are sargable. Applying a function to a column (e.g. YEAR(date_month)) prevents indexes on the column from being used efficiently.

Try this expression instead to avoid the functions. There are other methods depending on the SQL Server version.

WHERE
    date_month >= DATEADD(day, 1, DATEADD(month, -1, EOMONTH(@tdate)))
    AND date_month < DATEADD(day, 1, DATEADD(month, 1, EOMONTH(@tdate)))

answered Sep 8, 2019 at 11:14

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

forpas Over a year ago

DATEADD() and EOMONTH() aren't functions?!!! Your code is not consistent with your claims.

Martin Smith Over a year ago

They are functions but they are not being applied to a column. They are being applied on variables

Dan Guzman Over a year ago

@MartinSmith, unless I'm missing something, date_month looks to be a column name. Functions are also applied to variables in the query.

Martin Smith Over a year ago

my response was to @forpas. Responding to DATEADD() and EOMONTH() aren't functions?!!!

forpas · Accepted Answer · 2019-09-08 11:11:27Z

1

Try by replacing NOT IN with a LEFT JOIN of the table [db_land].[dbo].tbl_bill on all the conditions and adding in the WHERE clause holdingNo is null so the returned rows are the non matching rows:

select c.id as clid, h.id as hlid,h.holdinNo, c.cliendID, c.clientName, h.floor, h.connect_radius 
from [db_land].[dbo].tbl_client as c 
inner join [db_land].[dbo].tx_holding as h 
on c.id= h.clid
left join [db_land].[dbo].tbl_bill as b 
on b.holdingNo = h.holdinNo and year(b.date_month) = YEAR(@tdate) and MONTH(b.date_month) = MONTH(@tdate) 
and (b.update_by is not null or b.ispay = 1)
where h.status = 1 and h.connect_radius is not null and c.status=1 and h.type='Residential' and b.holdingNo is null

answered Sep 8, 2019 at 11:11

forpas

165k10 gold badges51 silver badges85 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2019-09-08 11:17:01Z

1

I would recommend changing the NOT IN to NOT EXISTS and adding an index:

WHERE . . . AND
      NOT EXISTS (SELECT 1
                  FROM [db_land].[dbo].tbl_bill b
                  WHERE b.holdingNo = h.holdingNo AND
                        b.date_month >= DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1) AND
                        b.date_month < DATEADD(month, 1, DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1)) AND
                        (b.update_by IS NOT NULL OR b.ispay = 1
                 )

Then the index that you want is on tbl_bill(holdingNo, date_month, update_by, ispay).

answered Sep 8, 2019 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

masoud · Accepted Answer · 2019-09-08 11:28:57Z

Put your sub query into temp table :

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT holdingNo 
into #TmpholdingNo
FROM   [db_land].[dbo].tbl_bill
WHERE  year(date_month) = YEAR(@tdate)
        AND MONTH(date_month) = MONTH(@tdate)
        AND ( update_by IS NOT NULL
        OR ispay = 1 )

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND h.holdinNo NOT IN (SELECT holdingNo from #TmpholdingNo) 

drop table #TmpholdingNo

Steve Ford · Accepted Answer · 2019-09-09 08:25:27Z

Rather than using functions in your WHERE clause try calculating the start and end filter dates, using OPTION (RECOMPILE) can help SQL to use the actual values of your variables in your query plan. I would also change NOT IN to NOT EXISTS:

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'
DECLARE @startDate DATE = DATEFROMPARTS(YEAR(@tdate), MONTH(@tdate), 1)
DECLARE @endDate DATE = DATEADD(day,1,EOMONTH(@tdate))


SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM   [db_land].[dbo].tbl_client AS c
       INNER JOIN [db_land].[dbo].tx_holding AS h
               ON c.id = h.clid
WHERE  h.status = 1
       AND h.connect_radius IS NOT NULL
       AND c.status = 1
       AND h.type = 'Residential'
       AND NOT EXISTS (SELECT holdingNo
                       FROM   [db_land].[dbo].tbl_bill
                       WHERE  holdingNo = h.holdinNo AND
                       date_month >= @startDate AND
                       date_month < @endDate AND 
                                     AND ( update_by IS NOT NULL
                                            OR ispay = 1 )) 
OPTION (RECOMPILE)

Stefanos Zilellis · Accepted Answer · 2019-09-09 14:38:26Z

give a try try this:

select main.* from 

    (SELECT c.id AS clid,
           h.id AS hlid,
           h.holdinNo,
           c.cliendID,
           c.clientName,
           h.floor,
           h.connect_radius
    FROM   [db_land].[dbo].tbl_client AS c
           INNER JOIN [db_land].[dbo].tx_holding AS h
                   ON c.id = h.clid
    WHERE  h.status = 1
           AND h.connect_radius IS NOT NULL
           AND c.status = 1
           AND h.type = 'Residential')main

left join 

    (select holdingNo from 
        (SELECT holdingNo, update_by, ispay
        FROM   [db_land].[dbo].tbl_bill
        WHERE  year(date_month) = YEAR(@tdate)
        AND MONTH(date_month) = MONTH(@tdate))bill1
    where update_by IS NOT NULL OR ispay = 1)bill2

on main.holdinNo = bill2.holdinNo


where bill2.holdinNo is null

Mahbubur Rahman Manik · Accepted Answer · 2019-09-22 16:03:03Z

0

put the filter list at variable,then them apply the filter

     DECLARE @filter TABLE  INSERT INTO @filter SELECT  FROM  [db_land].[dbo].tbl_bill

them apply the filter

DECLARE @tdate DATETIME = '2019-09-01 00:00:00.000'

SELECT c.id AS clid,
       h.id AS hlid,
       h.holdinNo,
       c.cliendID,
       c.clientName,
       h.floor,
       h.connect_radius
FROM [db_land].[dbo].tbl_client AS c
INNER JOIN [db_land].[dbo].tx_holding AS h ON c.id= h.clid
WHERE h.status=1
  AND h.connect_radius IS NOT NULL
  AND c.status=1
  AND h.type='Residential'
  AND h.holdinNo NOT IN (filter)

edited Sep 22, 2019 at 16:03

Mahbubur Rahman Manik

5,1813 gold badges42 silver badges47 bronze badges

answered Sep 8, 2019 at 11:12

feras

113 bronze badges

Collectives™ on Stack Overflow

SQL Server: query optimization

7 Answers 7

4 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related