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: matthew.green AT datamartcomputing.com
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] 0.91 freebcp performance
  • Date: Thu, 25 Jun 2015 07:43:18 +0000

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




Archive powered by MHonArc 2.6.24.

Top of Page