No subject
Fri May 2 19:13:14 EDT 2003
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')
------_=_NextPart_001_01C260F3.993D8D24
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: [freetds] RE: slow , very slow</TITLE>
</HEAD>
<BODY>
<BR>
<P><FONT SIZE=3D2> I didn't mean to anger you and appreciate your =
time responding.</FONT>
<BR><FONT SIZE=3D2>However you response does not help matters any. I =
did perform </FONT>
<BR><FONT SIZE=3D2>Querys on more rows and the result was extremely =
slow a 10 thousand</FONT>
<BR><FONT SIZE=3D2>Row query took 1600 seconds. The same query across =
the same network </FONT>
<BR><FONT SIZE=3D2>to mysql took 3 seconds. The same query local to the =
windows box took </FONT>
<BR><FONT SIZE=3D2>3 seconds. I understand mssql is slower, but not by =
that much.</FONT>
</P>
<P><FONT SIZE=3D2>I'm afraid the speed difference between my three =
test</FONT>
<BR><FONT SIZE=3D2>Tell me one the freetds interface has problems or =
two </FONT>
<BR><FONT SIZE=3D2>it's a freetds configuration problem.</FONT>
</P>
<P><FONT SIZE=3D2>I'm looking for solid help here guys. Is there =
anything I can check</FONT>
<BR><FONT SIZE=3D2>Configuration wise or an alternate connection =
method.</FONT>
</P>
<P><FONT SIZE=3D2>Thanks</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Lowden, James K [<A =
HREF=3D"mailto:LowdenJK at bernstein.com">mailto:LowdenJK at bernstein.com</A>=
] </FONT>
<BR><FONT SIZE=3D2>Sent: Friday, September 20, 2002 4:50 PM</FONT>
<BR><FONT SIZE=3D2>To: TDS Development Group</FONT>
<BR><FONT SIZE=3D2>Subject: [freetds] RE: slow , very slow</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>From: Walgamotte, David [<A =
HREF=3D"mailto:david.walgamotte at wild.net">mailto:david.walgamotte at wild.n=
et</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: September 20, 2002 4:15 PM</FONT>
</P>
<P><FONT SIZE=3D2>Why is free tds so slow ? </FONT>
<BR><FONT SIZE=3D2>Processing 584 rows: MYSQL takes 0.905979990959 =
seconds </FONT>
<BR><FONT SIZE=3D2>Processing 53 rows: MS SQL takes 1.26668703556 =
seconds </FONT>
</P>
<P><FONT SIZE=3D2>David, </FONT>
</P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>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? </FONT></P>
<P><FONT SIZE=3D2>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. =
</FONT></P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>How does one separate the speed of FreeTDS from the =
speed of SQL Server? </FONT>
</P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>The critical aspects of the TDS protocol's speed =
would be:</FONT>
</P>
<P><FONT SIZE=3D2>1. Round trips between =
server and client, affected by latency.</FONT>
<BR><FONT SIZE=3D2>2. Overhead, defined =
as non-data over transmitted bytes. </FONT>
</P>
<P><FONT SIZE=3D2>The second comes into play during bcp and =
transmitting the result set from a select. </FONT>
</P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>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. =
</FONT></P>
<P><FONT SIZE=3D2>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". </FONT></P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>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. </FONT></P>
<P><FONT SIZE=3D2>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. =
</FONT></P>
<P><FONT SIZE=3D2>Welcome to FreeTDS. </FONT>
</P>
<P><FONT SIZE=3D2>--jkl</FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=3D2>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.</FONT></P>
<BR>
<BR>
<P><FONT SIZE=3D2>---</FONT>
<BR><FONT SIZE=3D2>You are currently subscribed to freetds as: =
[david.walgamotte at wild.net] To unsubscribe, forward this message to =
$subst('Email.Unsub')</FONT></P>
</BODY>
</HTML>
------_=_NextPart_001_01C260F3.993D8D24--
More information about the FreeTDS
mailing list