Skip to Content.
Sympa Menu

freetds - [freetds] patch to improve performance selecting data

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Steve Brown (FICC IT)" <Steven.Brown AT macquarie.com>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] patch to improve performance selecting data
  • Date: Wed, 31 Mar 2010 12:33:45 +0100

Hi, I recently did a small test of performance on freetds library
against Sybase ASE 15.0.3 supplied client-libs on RHEL5.4 x64; and found
freetds to be significantly slower.

After running through callgrind, it seemed that tdsdump_log was the
majority of the difference... spending about 23% of the program time in
this function even though the logging was turned off... it was basically
because this function was called 52M times so the call-overhead was
significant.

Here is a patch to resolve this issue... it uses a macro to check
write_dump variable (renamed to tds_write_dump) before making the
function call.

My timing tests (in seconds, smaller is better) are below; averaged over
5 runs. "real" time can probably be ignored since it includes the
elapsed time spent waiting for my Sybase server to respond.

before patch:

Select a varchar column from a big table:
what, Sybase 15.0.3, freetds-0.82
time real, 6.3s, 5.8s
time user, 0.90s, 1.69s (quite a lot slower)
time sys, 0.50s, 0.47s

Select varchar,varchar,int columns from a big table:
what, Sybase 15.0.3, freetds-0.82
time real, 9.1s, 9.8s
time user, 1.53s, 2.41s (quite a lot slower)
time sys, 0.89s, 0.98s

After patch:

Select a varchar column from a big table:
what, Sybase 15.0.3, freetds-0.82
time real, 6.3s, 6.2s
time user, 0.90s, 1.21s (better than unpatched)
time sys, 0.50s, 0.51

Select varchar,varchar,int columns from a big table:
what, Sybase 15.0.3, freetds-0.82
time real, 9.1s, 9.2s
time user, 1.53s, 1.59s (better than unpatched and almost same speed as
sybase)
time sys, 0.89s, 1.06s

I believe there are other opportunities for speeding up tds but I have
not made these as I don't have a good enough understanding of the code,
nor time to test... E.g:

1.
Caching (if possible) the srctype and desttype variables in
buffer_transfer_bound_data... I'm not familiar with the code; but it
would seem sensible to do those calls (desttype =
_db_get_server_type(..), srctype = tds_get_conversion_type(..) ) just
once for the current result-set and store the results inside the
row->resinfo->columns[i] object.
2.
use num->array[0] = 0 instead of memset in tds_get_data()
although this may be a security concern (not clearing memory).

Steve






The information contained in this email is confidential. If you are not the
intended recipient please tell us immediately by return e-mail and
permanently delete the document and all attachments. You are not authorised
to use the information in this email in any way.

The Macquarie Group does not guarantee the integrity of any emails or
attached files and are not responsible for any changes made to them by any
other person. Information in this email is believed to be reliable but its
completeness and accuracy is not guaranteed. The views or opinions expressed
are the author's own and may not reflect the views or opinions of Macquarie.

The Macquarie Group includes Macquarie Bank Limited ("MBL"), Macquarie
Capital Funds (Europe) Limited ("MCFEL"), Macquarie Capital (Europe) Limited
("MCEL") and Macquarie Bank International Limited (“MBIL”). MBL (London
Branch) is registered as a branch of a foreign company under number BR002678,
whose registered office is at Level 25, Citypoint, 1 Ropemaker Street,
London, EC2Y 9HD, United Kingdom.

MCFEL (company number: 03976881) and MCEL (company number 03704031) are
registered in England and Wales at Level 25, Citypoint, 1 Ropemaker Street,
London, EC2Y 9HD, United Kingdom. MBIL (company number: 06309906) is
registered in England and Wales at Level 25, Citypoint, 1 Ropemaker Street,
London, EC2Y 9HD, United Kingdom. MBL (Firm No. 170934), MCEL (Firm No.
193905), MCFEL (Firm No. 195652) and MBIL (Firm No. 471080) are authorised
and regulated by the Financial Services Authority.

Attachment: patch
Description: patch



  • [freetds] patch to improve performance selecting data, Steve Brown (FICC IT), 03/31/2010

Archive powered by MHonArc 2.6.24.

Top of Page