FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Gad Hayisraeli [mailto:gadh AT vmanage.com]
> Sent: February 3, 2003 11:07 AM
> > You compared different database servers on different 
> operating systems, and
> > concluded it was "obviously" FreeTDS.  When I pointed that 
> out, you posted
> > no response.  When I suggested a plan better suited to yielding the
> > information you seemed to want, you answered me with refutation.
> 
> ok , maybe i did not understand what you offer to prove that 
> its not the freetds that slows down. please explain again
> how your test can prove this

Gad, 

If you want to measure the network protocol (or its implementation), you
have to control for the other elements.  No news there.  

My suggestion was to first measure server-bound insertions with various
table widths, then insertions from the client.  More information would be
provided if other implementations were included, and/or bcp operations.
Also, it's important to control for, or at least note, the TDS protocol
version, and to know the speed of the underlying network.  TDS won't go any
faster than tcp, although in my experience it can exceed 90% of tcp.  

As I said, if you conduct a reasonably objective test, and provide enough
information that someone could reproduce it, and find a problem specific to
FreeTDS, I'd be very interested.  In fact, if your results are reproducible
and demonstrate a significant problem in FreeTDS, I'd be astonished if we
didn't fix it very quickly.  Nothing turns a geek's propeller like an
isolated reproducible performance problem.  

Here's an experiment I know would draw attention.  As I understand you,
you're concerned with row-by-row insertions.  If you're actually interested
in inserting more rows at a time, replace "one" with "N" for some
interesting value of N.  

Assertion:
        Tables with more than 14 columns are an order of magnitude slower
than narrower tables.  As column count grows from 6 to 26, rows/second drops
by a factor of 100.  

Hypothesis:
        TDS protocol imposes or incurs overhead, making wide tables slower.
FreeTDS has a bug or design flaw that participates in or causes the
slowness.

Tests:
        1.  Determine the performance of the server alone, independent of
the network. Insert data into a temporary table.  Write a stored procedure
to read the table a row at a time, insert the data into a real table, and
time the results in rows/second.  Vary the width of the table: 6, 12, 18, 24
columns.  

        2.  Try freebcp for the same tables.  Measure rows/second.  Adjust
TDSVER and repeat.  How do these compare with results from #1?  How does the
best data rate compare with raw network throughput (say, ftp)?  Try vendor's
bcp utilities.  How do these compare?  

        3.  Try row-at-a-time insertions from the client for each table,
using FreeTDS, Microsoft, and Sybase libraries.  Adjust TDSVER and repeat,
if possible (Microsoft doesn't support that, but Sybase does).  

        It would be good to repeat all the above tests several times and
take the averages.  It's especially important to repeat the tests that
specifically support your conclusion(s).  

Report:
        Tabulate the results.  Each row of the report should document one
combination of client library and TDS version, showing the results for the
various table widths.  Sort the rows by average throughput across table
widths.  

Expectations:
        1.  Test #1 is a baseline, but will not be the fastest.  Row-by-row
insertions incur high server transaction overhead.  
        2.  Slowest will be row-by-row insertions from the client,
irrespective of client library or TDS version, due to network latency.  
        3.  BCP operations -- again, regardless of vendor -- will be
fastest.  
        4.  Wide rows take longer, regardless of actual column count.
Network operations will not be measureably *more* affected by wider rows
unless bcp throughput approaches network throughput.  


Data from the above experiment could be analyzed from serveral directions:
vendor (including us), transaction size, TDS version, network efficiency.
I'm sure the results would be interesting, even if they don't reveal a bug.


Regards, 

--jkl


The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.