FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi Patrick,

Frediano is our ODBC expert, I'm very much not...but...

> does FreeTDS ODBC support of only single-record fetch have a performance 
> impact?  Or is the underlying TDS layer doing and caching a multi-record
fetch 
> and feeding single records to the ODBC layer, so that performance is not
much 
> different?

Conceptually, it works like this:

The server processes the passed query, and will send all the returned data
back to the client in one block of data.
This block consists of a "metadata" or "results" token, which describes the
result set; zero or more "row" tokens - the data; an "end" token signifying
the end of the result set. 

Although this block of data may be divided up into packets at a network
protocol level, and is divided up into packets at a TDS protocol level, all
this data is effectively "on the wire" as soon as the query is processed by
the server.

our "fetch" API functions, in the ODBC API or in our other API's, simply
read a row token from the wire and return the values to the calling program.
So, fetches do not involve a "client-server round-trip" and there should be
no significant performance difference between a singleton fetch and a multi
row fetch.

If multi-row fetches are not currently suppported in the ODBC API ( I'm not
owning up to this, as I don't know the ODBC code ), it is probably only a
matter that this hasn't been coded as yet. 
Another API which I know much better - ct-library - *does* support multi-row
or array fetches. In this API you bind an array of host variables to a
returned column, and set a flag saying how many rows you want returned by
each call to the fetch API function. 
The call to the fetch function does the rest, reading the required number of
rows from the wire and populting the host program arrays accordingly.

I hope I've got this right. I'm sure Freddy will correct me where I'm wrong.


Notwithstanding, I hope this helps....


Bill

> -----Original Message-----
> From: Patrick Surry [SMTP:pds AT quadstone.com]
> Sent: Friday, February 13, 2004 1:11 PM
> To:   freetds AT lists.ibiblio.org
> Subject:      [freetds] Multi-record SQLFetch in ODBC layer?
> 
> Now, I'm no expert on ODBC but have been trying to use FreeTDS ODBC driver
> on 
> HPUX to allow some inhouse ODBC-aware data-analysis s/w to pull data from
> MSSQL.
> 
> I've successfully installed and tested the driver with iODBC
> driver-manager 
> (eg. freetds tests, iodbc tests and perl DBI:ODBC interface all work
> fine).
> 
> However, our s/w tries to use multi-record fetch for performance reasons
> and 
> gets confused about the warning (rather than error) generated by FreeTDS
> which 
> apparently only supports single-record fetch at the ODBC layer (one of our
> 
> developers has explained with reference to the FreeTDS source code below).
> Our 
> engineers will make a patch to adapt to the FreeTDS ODBC driver, but I
> have a 
> couple of questions that I thought someone on this list might be able to
> answer:
> 
> - is our use of multi-record fetch via ODBC a 'non-standard' practice (and
> 
> hence why it's not currently supported)?
> 
> - does FreeTDS ODBC support of only single-record fetch have a performance
> 
> impact?  Or is the underlying TDS layer doing and caching a multi-record
> fetch 
> and feeding single records to the ODBC layer, so that performance is not
> much 
> different?
> 
> Regards,
> Patrick
> 
>  >> Well, here is the culprit, the code in FreeTds src/odbc/odbc.c
>  >> for SQLSetStmtAttr when passing in our request to block fetch
>  >> at 24756 records looks like the following:
>  >>
>  >>        case SQL_ATTR_ROW_ARRAY_SIZE:
>  >>                assert(stmt->ard->header.sql_desc_array_size == 1);
>  >>                if (stmt->ard->header.sql_desc_array_size != ui) {
>  >>                        odbc_errs_add(&stmt->errs, "01S02", NULL,
> NULL);
>  >>                        ODBC_RETURN(stmt, SQL_SUCCESS_WITH_INFO);
>  >>                }
>  >>                stmt->ard->header.sql_desc_array_size = ui;
>  >>                break;
>  >>
>  >> The value we are passing in is mapped to 'ui'.
>  >> As you can see their implementation hardwires the fetch to be just
>  >> s single record at a time. Because their code returns a warning and
>  >> not an error, our code thinks its able to fetch 24576, atcually
>  >> processes just a single record at a time but is under the
>  >> misapprehension that its really dealing in blocks of 24576 hence
>  >> why only your first record is populated with data, the rest wuth
>  >> nulls and why it takes a long time to execute.
> 
> 
> ...
> 
>  >> Now I can fix our code to query what the setting of
> ATTR_ROW_ARRAY_SIZE
>  >> is after we set it in order to workaround this, but we will still be
>  >> left with this implementation of FreeTDS ODBC driver not supporting
>  >> block fetches. The underlying TDS library does support this and I
>  >> don't understand yet why the ODBC driver cannot (there is another
>  >> setting to do with block inserts which is supported).
> 
> 
> ...
> 
>  >> So I could fix our driver to cope with FreeTDS ODBC driver, but
>  >> I'd like to see the FreeTDS ODBC driver be block fetch enabled.
> 
> 
> -- 
> _________________________________________________________________________
> Patrick Surry, PhD  Tel 617 457 5200 Fax 617 457 5299   www.quadstone.com
> 
> 
> -- 
> _________________________________________________________________________
> Patrick Surry, PhD  Tel 617 457 5200 Fax 617 457 5299   www.quadstone.com
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

==============================================================================

If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose,
copy, retain or redistribute it.

Click here for important additional terms relating to this e-mail.
     <http://www.ml.com/email_terms/>

==============================================================================