>You could use EXPLAIN to see if there is a different query plan, but I'd >bet there isn't. * will generally be slower, just because you usually >won't need EVERY column. If you can specify only certain columns, that >will save you some time.
This will "save you some time" in the sense that the program may execute faster at runtime. But in those situations where I'm going to need everything anyway, I feel no guilt about using the asterisk. For example, the asterisk can afford a measure of dynamicity when writing a data access layer, ORM, or the like. If the schema changes, a listing of every column can be invalidated, but the asterisk cannot. I can imagine architectures in which tables are mapped to structures on a one-to-one basis, with all fields included, and in that case use of the asterisk could be the difference between "success" and "success with a side of carpal tunnel syndrome." >I don't know about SQLite, but in all SQL courses you learn that you >should NEVER use the asterisk. Generally, I am skeptical about absolute statements (e.g. "always" and "never"). Also, I am skeptical of commentaries that attempt to embellish the specification of a language with points-of-advice expressed in natural langauge, e.g. the old saw that "C has a preprocessor but you shouldn't use it" (or pretty much any statement ever made by Herbert Schildt). This sort of hand-holding is fine in the semster of instruction, perhaps, but after that I really have no use for it. The specification includes the asterisk and I refuse to feel guilt for using it. The "dark underbelly" of these commonsensical aphorisms is that they end up being immortalized into corporate standards. I am sure that somewhere, some poor programmer is burning up his keyboard entering thousands of column names needlessly because some architect-type at his company heard that the asterisk was bad. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kristoffer Danielsson Sent: Wednesday, October 28, 2009 12:51 PM To: [email protected] Subject: Re: [sqlite] SELECT * vs SELECT columns ? I don't know about SQLite, but in all SQL courses you learn that you should NEVER use the asterisk. The asterisk is merely there to let you quickly view data _manually_. > Date: Wed, 28 Oct 2009 16:02:01 +0200 > From: [email protected] > To: [email protected] > Subject: Re: [sqlite] SELECT * vs SELECT columns ? > > I would expect there to be a speed and memory performance *impact* if > the result set contains columns other than the three specified ones, > since obviously the library will need to allocate more memory to hold > the extra data. > > On 10/28/2009 03:52 PM, Pete56 wrote: > > I am searching across two joined tables and am interested in a few > > parameters: > > > > SELECT a.first a.third b.first FROM a JOIN b ON a.RowID = b.RowID WHERE > > value = :value > > > > Is there any speed or memory performance improvement by using SELECT *, > > rather than SELECT<specific columns> ? > > > > If I know there will only be one item (unique :value) selected (LIMIT 1) can > > I make any performance improvements ? > > > > -- > Cu stima, > Mihai Limbasan > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _________________________________________________________________ Nya Windows 7 - Hitta en dator som passar dig! Mer information. http://windows.microsoft.com/shop _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users The information contained in this e-mail is privileged and confidential information intended only for the use of the individual or entity named. If you are not the intended recipient, or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any disclosure, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender and delete any copies from your system. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
