Need help improving the speed of a select statement-CodePudding

I have a select statement that is taking too long to run. It's probably because it needs to exclude about 98K records. Here is the scenario. I have two select statements: The first select gives me a list of IdValues. I take these IDValues (about 98K Records) and run them in my second select as an exclusion.

Query One

Select IdValue,IDName,IDCreatedby from Table1
Inner Join Table2 ON Table1Id.Table2Id
WHERE IdName = 'Acquisition'

Let's say the result set of the first query is as follows: IdValue1, IdValue2, IdValue3... So I take these values and tell my query to exclude these values.

Query Two

Select FName, MI, LName, and Dateofpurchase from Table3
inner join Table4 on Table3.Id=Table4.id
where PurchaseType = 'Holiday'
and IdValue not in ('IdValue1','IdValue2','IdValue3') (98K records for these Values)

Query Two is taking a lot of time to run, and I would like to see if I can make this query faster. Currently, I am copying the IdValues from query one, putting it into an excel file, then after I add '', I include it in the second query. This takes so much time, let alone the query's execution time.

Any help I can get is appreciated.

CodePudding user response：

What you do is a left join and then only take the null values. This is faster than having to store all 98k values and if your indexes are set up well it will be very fast. something like this -- note it was hard to tell what fields went to what alias so I made some guesses.

Select FName, MI, LName, Dateofpurchase 
from Table3
join Table4 on Table3.Id=Table4.id
left join (
  Select IdValue
  from Table1
  Join Table2 ON Table1Id.Table2Id = table2.id
  WHERE IdName = 'Acquisition'
) x on x.idValue = table3.idvalue
where PurchaseType = 'Holiday'
  and x.IdValue is null

This can be optimized -- for example if table1 has the idvalue returned by in x.idvalue then you don't even need a sub-query

CodePudding user response：

NOT EXISTS is something you should get familiar with. It performs better than NOT IN, as NOT IN is basically a series of:

 THIS OR THAT OR THIS OR THAT etc etc etc

I also left you some handy dandy tips for you. These tips point out something that is very important, especially for people who have to look at your code and try and figure things out.

SELECT 
    FName  -- all columns should have the proper table qualifier/alias
    , MI  -- all columns should have the proper table qualifier/alias
    , LName  -- all columns should have the proper table qualifier/alias
    , Dateofpurchase  -- all columns should have the proper table qualifier/alias
FROM 
    Table3  -- alias your tables 
    INNER JOIN Table4 ON Table3.Id = Table4.id  -- alias your tables 
WHERE 
    PurchaseType = 'Holiday'  -- all columns should have the proper table qualifier/alias
    AND NOT EXISTS
        (
            Select 
                IdValue  -- all columns should have the proper table qualifier/alias
            from 
                Table1   -- alias your tables 
                Inner Join Table2 ON Table1Id.Table2Id   -- alias your tables 
            WHERE 
                IdName = 'Acquisition'  -- all columns should have the proper table qualifier/alias
                AND IdValue = IdValue   -- all columns should have the proper table qualifier/alias
        )