Pull data from Cache 5.0 to SQL 2000 (fast?)

[Pippen]

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five
days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational
format (SQL 2000)???

Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.

Thanks,

-p

[Greg D. Moore \(Strider\)]

"Pippen" <12*@hotmail.com> wrote in message
news:uadBb.1525$8y1.14237@attbi_s52...

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational format (SQL 2000)???

Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.
I don't know a thing about Cache, but it could be several things.

Is the table you're pulling the data into have a large number of indices?
If so, it is probably faster to drop them, load the data and then rebuild
them.

Also, you can try dumping the cache data to a text file of some sort and
using BCP or a DTS package to load it.

As reference, I load 3 tables compromising I think about 10 gig of data (I'd
have to look it up) and index them in I think under 6 hours. And that's on
moderately old hardware (3 years old).

I do this 4 times a year for 3 different databases. If I had to do it more
often I could probably speed it up even faster than that.

So, it's quite possible you may be able to speed this up.


Thanks,

-p

[Pippen]

"Greg D. Moore (Strider)" <mo*****@greenms.com> wrote in message
news:5A*********************@twister.nyroc.rr.com. ..

"Pippen" <12*@hotmail.com> wrote in message
news:uadBb.1525$8y1.14237@attbi_s52...

I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five

days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a

relational

format (SQL 2000)???

Does anyone have any ideas / suggestions on how to speed this transfer of data? I'm very new to Cache and any help would be greatly appreciated.

I don't know a thing about Cache, but it could be several things.

Is the table you're pulling the data into have a large number of indices?
If so, it is probably faster to drop them, load the data and then rebuild
them.

Also, you can try dumping the cache data to a text file of some sort and
using BCP or a DTS package to load it.

As reference, I load 3 tables compromising I think about 10 gig of data

(I'd have to look it up) and index them in I think under 6 hours. And that's on moderately old hardware (3 years old).

I do this 4 times a year for 3 different databases. If I had to do it more often I could probably speed it up even faster than that.

So, it's quite possible you may be able to speed this up.

Thanks,

-p

Thanks for the advice. I probably should have mentioned that the data is
going in to empty non indexed tables...

-js

[Greg D. Moore \(Strider\)]

"Pippen" <12*@hotmail.com> wrote in message
news:9koBb.484514$Tr4.1330184@attbi_s03...

Thanks for the advice. I probably should have mentioned that the data is
going in to empty non indexed tables...

Hmm, interesting. I'd then try to dump the data out of Cache and use BCP
and see if that's any faster.

At the very least it may break it into two parts and give you an idea of
which one is the slow part.

-js