Sent: Friday, September 20, 2002 7:18
PM
Subject: [freetds] RE: slow , very
slow
I didn't mean to anger you and appreciate your time
responding.
However you response does not help matters
any. I did perform
Querys on more rows and the result
was extremely slow a 10 thousand
Row query took 1600
seconds. The same query across the same network
to
mysql took 3 seconds. The same query local to the windows box took
3 seconds. I understand mssql is slower, but not by
that much.
I'm afraid the speed difference between my three test
Tell me one the freetds interface has problems or two
it's a freetds configuration problem.
I'm looking for solid help here guys. Is there anything I can
check
Configuration wise or an alternate connection
method.
Thanks
-----Original Message-----
From:
Lowden, James K [mailto:LowdenJK AT bernstein.com]
Sent: Friday, September 20, 2002 4:50 PM
To: TDS Development Group
Subject:
[freetds] RE: slow , very slow
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.
---
You are currently subscribed to
freetds as: [david.walgamotte AT wild.net] To unsubscribe, forward this message
to $subst('Email.Unsub')
---
You are
currently subscribed to freetds as: [kseixas AT uol.com.br]
To unsubscribe,
forward this message to $subst('Email.Unsub')
