• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

exist check?

 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 rs.next() I assume its good. Is there a better of faster way?
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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,
Avi.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In not interested in speed. Only in doing the query in the simplest fashion.

Thanks.
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1821
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well ID is the primary key. You all have given me some good ideas. Ill try the SELECT null FROM technique.
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic