Skip to Content.
Sympa Menu

freetds - RE: slow , very slow

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Walgamotte, David" <david.walgamotte AT wild.net>
  • To: "'TDS Development Group'" <freetds AT franklin.metalab.unc.edu>
  • Subject: RE: slow , very slow
  • Date: Fri, 20 Sep 2002 17:18:01 -0500

Title: RE: [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')




Archive powered by MHonArc 2.6.24.

Top of Page