Query Performance Increase

[happyIndian100]

Please Help,

The below query is taking me 1 min 30 secs to execute.

can any body help me to optimise the below query...

INSERT INTO dbo.Table1
SELECT * FROM Table2
WHERE
AccNo NOT IN
(select distinct AccNo from dbo.Table1
OR
Location NOT IN
(select distinct Location from dbo.Table1

Thanks in advance

--Happy

 

[Welshbird]

Hi Happy -

First questino I'd guess has to be do you know what indexes are on these tables? That's have to help for a start...

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[vongrunt]

There is no need to use DISTINCT with IN()...

About how many rows are we talking anyway?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Use not exists instead and get rid of the distinct

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Welshbird]

Good point vongrunt.... as usual I missed the obvious!

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[happyIndian100]

The Total Records i am working are 2000,000.

The indexes are already set on Location and AccNo.


I removed Disctinct clause and executed the query it still takes the same amount of time.

ANY HELP IS APPRETIATED...

thx
-Happy

 

[vongrunt]

Lemme do DeMorgan thing:

NOT(AB) OR NOT(AC) = NOT (AB AND AC)

So query turns into classic outer join NULL check:

INSERT INTO dbo.Table1
SELECT * FROM Table2
LEFT OUTER JOIN Table1
	ON Table2.AccNo = Table1.AccNo AND Table2.Location=Table1.Location
WHERE	 Table1.primarykey IS NULL

I'm not sure this will run faster but try it anyway... btw. how many rows were inserted into Table1 after query finished?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[happyIndian100]

we are inserting 700000 records.

for table1 the primary key is a combination of columns.

so in that case how the above query looks like.

thx

 

[vongrunt]

90 seconds for 700,000 rows is not bad, depending on hardware you have.

There is a good chance most of time goes on INSERT itself. If SELECT part of query is optimized well, divide mass insert into smaller batches. See faq183-3141 for more details.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[happyIndian100]

Actually 90 Secs is consumed only by Select query.

 

[chiph]

Whenever you use "NOT", a table-scan usually results, so indexing will have no effect.

You might try creating a temp table that contains the values from your nested selects, and see if that speeds things up.

Also - SELECT * is usually bad practice. You'll want to specify which columns are involved to prevent maintenance headaches in the future.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first