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
MadIceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
First of all: Have you took a look at the activity monitor? It may be some blocking issue with other processes.

Table variables perform not that good, cause the row estimate is normally 1, even when you have more rows in your table variable. So the query optimizer may choose a suboptimal plan. btw, how many rows are in your table variable?

One optimization would be using a temporary table ifor @ADC.

Another optimization would be an INNER JOIN instead of an IN operator:

DECLARE @ADC TABLE ( Lot VARCHAR(20) );
DECLARE @ADC1 TABLE 
(
	Lot	VARCHAR(20),
	ComponentLot VARCHAR(30)
);

INSERT INTO @ADC
	SELECT  DISTINCT 
			EADC.LOT
	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 
	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
		INNER JOIN @ADC A ON CADC.LOT = A.Lot
	WHERE	N.NOUN like '%BODY%ASSY%';

Open in new window


And further more, why using that variable at all?

DECLARE @ADC1 TABLE 
(
	Lot	VARCHAR(20),
	ComponentLot VARCHAR(30)
);

WITH ADC AS (
	SELECT  EADC.LOT
	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 
		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
			INNER JOIN ADC A ON CADC.LOT = A.Lot
		WHERE	N.NOUN like '%BODY%ASSY%';

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MadIceAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.