Skip to Content.
Sympa Menu

freetds - RE: slow , very slow

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: "'TDS Development Group'" <freetds AT franklin.metalab.unc.edu>
  • Subject: RE: slow , very slow
  • Date: Fri, 20 Sep 2002 17:49:57 -0400


From: Walgamotte, David [mailto:david.walgamotte AT wild.net]
Sent: September 20, 2002 4:15 PM

Why is free tds so slow ?
Processing 584 rows: MYSQL takes 0.905979990959 seconds
Processing 53 rows: MS SQL takes 1.26668703556 seconds

David,

You must be kidding. I hardly know where to start. I probably should spend
my time doing something productive instead, but this might be fun. I hope
it's what you signed up for.

To start with, is your clock really precise to 0.000000000001 seconds? Or
are we really looking at 0.91 and 1.27 seconds, given 30 ms resolution?

With so few rows, you don't know if 80% of the time was query overhead. It
would be silly to think that because 53 rows take 1 second, then 530 rows
would take 100 seconds.

>From your subsequent mail, it's clear your question doesn't match your test
at all. You asked why "free tds" is slow, but you're testing end-to-end
throughput to PHP. You might as well ask "Why is PHP slower talking to SQL
Server?" It would be as accurate.

How does one separate the speed of FreeTDS from the speed of SQL Server?

I would be very surprised if any aspect of TDS (free or non) impedes the
flow of data to/from the server, except during large data transfers under
optimal conditions. The server has to store/retrieve the data; it hardly
ever waits for the network. When the network does become the bottleneck, I
have measured TDS speeds very close to Ethernet's TCP capacity.

The critical aspects of the TDS protocol's speed would be:

1. Round trips between server and client, affected by latency.
2. Overhead, defined as non-data over transmitted bytes.

The second comes into play during bcp and transmitting the result set from a
select.

You can examine the TDS protocol yourself, either by looking at the
documentation or reviewing the FreeTDS log files. You will find, if you do,
that the overhead of TDS to transmit a result set or bcp stream is very low.
I haven't measured it, but I would venture to say under 10%. In any case,
you won't find anything to explain an order of magnitude difference such as
your numbers seem to suggest.

On the other hand, it may be the case that TDS has a more complex
client-server interaction than MySQL does to set up a query. Because your
result set is so small, item #1 plays a greater role, not that I think it's
significant. You could use tdsdump to compare the network traffic of the
two systems.

To even begin to pretend to try to measure these two things seriously, you
would have to make sure you have a repeatable query that performs similarly
on both systems while logged in to the server, not transmitting data over
the network. Use isql or sqsh to do something like "insert into A select *
from B" where B has, say, 1 million rows. Obviously, you'll want to do
several trials, say 10, tossing out the highest and lowest and averaging the
others. I suspect the two will not exhibit similar timings. You will,
though, be able so say "Server X inserts x rows/second and server Y does y
rows/second".

Once the servers are on par, you could test the network thoughput. If you
use sqsh to "select * from B", routing its output to a file, or freebcp to
move the same million rows, you'll be able to measure rows/second delivered
over the network. Again, do several trials. You could express that as a
function of non-network throughput. Probably you'll see something < 100%,
that is, the network steals some time. (If server X does 10 rows/second on
the server and 9 rows/second to the client, that's 90% throughput. If total
data transmitted is anything like 80% of your Ethernet capacity, you've
reached the limit of your network.) But, when you compare *that* apple to a
MySQL apple, I doubt FreeTDS will fare badly. Anyway, I think it will be at
the margins.

The above describes a crude test, leaving out and assuming more than it
measures. You still wouldn't know where the bottlenecks really are, but
you'd have some idea what to expect from the two machines with your current
setup.

At this point, though, I suspect I've spent more time thinking about the
question than you have. If you are indeed earnest but ill-informed (which
is not a crime), if you indeed are trying to gauge the performance of the
two systems, please make some attempt to carry out a reasonable test and let
us know.

Welcome to FreeTDS.

--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 for certain accounts we do not accept
orders and/or instructions by e-mail, and for those accounts we will not be
responsible for carrying out such orders and/or instructions. Kindly refrain
from sending orders or instructions by e-mail unless you have confirmed that
we accept such communications for your account. Please also note that to
satisfy regulatory requirements we review the outgoing and incoming e-mail
correspondence of staff members serving certain functions.






Archive powered by MHonArc 2.6.24.

Top of Page