Not Exist causing major performance problem

Question

Not Exist causing major performance problem

Welsh Corgi

SSC Guru

Points: 116520
More actions
August 28, 2015 at 8:15 am

#398957

I have the following code that includes a Not exist and when I run it takes 4 minutes. If I exclude it take 40 seconds to run.
My thought was to create the temporary table without the Not exist and delete the record from the temp table.
I need to add the columns in the select statement with a different alias.
I'm hung up on this. Can anyone please provide a recommendation?
This is the statement that is causing major performance issues.
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)
select distinct n.contr_id, contr_status, program_type, u.abbreviation,
z.ID, n.Contr_Trade, z.name as countyname,
t.Trade_ds_tx
into #tmpnexus
from tblnexus n
inner join contractor c on c.contr_id = n.contr_id
inner join contractorcoverage a on a.ContractorID = c.contr_id
inner join Postal_CountyRegion z on z.StateProvinceID = a.StateProvinceID and a.CountyRegionID = z.ID
inner join vContractorProfiles p on p.contr_id = n.contr_id and a.CoverageTypeID = p.profileid
inner join Postal_StateProvince u on u.ID = a.StateProvinceID
inner join trades t on t.trade_id_nb = n.contr_trade
inner join tradetablemap m on m.trade_id_nb = t.trade_id_nb
inner join prismclients pc on pc.prismclientid = n.prismclientid
inner join contractorstatus s on s.status_id = n.contr_status
inner join contrtrade ct on ct.contr_id = n.contr_id
inner join #tmpselect sel on sel.CountyRegionID = z.ID
inner join Postal on postal.countyregionid = a.CountyRegionID and postal.stateprovinceid = a.StateProvinceID
inner join #tmpcontrtrade tempc on tempc.contr_trade = n.contr_trade
where
sel.countyregionid = a.countyregionid
and n.contr_trade = tempc.contr_trade
and n.contr_status in (1,12,17,21,2,13,23,26,27,32,3,14,28,6,7,10,20,36,34,33,35,37)
and c.activeforreports = 1
and pc.ActiveForReports = 1
and n.prismclientid in (1)
and program_type in (1)
and
(
(n.contr_trade = 63 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 48),2,1)) OR
(n.contr_trade = 46 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 28),1)) OR
(n.contr_trade in (1,93,2,81,76) and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID is null),1)) OR
(n.contr_trade not in (1,93,2,81,76,63,46) and p.contrcoverageareatypeid = 1)
)
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 19 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Read the article on your signature on how to post performance questions.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

All that I want to do is remove the not exist and delete from the Temp table.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Help us help you. Provide what is asked in the articles you reference in your own signature block.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

You've been around long enough to know what's needed to address a problem.

Like Luis and Lynn said, read your own signature block.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

ok, I will resolve the problem myself.

No need for additional post.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Welsh Corgi (8/28/2015)
I have the following code that includes a Not exist and when I run it takes 4 minutes. If I exclude it take 40 seconds to run.
My thought was to create the temporary table without the Not exist and delete the record from the temp table.
I need to add the columns in the select statement with a different alias.
I'm hung up on this. Can anyone please provide a recommendation?
This is the statement that is causing major performance issues.
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)
select distinct n.contr_id, contr_status, program_type, u.abbreviation,
z.ID, n.Contr_Trade, z.name as countyname,
t.Trade_ds_tx
into #tmpnexus
from tblnexus n
inner join contractor c on c.contr_id = n.contr_id
inner join contractorcoverage a on a.ContractorID = c.contr_id
inner join Postal_CountyRegion z on z.StateProvinceID = a.StateProvinceID and a.CountyRegionID = z.ID
inner join vContractorProfiles p on p.contr_id = n.contr_id and a.CoverageTypeID = p.profileid
inner join Postal_StateProvince u on u.ID = a.StateProvinceID
inner join trades t on t.trade_id_nb = n.contr_trade
inner join tradetablemap m on m.trade_id_nb = t.trade_id_nb
inner join prismclients pc on pc.prismclientid = n.prismclientid
inner join contractorstatus s on s.status_id = n.contr_status
inner join contrtrade ct on ct.contr_id = n.contr_id
inner join #tmpselect sel on sel.CountyRegionID = z.ID
inner join Postal on postal.countyregionid = a.CountyRegionID and postal.stateprovinceid = a.StateProvinceID
inner join #tmpcontrtrade tempc on tempc.contr_trade = n.contr_trade
where
sel.countyregionid = a.countyregionid
and n.contr_trade = tempc.contr_trade
and n.contr_status in (1,12,17,21,2,13,23,26,27,32,3,14,28,6,7,10,20,36,34,33,35,37)
and c.activeforreports = 1
and pc.ActiveForReports = 1
and n.prismclientid in (1)
and program_type in (1)
and
(
(n.contr_trade = 63 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 48),2,1)) OR
(n.contr_trade = 46 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 28),1)) OR
(n.contr_trade in (1,93,2,81,76) and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID is null),1)) OR
(n.contr_trade not in (1,93,2,81,76,63,46) and p.contrcoverageareatypeid = 1)
)
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)

