----- Original Message -----
From: "Amir Michail" <[EMAIL PROTECTED]>
To: "Derby Discussion" <[email protected]>
Sent: Monday, October 20, 2008 12:15 AM
Subject: How to efficiently check for existence of a row?
Hi,
Given some condition, how do you efficiently check whether there is at
least one row that satisfies that condition?
count(*) is slow.
Hi Amir
Looking at your posts, you seem to be relying on stat (aggregate functions)
for everything... think about normal SQL like this...
String sSql = "SELECT id FROM " + dbTable + " ORDER BY id DESC";
Statement stmt= con.createStatement();
stmt.setMaxRows(1);
ResultSet resultSet = stmt.executeQuery(sSql);
return resultSet;
So its the max ID because its sorted and it only returns one record...
Then also read up on "forward only" recordsets... normally the default and
are very fast.
And you MUST index the stuff... in this case the ID.
Also read up on dB normalization... if you get that, you'll start building
fast dB's regardles of size.
Because you also cant just index every field... that also slows things down,
on entry.
Try avoid making the dB bring 20 million records into memory and then
counting the things...
In the above because its indexed on ID and you bringing back only one
record... its always fast.
If the dB is normalized correctly, then what you will find is that WHEREs
tend to act on indexed data.
.... is what I'm trying to show you
Its always possible to make terrible designs no matter what dB engine you
using... always think about whats happening to memory as the dB gets
larger...
For that you have to learn dB normalization and about indexes...
Good Luck...
---------------------------------------------------------------------------
HARBOR : http://www.kewlstuff.co.za/index.htm
The most powerful application server on earth.
The only real POJO Application Server.
See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
---------------------------------------------------------------------------
If you cant pay in gold... get lost...
http://coolharbor.100free.com/debt/usadebt.htm
