[dba-SQLServer] Moving tables back and forth between database s

Colby, John JColby at dispec.com
Tue Nov 9 08:45:56 CST 2004


I do have a gigabit switch connecting all the machines.  Even there, if each
machine does all processing of its piece and just returned a data set to a
control server, the total data over the wire would be fairly small.

I would love to distribute the processing to different machines.  I have
potentially 4 3ghz machines with up to 3gb of ram on each machine.  Assume
that there are 30 sub-tables.  If each machine could take a set of these
sub-tables (8 each) and then each machine were to do the processing of the
portion of the sql statement that was defined by the sub-tables each machine
held, I could effectively multiply my processing power by a factor of 4 or
so.  Further, if this worked, I could add additional machines if required.

Has anyone on this list ever done something of this nature?

John W. Colby
The DIS Database Guy

-----Original Message-----
From: Francisco Tapia [mailto:fhtapia at gmail.com]
Sent: Tuesday, November 09, 2004 1:27 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Moving tables back and forth between

agreed, I'd first try to use a linked server to house the extended
tables.  This would help offload processing time, but perhaps increase
network traffic, however IIRC you're on a gigabit backbone between the

On Tue, 9 Nov 2004 16:53:55 +1100, Michael Maddison
<michael at ddisolutions.com.au> wrote:
> John,
> I'm not sure EM or DTS will do a transfer with indexes.  I'm pretty sure
> it wont actually.
> I think you will have to script the create indexes manually.  What I'd
> do is create a script
> for each server, (QA will do most of the work for you)
> create table
> go
> append data
> go
> alter table  (constraints)
> go
> create index
> go
> Then run it from the target server, that way it wont affect your main
> server too much.
> I hope your getting paid the big bucks because If you think you've had
> troubles before I
> think the fun is just about to begin ;-)))
> I read your other posts as well and I'm not sure if my comments will be
> all that helpful as
> I suspect you'll need a fair amount of trial and error anyway to see
> what works best for
> the environment you have to work with...
> However, I'm nervous about you normalising the big table.  I predict you
> will only end up
> hampering performance.  Joins on large table or many joins take a
> considerable amount of time.
> If you need to create separate files for space reasons have a look at
> Partitioned Views in BOL.
> This will allow you to split the table over multiple drives and multple
> servers.
> I'd also seriously try and coerce the client into taking an OLAP
> approach, though this also has its
> drawbacks as there are so many damn columns.  A cube with 600 dimensions
> would never
> finish processing so maybe not :-)))   Just how dynamic do the queries
> have to be?
> cheers
> Michael M
> Can SQL Server EM copy tables from a database in one SQL Server (Neo2)
> to another SQL Server (Neo1)?  I am trying to tear down the big 64
> million record 640 field table into sub-tables.  The process consists of
> creating the tables, building an append query to copy the data from the
> big table to the new sub-table, then creating indexes on every field in
> the sub-table.
> The process of creating the new tables is relatively easy and quick,
> manually copying the field defs into the paste buffer, then pasting them
> back in to a new table.  The process of creating and executing the
> append query is relatively painless and fairly quick though it does take
> a couple of hours (I think, never actually timed it) to copy the data.
> However the process of creating the indexes is time consuming both in
> the doing (manually in table design view) and particularly in the
> execution (saving the table with the new indexes - which can take
> overnight.  The process of creating the indexes pretty much locks up the
> machine (80-100% CPU
> utilization) for many many hours.
> I would like to create the sub-tables all at once, then farm out the
> index creation to my other machines, but in order to do that I have to
> copy the data filled tables to another machine, switch to that machine,
> create the indexes in design view, then save the table in design view
> which starts the index creation process happening.
> I have no idea whether it is even possible to do this "table copy", but
> all indexes need to copy as well at least when I copy them back to the
> main SQL Server.
> On another note, how do I split the database into more files?  I built
> it originally in 4 files, and then as I finished the import I shrank
> them down into two files (on two physical 250g disks).  I am now pushing
> the size up such that I need to split them back out to at least one more
> 250g disk (one more file), and perhaps even two.  I haven't discovered
> how to split the database into more physical files.
> John W. Colby
> www.ColbyConsulting.com
> Contribute your unused CPU cycles to a good cause:
> http://folding.stanford.edu/
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com

<a href="http://pcthis.blogspot.com">Pc This! pc news with out the
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com

More information about the dba-SQLServer mailing list