Before you go to all that trouble, try changing "postalid" to "*" or "1" in the not exists and see what happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 8

You can also get rid of the not exist altogether by doing a LEFT JOIN to ContractorCoverageException cce...

Then in the WHERE clause...

WHERE cce.contractorcoverageid IS NULL

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Before I read the replies I replaced the not exist with a left join.

It takes 5 minutes either way.

Thank you.

left JOIN ContractorCoverageException cce ON cce.contractorcoverageID = a.ID and cce.PostalID = postal.ID and e.postalid is null

-- and not exists (select postalid from ContractorCoverageException e where e.contractorcoverageid = ContractorCoverage.id and postalid = postal.id)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

Jeff Moden (8/28/2015)
Welsh Corgi (8/28/2015)
I have the following code that includes a Not exist and when I run it takes 4 minutes. If I exclude it take 40 seconds to run.
My thought was to create the temporary table without the Not exist and delete the record from the temp table.
I need to add the columns in the select statement with a different alias.
I'm hung up on this. Can anyone please provide a recommendation?
This is the statement that is causing major performance issues.
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)
select distinct n.contr_id, contr_status, program_type, u.abbreviation,
z.ID, n.Contr_Trade, z.name as countyname,
t.Trade_ds_tx
into #tmpnexus
from tblnexus n
inner join contractor c on c.contr_id = n.contr_id
inner join contractorcoverage a on a.ContractorID = c.contr_id
inner join Postal_CountyRegion z on z.StateProvinceID = a.StateProvinceID and a.CountyRegionID = z.ID
inner join vContractorProfiles p on p.contr_id = n.contr_id and a.CoverageTypeID = p.profileid
inner join Postal_StateProvince u on u.ID = a.StateProvinceID
inner join trades t on t.trade_id_nb = n.contr_trade
inner join tradetablemap m on m.trade_id_nb = t.trade_id_nb
inner join prismclients pc on pc.prismclientid = n.prismclientid
inner join contractorstatus s on s.status_id = n.contr_status
inner join contrtrade ct on ct.contr_id = n.contr_id
inner join #tmpselect sel on sel.CountyRegionID = z.ID
inner join Postal on postal.countyregionid = a.CountyRegionID and postal.stateprovinceid = a.StateProvinceID
inner join #tmpcontrtrade tempc on tempc.contr_trade = n.contr_trade
where
sel.countyregionid = a.countyregionid
and n.contr_trade = tempc.contr_trade
and n.contr_status in (1,12,17,21,2,13,23,26,27,32,3,14,28,6,7,10,20,36,34,33,35,37)
and c.activeforreports = 1
and pc.ActiveForReports = 1
and n.prismclientid in (1)
and program_type in (1)
and
(
(n.contr_trade = 63 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 48),2,1)) OR
(n.contr_trade = 46 and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID = 28),1)) OR
(n.contr_trade in (1,93,2,81,76) and p.contrcoverageareatypeid = Coalesce((Select Top 1 ID from ContractorCoverageType where ClientID = 1 and SingleTradeID is null),1)) OR
(n.contr_trade not in (1,93,2,81,76,63,46) and p.contrcoverageareatypeid = 1)
)
--and not exists (select postalid from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)
Before you go to all that trouble, try changing "postalid" to "*" or "1" in the not exists and see what happens.

ok I will do that, Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

.Jeff,

I'm not sure how to change the code?

Thanks again.

left JOIN ContractorCoverageException cce ON cce.contractorcoverageID = a.ID and cce.PostalID = postal.ID and e.postalid is null

-- and not exists (select postalid from ContractorCoverageException e where e.contractorcoverageid = ContractorCoverage.id and postalid = postal.id)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

and not exists (select * from ContractorCoverageException cce where cce.contractorcoverageid = a.id and postalid = postal.id)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kim Crosser SSCommitted Points: 1763 More actions · Answer 13

Have you tried the following?

-- in the joins section

left outer join ContractorCoverageException e where e.contractorcoverageid = ContractorCoverage.id and e.postalid = postal.id

-- in the where section

and e.postalid is null

Kim Crosser SSCommitted Points: 1763 More actions · Answer 14

... and of course, you have a suitable (unique? clustered? as appropriate) index on ContractorCoverageException, like:

... ContractorCoverageException (contractorcoverageid, postalid)

or

... ContractorCoverageException (contractorcoverageid) include (postalid)