FreeTDS Development Group

[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