Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Gene Wirchenko Newsgroups: comp.databases.ms-sqlserver Subject: Re: Odbc and client library/drivers Date: Wed, 19 Sep 2012 13:14:35 -0700 Organization: A noiseless patient Spider Lines: 75 Message-ID: <2d9k58l3ufpbvim2n0ad7irssv0tp7frfq@4ax.com> References: <94298aed-7cbb-41f8-a155-c4632fc9db35@googlegroups.com> <4486daa1-575b-4713-9ca5-f4c8654eb9c8@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="c0a6a1dc41fc92eb7000e57afbd16211"; logging-data="3933"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+SEhZuFkovqgB16Fl5IutsRBWkpOvv0qA=" X-Newsreader: Forte Agent 4.2/32.1118 Cancel-Lock: sha1:cYfCw6JBgkJVN2BScXUuS4i1b5Y= Xref: csiph.com comp.databases.ms-sqlserver:1297 On Wed, 19 Sep 2012 21:16:52 +0200, Erland Sommarskog wrote: >björn lundin (b.f.lundin@gmail.com) writes: >>>But you should not use SELECT * in production code. >> >> Why? > >Because if the table changes, that changes the result set, and >"interesting" things can happen. > >If you only need three columns, but retrieve 30 you are degrading >performance in two way: >1) Extra chatter on the wire (particularly evil if you have LOB columns) >2) With a narrow result set, the optimizer might find a better query > plan, and for instance use a narrow result set. Occasionally, I want all columns regardless. This happens in one of two cases: 1) I am doing something DBAish (in which case, it is not production code), and 2) I am building a cursor out of several queries. The queries out of the raw tables will have columns specified, but queries out of a cursor usually are for all columns. >> The system is in Ada, and being in Ada it is strongly typed. That means >> we use some kind of struct, with members that corresponds to the fields >> of a table. We want to fill the whole struct, because i project >> adaptions, they might want to use a field that we do not use in >> standard. And then, we find the quality of the system increases a whole >> lot, if we get all the data out, instead of just getting the data we >> want rigth now. In what way is it better? It takes more memory, bandwidth, and time. >And then the question arises: is this column really used for anything? >Maybe you want to drop the column. Maybe you want to redefine its >semantics. But you find that it is virtually impossible to tell. My two exceptions are safe from this as both are temporary. I would hate to have to clean up code with select * in the way OP wants. It would be much harder to find references to columns. >> select a,b,c, from d >> are farr worse, since that tends to be >> >> get(a,varA); >> get(c,varC); >> get(b,varB); <--fail because we got B first. get() is miswritten then. I think your comment is wrong, too. Your bogus code corrected is likely: get(a,varA); get(c,varC); get(b,varB); <--fail because we got C first. ^ >> This is mostly a problem in migrating old sites, >> since the select * method gets it right. Read "hides the bug". >I have not programmed much with ODBC, but I'm somewhat skeptic that >this is a behaviour of the driver. Can you repro this with a C++ >program? Since you program in Ada, I assume that you have something >that sits between your Ada environment and ODBC, and this could be >the culprit. I agree with this. I think that there is some sloppy or limited code between the data and the app. Sincerely, Gene Wirchenko