FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

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