FreeTDS Development Group

[matthew.green AT datamartcomputing.com]

Glad to hear things are working better! 

As an aside you could try using a larger packet size and try different batch 
sizes to optimise the process even further. Larger packet sizes would imply 
that the Sybase server is set-up to support them ("additional network memory" 
and "max network packet size").

Cheers,

Matthew.

June 23 2015 10:41 PM, "Ray Rankins" <rrankins AT gothamconsulting.com> wrote:
> Thanks Frediano.
> That’s actually what I ended up doing.
> I built a copy of 0.95 and installed it in a different folder alongside the 
> 0.91 version.
> In the shell scripts that run the bcp loads, I set the environment 
> variables to point to the 0.95
> folder and it's working like a charm.
> Import of an 83 million row file went from more than a day down to 1 hour 
> and 42 minutes.
> 
> -Ray
> 
>> -----Original Message-----
>> From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
>> Frediano Ziglio
>> Sent: Tuesday, June 23, 2015 4:27 PM
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] 0.91 freebcp performance
>> 
>> 2015-06-23 14:29 GMT+01:00 Ray Rankins
>> <rrankins AT gothamconsulting.com>:
>>> Thanks David.
>>> I was thinking more deeply regarding fast bcp versus "slow" bcp beyond
>> just whether there was an index or not.
>>> Been working more in SQL Server these days and whether you get
>> minimally logged bcp there depends on indexes, triggers, as well as the
>> recovery model chosen.
>>> 
>>> Anyway, whether fast or slow bcp, I compared Sybase versus freebcp both
>> with and without indexes on the table and 0.91 version of freebcp was
>> considerably slower in both cases. Performance without indexes still was 
>> not
>> acceptable.
>>> 
>>> I downloaded and built 0.95 last night and tested the freebcp included 
>>> with
>> that and that was just as fast (and possibly slightly faster) than the 
>> Sybase
>> bcp.
>>> So now I guess it may be a matter of convincing them to switch to 0.95, 
>>> but
>> they are pretty far along in their testing cycle, so it might not be 
>> feasible at
>> this point as they'd probably have to go back and regression test 
>> everything
>> again.
>>> 
>>> -Ray
>>> 
>> 
>> Glad to ear this!
>> 
>> Usually Unix is very flexible, you could try installing new version
>> along the old one. PATH, LD_RUN_PATH, LD_LIBRARY_PATH and --prefix are
>> your friends :-)
>> 
>> Frediano
>> 
>>> 
>>>> -----Original Message-----
>>>> From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of
>> David
>>>> Chang
>>>> Sent: Monday, June 22, 2015 2:47 PM
>>>> To: FreeTDS Development Group
>>>> Subject: Re: [freetds] 0.91 freebcp performance
>>>> 
>>>> 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
>>>> 
>>>> _______________________________________________
>>>> 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
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds