FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

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