Skip to Content.
Sympa Menu

freetds - Re: [freetds] 0.91 freebcp performance

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: David Chang <dchang AT fsautomation.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] 0.91 freebcp performance
  • Date: Mon, 22 Jun 2015 11:47:11 -0700

Ray,

I don't use freebcp, but for Sybase bcp, it runs the fast bcp (non-logged) if you don't have any indexes on the table. Thus, for large tables, we usually drop the indexes, run the bcp, then create the indexes again.

However, if you are running freebcp and Sybase bcp on the same exact client and server with the same exact bcp import file to the same exact database table, I think you've uncovered a bug in freebcp.

Your table is very narrow (less than 100 bytes wide). You have very little data (5M rows). I would expect to insert this amount of data into Sybase in about a minute.

To test out the fast bcp versus slow bcp, I would create a new table with the same table structure (but no indexes) and test out freebcp against it.

DC

On 6/22/2015 10:10 AM, Ray Rankins wrote:
Thanks Matt,
I might expect some slight performance degradation compared to Sybase bcp (or
SQL Server bcp), but I'm seeing orders of magnitude degradation (1.5 minutes
versus 1.5 hours for 5 million rows).

The table is pretty simple - no large object types.
Mostly int and float fields. Largest char field is 7 characters.
There is one non-nulllable date field at the end which has a default -
freebcp didn't like that the file didn't contain a value for the last field,
but I worked around this using a format file or by making the last field
nullable.

CREATE TABLE [dbo].[test_table](
[val_geo] [char](5) NOT NULL,
[cd_wrsi_mdl] [smallint] NOT NULL,
[cd_geo_srce] [int] NOT NULL,
[cd_ppty_type_cpr] [char](1) NOT NULL,
[cd_mrtg_purp_altv] [char](4) NOT NULL,
[text_grth_multr_mol] [char](7) NOT NULL,
[cd_geo_type] [smallint] NULL,
[rate_grth_multr] [float] NULL,
[rate_std_dev_neg] [float] NULL,
[rate_std_dev_pstv] [float] NULL,
[dt_lst_updt] [date] default getdate()NULL
)

Initially, I was running freebcp from a Solaris host to SQL Server on
Windows, but then I tested Sybase bcp and freebcp both running on the same
Solaris client and importing into the same ASE server running on a Linux
host, so it was an apples to apples comparison between the 2.

The import file is a text file, so the flags I'm using are -c, -t, -r with
the -b to set a batch size of 10000
Also tried -f with a format file and there was no noticeable performance
difference (although there did appear to be a bug when using the format file
where seemed to ignore the -b option).

I don't have much access to the Sybase server to do too much monitoring, but
what I could see, it seemed like it was waiting on network I/O most of the
time.
Is there and easy way to tell of the BCP is using fast bcp versus fully
logged besides looking at what's being written to the log file?

-Ray

-----Original Message-----
From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
Matthew
Sent: Saturday, June 20, 2015 9:40 AM
To: FreeTDS Development Group
Subject: Re: [freetds] 0.91 freebcp performance

Hi Ray,

I did come across performance problems when compared to Sybase bcp but
those were mostly around text and image data types. Those problems
appeared to be fixed, in my testing, or at least greatly improved when I
tried a nightly from a few weeks ago.

What's the definition of the table you are using and which flags are you
using? Can you take a look inside the Sybase server and see what it's
waiting for when you use freetds and which packet size the connection is
using? I assume your comparison is from the same machine and it's not
the case that you're running the Sybase bcp locally and freetds
remotely? Can you see if both are using fast bcp, i.e. minimally logged
or are both using fully logged?

Just some ideas unless someone else has got better ones!

Cheers,

Matthew


On 20/06/15 15:18, Ray Rankins wrote:
Just tested running a large bcp with 0.91 freebcp and the performance was
awful.
Took 1.5 hours to load 5 million rows (conversely, Sybase bcp loaded the
same file in 1.5 minutes).
Is there some setting that might be on during compile that would cause
freebcp to run slow.
I checked and double checked that the debug flags were not enabled (have
made that mistake before) and they were not.
Are there any compile time options that could slow down freebcp that I
should make sure are disabled when I compile it?
-Ray



_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page