FreeTDS Development Group

["Ray Rankins" <rrankins AT gothamconsulting.com>]

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