[freetds] 0.91 freebcp performance

Frediano Ziglio freddy77 at gmail.com
Tue Jun 30 04:35:11 EDT 2015


Sorry this time is my time to confuse people.

Yes, there is the option for request a different packet size. By
default server can also suggest a given packet size base on its
settings but this was not enabled (although supported) in 0.95.
The change is really small, just set one bit to say to the server
"please give me your suggestion on packet size".

Frediano


2015-06-25 8:43 GMT+01:00  <matthew.green at datamartcomputing.com>:
> That I didn't know! I saw it being used in datacopy (confirmed by querying the system tables in both SAP/Sybase and SQL Server) so I just assumed it was implemented for bcp too.
>
> My mistake,
>
> Matthew.
>
> June 24 2015 10:38 PM, "Frediano Ziglio" <freddy77 at gmail.com> wrote:
>> Il 24/Giu/2015 09:07, <matthew.green at datamartcomputing.com> ha scritto:
>>
>>> 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.
>>
>> I don't think it will change much. Unfortunately I realized that we don't
>> ask for packet size for Sybase. There is a patch in master that change a
>> bit in the capability.
>>
>> If it continue like this version 1.0 will be ready this year :-) There are
>> already 100 patches since 0.95.
>>
>> Frediano
>>
>>> 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
>>> _______________________________________________
>>> 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


More information about the FreeTDS mailing list