It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes exist check? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "exist check?" Watch "exist check?" New topic

exist check?

Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

If I want to know if something exist in a table. For instance if there is an entry with column ID = 5, what type of query should I perform?

Now I to a "SELECT ID FROM table WHERE ID = 5;"

and if I assume its good. Is there a better of faster way?
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1138

Mr. Gilbert,
You asked:

Is there a better or faster way?

That would depend on what you mean by "better". As far as I'm concerned, "better" doesn't necessarily mean "faster".

Regarding "faster", that would depend on the database you are using, since each one has their own "tricks" for making a query run faster.

If you are using an Oracle database, then -- in order to make it "faster" -- I would create an index on the ID column and then do the following:
[Note that this is uncompiled and untested code and only used to help get you started.]

Good Luck,
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

I agree, PreparedStatements and Connection Pooling is about as far as I'd go towards streamlining the operation. I haven't profiled it, but you'd probably have to do about a bazzilion of them to get an effect on your app, so even a 50% improvement wouldn't be worth the effort compared to optimisations you may be able to perform elsewhere.

Note that I'm not[/i] telling you to optimise elsewhere, I'm saying [b]if performance becomes a problem and you need to optimise, subject to output from a profiling tool, you should probably concentrate your efforts elsewhere. Is that a suitable disclaimer?
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

The point was I am asing for ID but I dont need the ID, I only need to know its not NULL. Is there a more appropriate query besides asking for it to return to me the ID, when all i need is a yes or no reply?
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3749

Reviewing the posts I'm thinking your question has far more to do with what you think the performance impact of the projection will be, and not caring at all about indices.

Ironically, in this situation indices are more important, but lets skip that discussion.

Generally, 'no' your result is about as good as it gets for speed. You could return 'null' as someone suggested or as I prefer 'count(*)' which returns 0 if there was a result and 1 otherwise (for unique keys), but ultimately I don't see any noticeable performance difference in returning an int versus these other solutions. This is the kind of thing people tend to think optimizing will help and it never does. You're all ready returning a result set, adding an int isn't going to change much except in extreme cases.

BTW, the reason for null or count(*) solutions have nothing to do with performance to me, they are more for security, for example if the id was a social security number, avoiding passing it around a network when its not needed is a good thing.
[ November 01, 2005: Message edited by: Scott Selikoff ]

[OCA 8 Book] [Blog]
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

In not interested in speed. Only in doing the query in the simplest fashion.

Michael Matola
Ranch Hand

Joined: Mar 25, 2001
Posts: 1785
I am not necessarily recommending this, but you *can* just select a constant -- whatever constant you like.

select 'X' from ...
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

Well ID is the primary key. You all have given me some good ideas. Ill try the SELECT null FROM technique.
I agree. Here's the link:
subject: exist check?
It's not a secret anymore!