Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1297

Re: Odbc and client library/drivers

Show all headers | View raw

On Wed, 19 Sep 2012 21:16:52 +0200, Erland Sommarskog
<esquel@sommarskog.se> 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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-18 10:48 -0700
  Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-18 21:57 +0200
    Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-18 13:24 -0700
      Re: Odbc and client library/drivers George Neuner <gneuner2@comcast.net> - 2012-09-19 11:46 -0400
      Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-19 21:16 +0200
        Re: Odbc and client library/drivers Gene Wirchenko <genew@ocis.net> - 2012-09-19 13:14 -0700
          Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:41 -0700
            Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:45 -0700
              Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:56 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 14:00 -0700
        Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-19 13:26 -0700
          Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-19 23:44 +0200
            Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-20 01:50 -0700
              Re: Odbc and client library/drivers Erland Sommarskog <esquel@sommarskog.se> - 2012-09-20 23:31 +0200
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 01:18 -0700
                Re: Odbc and client library/drivers bradbury9 <ray.bradbury9@gmail.com> - 2012-09-21 02:57 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 03:10 -0700
                Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-21 03:14 -0700
  Re: Odbc and client library/drivers rja.carnegie@gmail.com - 2012-09-19 16:57 -0700
    Re: Odbc and client library/drivers björn lundin <b.f.lundin@gmail.com> - 2012-09-20 01:58 -0700

csiph-web