Unreasonable long SQL statement execution

From: Stan Brown <stanb_at_netcom.com>
Date: Mon, 21 Nov 1994 21:23:41 GMT
Message-ID: <stanbCzMzFH.F5q_at_netcom.com>

        I have a ProC program intended to archive data from the database and then delete it from the database. It works fine except for one SQL statement that takes days to execute.

        Here is the basic schema:

        Master table with on key column

	16 table that are 1 to 1 on the master with the masters key
	could declared as a foreign key

	2 one to many tables.

	The program selects data fitting a time window into a temporary
	table consisting of the appropriate keys to backup/delete.


	(this works fine)



	Then it deletes one at a time from the subsidiary tables using
	a sql statement like:

		DELETE from table where key in
		(select key from temp_table)


	(this work fine)



	The it attempts to do the same thing on the master table.


	(this takes several orders of magnitude longer)



	Now I realize that since deletes from the master table have

to check the subsidiary tables for the key (because of data integrity (constraints) that this will take some time longer. I could almost be convinced that it's going to take 18 times as long, but what is happening is that it takes *much* longer than even that.

        Hint the Oracle task doing this (not the ProC program) is cpu bound during this time (It's the only significant thing running on the machine at the time).

        Any suggestions as to what is going on would be appreciated.

BTW we are running version 7 if that helps.

-- 
Stan Brown     stanb_at_netcom.com                                    404-996-6955
Factory Automation Systems
Atlanta Ga.
--
DOS ... is still a real mode only non-reentrant interrupt
handler, and always will be.                -Russell Williams

Received on Mon Nov 21 1994 - 22:23:41 CET