Re: A question about update and insert/delete

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:lI2dnb4oMaeXF57fRVn-hg_at_comcast.com...
>
> "Amardeep Verma" <addverma_at_gmail.com> wrote in message
> news:431a4ee7.0502040203.1294d80a_at_posting.google.com...
>> Hi,
>> We have a piece of JAVA code, which does heavy Insert or update.
>>
>> We have found out that a single update with a single WHERE clause
>> (the table is indexed according to the Column given in where clause),
>> takes more time compared to a DELETE + INSERT statement.
>>
>> Can someone please shed light on this? Therotically speaking,
>> both the operations(INSERT & DELETE) should take roughly the same
>> amount of time.
>>
>> Let us say that INSERT+DELETE operation takes 10 seconds, than
>> update takes 20-30 seconds. We are using bulk updates, using
>> Statement.addBatch() statements.
>> The Database is Oracle 9i
>> _________________________________
>> Regards,
>> Amardeep Verma
> Also are you using bind variables? (parameterized)
> Jim
>
>

Yes, I would use PreparedStatement p = Connection.prepareStatement(String) then use p.setString(int, String) and whatnot, especially if you are doing the same SQL over and over. You might want to check your indexes as well, if you have an index on the key and its under a million rows (depending on the server ofcourse), it should be pretty snappy.

DR: I would imagine if the session is updating all of the columns instead of just one, and there are indexes on those other columns, that would slow it down a bit too. I've never had it happen to me though, at least not that I'm aware of :)

-- 
Tom Dyess
OraclePower.com