This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.]
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?
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?
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 ]