db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Johnny Kewl" <j...@kewlstuff.co.za>
Subject Re: How to efficiently check for existence of a row?
Date Mon, 20 Oct 2008 07:15:21 GMT

----- Original Message ----- 
From: "Amir Michail" <amichail@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
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



Mime
View raw message