Re: how big can disconnected dataset be?

From: CJ Taylor (nospam_at_blowgoats.com)
Date: 02/23/04 

Date: Mon, 23 Feb 2004 12:31:24 -0600

"Rich" <anonymous@discussions.microsoft.com> wrote in message
news:148bb01c3fa3a$33eb9470$a301280a@phx.gbl...
> Thanks. I forgot to mention, yes, reducing network
> traffice, or rather, get all the data to the local server
> and close the connection would be a good thing to
> achieve. The other data source is Lotus Notes R5. I have
> to pull data from 4 separate Notes DB's (nsf's). Not my
> design. I could take it or leave it.
>

I coded notes (4.5 and 5) for 2 years... I can honestly say that I have
never looked on a project and said "You know what would be a good
solution... Notes..."

I know it has its advantages, but coming from a developer standpoint it
kinda sucks and is INCREDIBLY slow...

> So you day that it would be possible to fill the datasets
> (disconnected datasets) this way? Thinking outloud, I
> have 4 tables in Sql Server and would have 4 datasets in
> my VB.net app.

Yeah, that would be fine. I'm sure the Notes API is slowing you down as it
is... So this will reduce your overall network traffic (well, amount of
time used for network traffic, you'll have a big burst at the beginning and
then nothing).

>Then, with multi-threading, I would start
> pulling data into each of these datasets simultaneously.
> I have 4 gigs of mem right now. The deal is that the old
> way I would just write the data directly to the Sql
> Tables. Here I load the data to memory as fast as I can,
> close my connection to Lotus Notes (using Domino Object
> Library here) and then start pushing the data into Sql
> Server from the datasets. Wouldn't this be better, more
> efficient than having 4 com based apps reading and writing
> data 250,000+ times a piece?

For network traffic, yes, it would be better. I don't think you will see a
huge performance increase on your SQL server, but I'm sure you will see some
(as you now are not requesting data from a foreign source for each record).

Nonetheless... 180 fields, 1 million records, still going to take al ittl
etime to get in there. =)

-CJ

>
>
>
>
> >-----Original Message-----
> >
> >">
> >> I have to read and write around one million records from
> >> an external data source to Sql Server2k every night.
> >> That's a lot of I/O.
> >
> >Yes it is... =)
> >
> >>I am using VB6 for this (takes
> >> hours). I am connecting to the external data source
> with
> >> API's from its object library (as opposed to ODBC in
> which
> >> case I would just use DTS to pull this data) and just
> >> looping.
> >
> >I'm assuming this is a proprietary data format?
> >
> >>I was thinking that with VB.Net I could read
> >> this data into a dataset in memory (currently have 4
> gigs
> >> of mem - may upgrade to 16 gigs if they let me have
> win2K
> >> Advanced Server - currently 2 processors 1 gig each).
> My
> >> idea is that if I could just read the data into memory
> (a
> >> dataset) it would be much faster than writing each
> record
> >> (180 fields per record). Then I could just do a
> >> dataAdapter.Update or InsertInto to Sql Server once the
> >> data is all in local memory. Any thoughts?
> >>
> >
> >Your still going to have a downturn on speed on the
> update/insert. I'm not
> >sure if it will cache data on your local disk or not
> depending on the amount
> >of memory. But you are still going to be subject to the
> speed of your SQL
> >Server. So you can have it all queued up, but its not
> necessarily going to
> >make it faster.
> >
> >> While I'm at it, the records come from 4 different
> >> sources. I was thinking about using multi-threading and
> >> pull the data simultaneously. I am aware than Sql
> >> Server2k only supports 4 gigs a mem. But if I have more
> >> than 4 gigs of data can one VB.Net app manage datasets
> in
> >> more than 4 gigs of memory? Once I fill my datasets, I
> >> would just do one InsertInto at a time. How is VB.Net
> for
> >> multi-threading? Again, in VB6 I invoke 4 separate apps
> >> which simultaneously pull the data each night. They
> write
> >> to 4 separate table in Sql Server2k. I would really
> like
> >> to have all this in one app and read/write directly to
> >> memory. Is VB.Net a way to do this?
> >>
> >
> >Many ways, I just don't know the benefit of it from what
> your giving us.
> >Yeah your reads and loading will be fast, but is this
> program going to run
> >on the SQL server itself? If your looking to reduce your
> amount of network
> >traffic, yeah this will solve your problem, if your
> looking to speed up
> >injection into your SQL server, not gonna do it... =(
> >
> >You could use a more updated OLEDB driver for SQL Server,
> which may shave
> >some time off because of optimized programming, but
> again, don't know how
> >much of a gain you will actually get.
> >
> >-CJ
> >
> >> Thanks,
> >> Rich
> >
> >
> >.
> >