Bypassing the disk.

Something like a batch select into dest_db..dest_table from
src_db..src_table or insert select.
Anything that does not involve excessive logging and storing bcp output on
disk.

On Nov 10, 10:11*pm, "Tester" <teste... (AT) tester (DOT) cc> wrote:
Hi

Assuming that you need to move the data between different dataservers,
you can use CIS for this purpose. Create a proxy table in the
destination database that points to your source table in the source
dataserver. Then, you will be able to run SELECT INTO statement into a
new table in the destination dataserver from the proxy table with
minimal logging. You may want to tune some CIS-related parameters on
source and destination dataservers for better performance, such as
"cis packet size", "cis bulk insert array size", "cis cursor rows"
etc.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On Nov 10, 2:11*pm, "Tester" <teste... (AT) tester (DOT) cc> wrote:
Or if you can't bypass the disk completely and don't have the CIS
plugin, I've had good results on some O/S's by doing this:

1) Create a FIFO (named pipe).
2) Start up a bcp in from the FIFO.
3) Run a bcp out to the FIFO.

Keith

On 11/12/09 17:53 , Keith wrote:
and if you go across macnines,

pipe the output from the prev post into ssh
and that into a fifo again
and a bcp out of that pipe

SELECT INTO does look like the fastest method.
But in our case, for instance, we have a table with approx. 150M rows. While
SELECT INTO took roughly 3 hours, it's the subsequent build of clustered
index that times out.

Is there a way to boost that, maybe run something in parallel? Partitioning
this table is not an open option at this time, since we are prohibited to
change the model by the vendor.


"Leonid Gvirtz" <lgvirtz (AT) yahoo (DOT) com> wrote

On Nov 10, 10:11 pm, "Tester" <teste... (AT) tester (DOT) cc> wrote:
Hi

Assuming that you need to move the data between different dataservers,
you can use CIS for this purpose. Create a proxy table in the
destination database that points to your source table in the source
dataserver. Then, you will be able to run SELECT INTO statement into a
new table in the destination dataserver from the proxy table with
minimal logging. You may want to tune some CIS-related parameters on
source and destination dataservers for better performance, such as
"cis packet size", "cis bulk insert array size", "cis cursor rows"
etc.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

Hi Axel

Did you try WITH SORTED_DATA option for creation of your clustered
index? If not - then it may be the way. Add ORDER BY clause according
to the clustered index fields to your SELECT INTO statement. Then, you
will be able to skip the data copying during the clustered index
creation by using WITH SORTED_DATA option since the data in the
resulting table is already sorted. See this link for more details:
http://infocenter.sybase.com/help/to...868.htm#X24774

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On Nov 18, 2:45*am, "Axel" <tes... (AT) tester (DOT) cc> wrote:

That's what I was thinking exactly, however, this seem to completely fill
the logsegment in tempdb unless we make it the size of this enormous table
we got. This happens during the SELECT INTO FROM src_table ORDER BY step.


"Leonid Gvirtz" <lgvirtz (AT) yahoo (DOT) com> wrote

Hi Axel

Did you try WITH SORTED_DATA option for creation of your clustered
index? If not - then it may be the way. Add ORDER BY clause according
to the clustered index fields to your SELECT INTO statement. Then, you
will be able to skip the data copying during the clustered index
creation by using WITH SORTED_DATA option since the data in the
resulting table is already sorted. See this link for more details:
http://infocenter.sybase.com/help/to...868.htm#X24774

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On Nov 18, 2:45 am, "Axel" <tes... (AT) tester (DOT) cc> wrote:

If the source table has an index identical to the desired clustered index for the new table, consider having the
SELECT/INTO use said index (force it if necessary) from the source table.

Then use the 'with sorted_data' option when creating the clustered index on the new table.

Upside: should eliminate the overhead of sorting the table (either via select/into/order-by or create/index)

Downside: SELECT could take longer to run and require more physical/logical IOs, especially if having to make 150
million hops from index leaf pages to data pages

Obviously (?):

- if the source table is APL with a clustered index, then insuring the SELECT uses said clustered index should be
(relatively) quick *and* allow for the use of the 'with sorted_data' option

- use of parallel/worker threads for the SELECT could cause the data to be scrambled durint the INSERT step, thus
negating the ability to use the 'with sorted_data' option


Axel wrote:

Instead of:

if the source table is APL with a clustered index

Should have been:

if the source table is APL with the same desired clustered index


Mark A. Parsons wrote:

Hi

You can use CIS trace flags (11201-11207, server-wide) in order to see
the actual query text which is sent to the source dataserver. Then,
you can check the execution plan on the source dataserver, watch for
Worktable(s) creation. I suspect that Worktables used by the query
fills up your tempdb on the source dataserver. If you have the same
clustered index on the source table then ORDER BY by index fields
should not cause Worktable creation.

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com


On Nov 19, 12:36*am, "Mark A. Parsons"
<iron_horse (AT) no_spamola (DOT) compuserve.com> wrote: