Win a copy of Head First Android this week in the Android forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

exist check?

 
Ranch Hand
Posts: 1170
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In not interested in speed. Only in doing the query in the simplest fashion.

Thanks.
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Hibernate Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic