Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Boring features..

3 posts in Product Futures Discussion Last posting was on 2002-03-05 06:28:44.0Z
j Posted on 2002-02-08 09:52:05.0Z
From: J
Date: Fri, 8 Feb 2002 04:52:05 -0500
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Boring features..
Message-ID: <2176D3A3DE785021003634F885256B5A.0036353B85256B5A@webforums>
Lines: 43
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:793
Article PK: 94318

A few remaining questions / nice-to haves ...?

1.
If a Delete can be sped up (like Roger Broadbent showed so elegantly),
shouldn't the optimiser devise such startegies ?
i.e. shouldn't the optimiser cost the effort of a delete vs a new_table
with object_id swap (a-la reorg rebuild) ?

2.
Is BCP faster than select into ?

3.
Wouldn't it be nice to, from within isql, perform a bulk_insert into
another, existing table (possibly containing rows),
using minimal logging, if the database options allows it.


My thoughts on the two questions:

1.
I think it should. Nothing much to think about, I'd think ?

2.
If I use a named pipe, bcp into a table from the pipe, and bcp out into the
pipe, BCP seems to be faster in general.
On large table BCP does perform faster than select into, and on smaller
tables it doesn't matter.
BCP logs less data than select into (I have data to prove that), and BCP
can use large I/O for writes, which select into cannot. (I have data
backing up that theory, but cannot prove it beyond doubt).
If the above two statements holds true, and I'm fairly sure they do, I
believe it may be worthwhile to re-design select into to be a bcp-like
task, which is I'm sure what the intent was from the start.
If it uses fast bcp for db's that allows minimally logged ops, and using
slow-bcp where the db does not. This will remove the problem with select
into's being dependent on the db-options.
Furthermore, select into can then use large I/O, and the remaining
functions.

3.
See #1.


J


Aditya Gurajada Posted on 2002-03-05 06:28:44.0Z
Message-ID: <3C84659C.3BB9696F@home.com>
Date: Mon, 04 Mar 2002 22:28:44 -0800
From: Aditya Gurajada <imrightnow@home.com>
Reply-To: aditya.gurajada@sybase.com
Organization: @Home Network
X-Mailer: Mozilla 4.72 [en]C-AtHome0404 (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Boring features..
References: <2176D3A3DE785021003634F885256B5A.0036353B85256B5A@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 48
NNTP-Posting-Host: 10.22.120.97
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:749
Article PK: 94279

Hi J,

> 2.
> Is BCP faster than select into ?
>

> 2.
> If I use a named pipe, bcp into a table from the pipe, and bcp out into the
> pipe, BCP seems to be faster in general.
> On large table BCP does perform faster than select into, and on smaller
> tables it doesn't matter.
> BCP logs less data than select into (I have data to prove that), and BCP
> can use large I/O for writes, which select into cannot. (I have data
> backing up that theory, but cannot prove it beyond doubt).

Mostly true, although if you do parallel select-into, we do use
large I/O allocation of pages. (This should probably be extended to also
do large I/O allocation for serial select-into, and we'll look into
that.)

> If the above two statements holds true, and I'm fairly sure they do, I
> believe it may be worthwhile to re-design select into to be a bcp-like
> task, which is I'm sure what the intent was from the start.
> If it uses fast bcp for db's that allows minimally logged ops, and using
> slow-bcp where the db does not. This will remove the problem with select
> into's being dependent on the db-options.
> Furthermore, select into can then use large I/O, and the remaining
> functions.
>
These ideas are good, and in fact, we do internally already use
bcp-based transfer for data using SELECT INTO across CIS servers
via proxy tables.

It maybe possible and faster if we did the same for select-into
into the same server, but am not sure that it is being considered
right now.

One main difference between bcp and select-into is that the latter
allows you to do data transformations as part of the operation.
E.g., you can selectively copy out some columns, driven by a WHERE
clause etc. etc. Not that this precludes what you suggest, just
pointing out the differences.

Thanks, for the nice writeup and analysis.
-- Aditya.
> 3.
> See #1.
>
> J


Roger Broadbent Posted on 2002-02-08 15:35:31.0Z
From: "Roger Broadbent" <RBroadbent@wilco-int.com>
References: <2176D3A3DE785021003634F885256B5A.0036353B85256B5A@webforums>
Subject: Re: Boring features..
Date: Fri, 8 Feb 2002 15:35:31 -0000
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <7ATGkdLsBHA.315@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: wilcohost-180.wilco-int.com 212.36.174.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:791
Article PK: 94317


<J> wrote in message
news:2176D3A3DE785021003634F885256B5A.0036353B85256B5A@webforums...
> A few remaining questions / nice-to haves ...?
>
> 1.
> If a Delete can be sped up (like Roger Broadbent showed so elegantly),

You are too kind :-)

> shouldn't the optimiser devise such startegies ?
> i.e. shouldn't the optimiser cost the effort of a delete vs a new_table
> with object_id swap (a-la reorg rebuild) ?

>
> 1.
> I think it should. Nothing much to think about, I'd think ?
>

There are the issues of concurrency and recoverability:

- most of my suggestions would require exclusive access to the table for at
least part of the time. Given datarow locking and the right configuration to
prevent lock escalation, the original DELETE does not have to block other
processes accessing the rows that are not to be deleted.

- most of my suggestions require or benefit from haing the select
into/bulkcopy flag set. This has issues for database recovery using
transaction logs.

Not to say these issues can't be overcome, but there are things to think
about here.

Roger
Roger