Link to home
Start Free TrialLog in
Avatar of MadIce
MadIce

asked on

Select Query to Insert slows process

I have a Select query that runs within a second. When I change to an Insert query, runs longer than 12 minutes. here is the code:

declare @ADC table (
Lot	varchar(20),
Rev varchar(5)
)

declare @ADC1 table (
Lot	varchar(20),
ComponentLot varchar(30))


insert into @ADC
SELECT  EADC.LOT, ADC.REV
FROM     ITEM_ID AS EADC INNER JOIN
               DATA_CARDS AS ADC ON EADC.ADC_ID = ADC.ADC_ID
WHERE  (ADC.NUM = '9246248')

insert into @ADC1
SELECT distinct  EADC.LOT, CADC.LOT_NUM AS ComponentLot
FROM     ITEM_ID AS EADC INNER JOIN
               DATA_CARDS AS CADC ON EADC.ADC_ID = CADC.ADC_ID INNER JOIN
               Noun N ON EADC.NounId = N.NounId
  where CADC.LOT in (Select Lot from @ADC) and N.NOUN like '%BODY%ASSY%' 

Open in new window


Its the second insert query that is running slow. If I changed to select, no problem. why is this an issue?
Using SQL Server 2008 R2, SSMS
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MadIce
MadIce

ASKER

I did switch to temporary table. changed the overall process from 11 minutes to 6 seconds. I remembered I had the same issue before. On your other suggestions. I should use the inner join and will probably switch. Not familiar with the "With" statement used in this way. Have to study up on it. I have a few other insert queries beyond what I displayed. So will have to take a second look.
Thank you for the answer and the time.