Re: Performance problem on delete from for 10k rows. May

Hi,

I'm using ibatis. But in this particular case the sql statement come
from a plain ascii file and it's run by the Ibatis ScriptRunner class.
Beside the fact this class come from ibatis framework it's just plain
sql connection (I'm I wrong???). Just to be sure, here is the code from
the class. I must say that i run script that contains create table,
alter table, insert statements with the same runner.

If I wrong please tell me .. I like to be wrong when the result is
eliminating a misunderstanding from my part :-)

Thanks for your help!

/David

public void runScript(Connection conn, Reader reader)
throws IOException, SQLException {
StringBuffer command = null;
try {
LineNumberReader lineReader = new LineNumberReader(reader);
String line = null;
while ((line = lineReader.readLine()) != null) {
if (command == null) {
command = new StringBuffer();
}
String trimmedLine = line.trim();
if (trimmedLine.startsWith("--")) {
println(trimmedLine);
if (log.isDebugEnabled()) {
log.debug(trimmedLine);
}
} else if (trimmedLine.length() < 1 ||
trimmedLine.startsWith("//")) {
//Do nothing
} else if (trimmedLine.endsWith(";")) {
command.append(line.substring(0,
line.lastIndexOf(";")));
command.append(" ");
Statement statement = conn.createStatement();

println(command);
if (log.isDebugEnabled()) {
log.debug(command);
}

boolean hasResults = false;
if (stopOnError) {
hasResults = statement.execute(command.toString());
} else {
try {
statement.execute(command.toString());
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
}
}

if (autoCommit && !conn.getAutoCommit()) {
conn.commit();
}

ResultSet rs = statement.getResultSet();
if (hasResults && rs != null) {
ResultSetMetaData md = rs.getMetaData();
int cols = md.getColumnCount();
for (int i = 0; i < cols; i++) {
String name = md.getColumnName(i);
print(name + "\t");
}
println("");
while (rs.next()) {
for (int i = 0; i < cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
println("");
}
}

command = null;
try {
statement.close();
} catch (Exception e) {
// Ignore to workaround a bug in Jakarta DBCP
// e.printStackTrace();
}
Thread.yield();
} else {
command.append(line);
command.append(" ");
}
}
if (!autoCommit) {
conn.commit();
}
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
log.error("Error executing: " + command, e);
throw e;
} catch (IOException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
log.error("Error executing: " + command, e);
throw e;
} finally {
conn.rollback();
flush();
}
}

Dave Cramer wrote:

>
>
> David Gagnon wrote:
>
>> Hi All,
>>
>> I rerun the example with the debug info turned on in postgresl. As
>> you can see all dependent tables (that as foreign key on table IC)
>> are emptied before the DELETE FROM IC statement is issued. For what
>> I understand the performance problem seem to came from those selects
>> that point back to IC ( LOG: statement: SELECT 1 FROM ONLY
>> "public"."ic" x WHERE "icnum" = $1 FOR UPDATE OF x). There are 6 of
>> them. I don't know where they are comming from. But if I want to
>> delete the content of the table (~10k) it may be long to those 6
>> selects for each deleted rows. Why are those selects are there ?
>> Are those select really run on each row deleted?
>
>
> You are using hibernate. Hibernate is generating them to lock the tables.
>
>>
>>
>> I'm running version 7.4.5 on cygwin. I ran the same delete from
>> pgAdminIII and I got 945562ms for all the deletes within the same
>> transaction .. (so I was wrong saying it took less time in
>> PgAdminIII... sorry about this).
>>
>> Do you have any idea why those 6 selects are there?
>
>
> Hibernate
>
>>
>> Maybe I can drop indexes before deleting the content of the table. I
>> didn't planned to because tables are quite small and it's more
>> complicated in my environment. And tell me if I'm wrong but if I
>> drop indexed do I have to reload all my stored procedure (to reset
>> the planner related info)??? Remember having read that somewhere..
>> (was it in the Postgresql General Bit newletter ...anyway)
>>
>> Thanks for your help I really appréciate it :-)
>>
>> /David
>>
>> LOG: duration: 144.000 ms
>> LOG: statement: DELETE FROM YN
>> LOG: duration: 30.000 ms
>> LOG: statement: DELETE FROM YO
>> LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" =
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."yn" x WHERE "ynyotype"
>> = $1 AND "ynyonum" = $2 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."yo" x WHERE "yotype" =
>> $1 AND "yonum" = $2 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."yr" x WHERE "yryotype"
>> = $1 AND "yryonum" = $2 FOR UPDATE OF x
>> LOG: duration: 83.000 ms
>> LOG: connection received: host=127.0.0.1 port=2196
>> LOG: connection authorized: user=admin database=webCatalog
>> LOG: statement: set datestyle to 'ISO'; select version(), case when
>> pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
>> getdatabaseencoding() end;
>> LOG: duration: 2.000 ms
>> LOG: statement: set client_encoding = 'UNICODE'
>> LOG: duration: 0.000 ms
>> LOG: statement: DELETE FROM IY
>> LOG: duration: 71.000 ms
>> LOG: statement: DELETE FROM IA
>> LOG: duration: 17.000 ms
>> LOG: statement: DELETE FROM IQ
>> LOG: duration: 384.000 ms
>> LOG: statement: DELETE FROM IC
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."iq" x WHERE "iqicnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ia" x WHERE "iaicnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumo"
>> = $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."iy" x WHERE "iyicnumr"
>> = $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."il" x WHERE "ilicnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."ic" x WHERE "icnum" =
>> $1 FOR UPDATE OF x
>> LOG: statement: SELECT 1 FROM ONLY "public"."bd" x WHERE "bdicnum" =
>> $1 FOR UPDATE OF x
>> LOG: duration: 656807.000 msMichael Fuhr wrote:
>>
>>
>>
>>
>>
>> -----------------------
>> DELETE FROM BM;
>> DELETE FROM BD;
>> DELETE FROM BO;
>> DELETE FROM IL;
>> DELETE FROM YR;
>> DELETE FROM YN;
>> DELETE FROM YO;
>> DELETE FROM IY;
>> DELETE FROM IA;
>> DELETE FROM IQ;
>> DELETE FROM IC;
>>
>> Michael Fuhr wrote:
>>
>>> On Tue, Mar 15, 2005 at 04:24:17PM -0500, David Gagnon wrote:
>>>
>>>
>>>
>>>> Il get this strange problem when deleting rows from a Java
>>>> program. Sometime (For what I noticed it's not all the time) the
>>>> server take almost forever to delete rows from table.
>>>>
>>>
>>>
>>>
>>> Do other tables have foreign key references to the table you're
>>> deleting from? If so, are there indexes on the foreign key columns?
>>>
>>> Do you have triggers or rules on the table?
>>>
>>> Have you queried pg_locks during the long-lasting deletes to see
>>> if the deleting transaction is waiting for a lock on something?
>>>
>>>
>>>
>>>> I rememeber having tried to delete the content of my table (IC) from
>>>> PgAdminIII and I took couples of seconds!!! Not minutes.
>>>>
>>>
>>>
>>>
>>> How many records did you delete in this case? If there are foreign
>>> key references, how many records were in the referencing tables?
>>> How repeatable is the disparity in delete time? A single test case
>>> might have been done under different conditions, so it might not
>>> mean much. No offense intended, but "I remember" doesn't carry as
>>> much weight as a documented example.
>>>
>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>