Query Analyzer runs a long time but VB app gets "Timeout" 1

[dtqui]

Hi,
I have this table postcode with 122694 records and address table with 122691 records. I'm trying to delete all the records that exists in postcode table but not in address table. My SQL is "DELETE FROM [Postcode] WHERE [Postcode].[Postal_cd] NOT IN (SELECT [Postal_cd] FROM [Address])".

I wanted to estimate how many records it will affect, so I changed it to a SELECT query. Upon running in my VB application, it returns Timeout. Running in Query Analyzer for over 5 minutes still doesn't return any results.

I've added indexes to the field Postal_cd in both tables but the results are still the same.

Can anyone tell me what is wrong?

Thanks in advance!!

 

[tlbroadbent]

The NOT IN clause in a query can be terribly inefficient. Try one of the following queries instead. Make sure each table has an Index on the column Postal_cd.

DELETE Postcode
FROM Postcode a
WHERE NOT EXISTS
(SELECT * FROM Address
WHERE Postal_cd=a.Postal_cd)

DELETE Postcode
FROM Postcode a
LEFT JOIN Address b
ON a.Postal_cd=b.Postal_cd
WHERE b.Postal_cd is null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dtqui]

Thanks a lot!! I tried the second SQL statement you gave me and it runs in under 10 seconds from my VB application.

Another question about SQL Statements.. Is there another thing wrong with this statement?

"INSERT INTO v_Postcode SELECT * FROM Postcode" (122691 records)

By the way, what is the timeout for a connection from a VB 5 application using ADO? Is the connection timeout the same as the timeout that occurred while running ineffective queries?

Thanks again!!

 

[tlbroadbent]

The INSERT statement appears to be syntactically correct. I can't tell you if anything else is wrong. Are you having problems with it?

I believe the ADO CommandTimeout default value is 30 seconds. Someone who is more familiar with ADO amy be able to answer that question more definitively. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dtqui]

Yes. I'm having the same problem as my first post except that when I run the insert query in Query Analyzer, it took about 30 over seconds to complete. The VB application has returned "Timeout expired".

Do you suggest any workarounds for this query, Terry?

Thanks for your help!

 

[tlbroadbent]

If you remove any existing indexes from the table you are inserting into, the query should run faster. However, the best thing you can do is split up the inserts into batches. If you split the inserts into batches, SQL doesn't need to continually expand tempdb and the transaction log of the current database.

In the following example, SQL would insert 20000 records each time the code was run. It would only insert records that didn't already exist in v_Postcode.

SET ROWCOUNT 20000 /* Only process 20000 records */

INSERT INTO v_Postcode
SELECT *
FROM Postcode a
LEFT JOIN v_Postcode b
ON a.Postal_cd=b.Postal_cd
WHERE b.Postal_cd is null

SET ROWCOUNT 0 /* Reset rowcount */ Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[MarkSweetland]

You're right, the default timeout is 30 seconds.

Use the CommandTimeout property on a ADO Connection or Command object to allow the cancellation of an Execute method call. If you set the property to zero, ADO will wait indefinitely until the execution is complete.

Mark

 

[dtqui]

Thank you to the both of you for helping me and answering my questions. My problems have been solved.

Now that I know the timeout is 30 seconds, next time for all those which exceed the time, I will use the rowcount method.

Thanks again!