Copy data between two dataadapters

Hi,

we want to build an application which copies all data from the source to the
destination without changing anything.

We use data adapters for this process. So we open both adapters, use "fill"
on the source and give an "update" to the destination.

The problem is that the table we want to copy is quite large. So we see
quite a memory usage. So we decided to move the data in blocks, 1000 records
at a time. We use the dataadpter.fill(dataset,0,1000,tablename) to read the
first records, then we do an update, then the next fill
(dataadpater.fill(dataset,1001,2000,tablename) and loop that till EOF.

We see that the programm moves the first records quite quickly but gets
slower and slower over time. Our idea is that the dataadapter take more and
more time to skip the first records of the source to get to the real
"payload".

Is there anything we can do better? We simply want to move the first 1000
records, then the next 1000, and so on...

Thanks,



Thomas

jonathan
Thu May 13 00:38:17 CDT 2004

Thomas,

If you have an indexed ID column in your table, I'd recommend
rewriting the SELECT on your source dataadapter to include WHERE id >
@minrecord AND id <= @maxrecord.

When before executing your dataadapter.fill, call

dataadapter.selectcommand.parameters("@minrecord").Value = 0
dataadapter.selectcommand.parameters("@maxrecord").Value = 1000

The next time through, increment them by 1000, and so on.

Good luck,

Jonathan

"Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message news:<#3fwq2DOEHA.620@TK2MSFTNGP10.phx.gbl>...
> Hi,
>
> we want to build an application which copies all data from the source to the
> destination without changing anything.
>
> We use data adapters for this process. So we open both adapters, use "fill"
> on the source and give an "update" to the destination.
>
> The problem is that the table we want to copy is quite large. So we see
> quite a memory usage. So we decided to move the data in blocks, 1000 records
> at a time. We use the dataadpter.fill(dataset,0,1000,tablename) to read the
> first records, then we do an update, then the next fill
> (dataadpater.fill(dataset,1001,2000,tablename) and loop that till EOF.
>
> We see that the programm moves the first records quite quickly but gets
> slower and slower over time. Our idea is that the dataadapter take more and
> more time to skip the first records of the source to get to the real
> "payload".
>
> Is there anything we can do better? We simply want to move the first 1000
> records, then the next 1000, and so on...
>
> Thanks,
>
>
>
> Thomas

Thomas
Thu May 13 02:07:32 CDT 2004

Jonathan,

we would be happy if we would have an ID... We have a primary key, but it's
a string...

Thanks,


Thomas

"Jonathan Levine" <jonathan.levine@gmail.com> schrieb im Newsbeitrag
news:df5434c9.0405122138.135e7e1b@posting.google.com...
> Thomas,
>
> If you have an indexed ID column in your table, I'd recommend
> rewriting the SELECT on your source dataadapter to include WHERE id >
> @minrecord AND id <= @maxrecord.
>
> When before executing your dataadapter.fill, call
>
> dataadapter.selectcommand.parameters("@minrecord").Value = 0
> dataadapter.selectcommand.parameters("@maxrecord").Value = 1000
>
> The next time through, increment them by 1000, and so on.
>
> Good luck,
>
> Jonathan
>
> "Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message
news:<#3fwq2DOEHA.620@TK2MSFTNGP10.phx.gbl>...
> > Hi,
> >
> > we want to build an application which copies all data from the source to
the
> > destination without changing anything.
> >
> > We use data adapters for this process. So we open both adapters, use
"fill"
> > on the source and give an "update" to the destination.
> >
> > The problem is that the table we want to copy is quite large. So we see
> > quite a memory usage. So we decided to move the data in blocks, 1000
records
> > at a time. We use the dataadpter.fill(dataset,0,1000,tablename) to read
the
> > first records, then we do an update, then the next fill
> > (dataadpater.fill(dataset,1001,2000,tablename) and loop that till EOF.
> >
> > We see that the programm moves the first records quite quickly but gets
> > slower and slower over time. Our idea is that the dataadapter take more
and
> > more time to skip the first records of the source to get to the real
> > "payload".
> >
> > Is there anything we can do better? We simply want to move the first
1000
> > records, then the next 1000, and so on...
> >
> > Thanks,
> >
> >
> >
> > Thomas

Thomas
Thu May 13 02:11:55 CDT 2004

Hi,

the problem is that we have a quite "special" ODBC driver as a source (SQL
Server as destination), we tried to use it with SQL Server DTS and it's not
very reliable. Getting data from it by a dataadapter works fine but we
experience performance problems. A bulk copy would make sense if we would
find a way to get the data from that "special" ODBC source into a text file
but I don't see a way to do that...

Thanks,


Thomas

"Bin Song, MCP" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:70348EED-D806-4AE1-95DA-A910700AE884@microsoft.com...
> Hi, Thomas,
>
> For copying large amount of data, I don't see the reason to use dataset.
> What's your database in both source and destination? Doing bulk copying in
database will be much faster that using dataset.
> Even using Insert ... Select ... will be more efficient.
>
> Bin Song, MCP

anonymous
Thu May 13 08:01:11 CDT 2004

Hi, Thomas

So which operation takes more time? Fill ODBC data into dataset, or copy first dataset to the second dataset, or Update data into SQL Server
If it is the problem of updating, I have another idea
1. Fill ODBC data into a dataset:
da.Fill(ds

2. Get the XML of the dataset:
strXml = ds.GetXml(

3. Create a Stored Procedure for updating
Create Proc MySP(@doc varchar(8000)) --Max length of varchar is 8000, you migh conside text for larger dat
A
DECLARE @idoc in
EXEC sp_xml_preparedocument @idoc OUTPUT, @do
INSERT Into YourTable
SELECT
FROM OPENXML (@idoc, '/ROOT/Row',1
EXEC sp_xml_removedocument @ido

4. Update using SQLComman
cmd.ExecuteNonQuery("MySP"

I am not sure this will work or not for your case. hope it helps

Bin Song, MC

jonathan
Thu May 13 23:17:43 CDT 2004

Thomas,

Does your source dataset support SELECT TOP n and ORDER BY on the primary key?

-- J

"Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message news:<#c7jWjLOEHA.3264@tk2msftngp13.phx.gbl>...
> Jonathan,
>
> we would be happy if we would have an ID... We have a primary key, but it's
> a string...
>
> Thanks,
>
>
> Thomas
>
> "Jonathan Levine" <jonathan.levine@gmail.com> schrieb im Newsbeitrag
> news:df5434c9.0405122138.135e7e1b@posting.google.com...
> > Thomas,
> >
> > If you have an indexed ID column in your table, I'd recommend
> > rewriting the SELECT on your source dataadapter to include WHERE id >
> > @minrecord AND id <= @maxrecord.
> >
> > When before executing your dataadapter.fill, call
> >
> > dataadapter.selectcommand.parameters("@minrecord").Value = 0
> > dataadapter.selectcommand.parameters("@maxrecord").Value = 1000
> >
> > The next time through, increment them by 1000, and so on.
> >
> > Good luck,
> >
> > Jonathan
> >
> > "Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message
> news:<#3fwq2DOEHA.620@TK2MSFTNGP10.phx.gbl>...
> > > Hi,
> > >
> > > we want to build an application which copies all data from the source to
> the
> > > destination without changing anything.
> > >
> > > We use data adapters for this process. So we open both adapters, use
> "fill"
> > > on the source and give an "update" to the destination.
> > >
> > > The problem is that the table we want to copy is quite large. So we see
> > > quite a memory usage. So we decided to move the data in blocks, 1000
> records
> > > at a time. We use the dataadpter.fill(dataset,0,1000,tablename) to read
> the
> > > first records, then we do an update, then the next fill
> > > (dataadpater.fill(dataset,1001,2000,tablename) and loop that till EOF.
> > >
> > > We see that the programm moves the first records quite quickly but gets
> > > slower and slower over time. Our idea is that the dataadapter take more
> and
> > > more time to skip the first records of the source to get to the real
> > > "payload".
> > >
> > > Is there anything we can do better? We simply want to move the first
> 1000
> > > records, then the next 1000, and so on...
> > >
> > > Thanks,
> > >
> > >
> > >
> > > Thomas

Thomas
Fri May 14 06:21:51 CDT 2004

Jonathan,

yes, that's no problem...

We just changed our application to read the source record by record... This
gives us a better performance on big recordsets...

Thanks,


Thomas

"Jonathan Levine" <jonathan.levine@gmail.com> schrieb im Newsbeitrag
news:df5434c9.0405132017.53f0bfea@posting.google.com...
> Thomas,
>
> Does your source dataset support SELECT TOP n and ORDER BY on the
primary key?
>
> -- J
>
> "Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message
news:<#c7jWjLOEHA.3264@tk2msftngp13.phx.gbl>...
> > Jonathan,
> >
> > we would be happy if we would have an ID... We have a primary key, but
it's
> > a string...
> >
> > Thanks,
> >
> >
> > Thomas
> >
> > "Jonathan Levine" <jonathan.levine@gmail.com> schrieb im Newsbeitrag
> > news:df5434c9.0405122138.135e7e1b@posting.google.com...
> > > Thomas,
> > >
> > > If you have an indexed ID column in your table, I'd recommend
> > > rewriting the SELECT on your source dataadapter to include WHERE id >
> > > @minrecord AND id <= @maxrecord.
> > >
> > > When before executing your dataadapter.fill, call
> > >
> > > dataadapter.selectcommand.parameters("@minrecord").Value = 0
> > > dataadapter.selectcommand.parameters("@maxrecord").Value = 1000
> > >
> > > The next time through, increment them by 1000, and so on.
> > >
> > > Good luck,
> > >
> > > Jonathan
> > >
> > > "Thomas Pagel" <tpagel@software4you.no-spam.com> wrote in message
> > news:<#3fwq2DOEHA.620@TK2MSFTNGP10.phx.gbl>...
> > > > Hi,
> > > >
> > > > we want to build an application which copies all data from the
source to
> > the
> > > > destination without changing anything.
> > > >
> > > > We use data adapters for this process. So we open both adapters, use
> > "fill"
> > > > on the source and give an "update" to the destination.
> > > >
> > > > The problem is that the table we want to copy is quite large. So we
see
> > > > quite a memory usage. So we decided to move the data in blocks, 1000
> > records
> > > > at a time. We use the dataadpter.fill(dataset,0,1000,tablename) to
read
> > the
> > > > first records, then we do an update, then the next fill
> > > > (dataadpater.fill(dataset,1001,2000,tablename) and loop that till
EOF.
> > > >
> > > > We see that the programm moves the first records quite quickly but
gets
> > > > slower and slower over time. Our idea is that the dataadapter take
more
> > and
> > > > more time to skip the first records of the source to get to the real
> > > > "payload".
> > > >
> > > > Is there anything we can do better? We simply want to move the first
> > 1000
> > > > records, then the next 1000, and so on...
> > > >
> > > > Thanks,
> > > >
> > > >
> > > >
> > > > Thomas