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\)]

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


"Pippen" <123@hotmail.co m> wrote in message
news:uadBb.1525 $8y1.14237@attb i_s52...[color=blue]
> 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[/color]
five[color=blue]
> 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[/color]
relational[color=blue]
> 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.[/color]

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.

[color=blue]
>
> Thanks,
>
> -p
>
>[/color]

[Pippen]

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


"Greg D. Moore (Strider)" <mooregr@greenm s.com> wrote in message
news:5AdBb.1983 32$1N3.102110@t wister.nyroc.rr .com...[color=blue]
>
> "Pippen" <123@hotmail.co m> wrote in message
> news:uadBb.1525 $8y1.14237@attb i_s52...[color=green]
> > 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[/color]
> five[color=green]
> > 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[/color]
> relational[color=green]
> > format (SQL 2000)???
> >
> > Does anyone have any ideas / suggestions on how to speed this transfer[/color][/color]
of[color=blue][color=green]
> > data? I'm very new to Cache and any help would be greatly appreciated.[/color]
>
> 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[/color]
(I'd[color=blue]
> have to look it up) and index them in I think under 6 hours. And that's[/color]
on[color=blue]
> moderately old hardware (3 years old).
>
> I do this 4 times a year for 3 different databases. If I had to do it[/color]
more[color=blue]
> often I could probably speed it up even faster than that.
>
> So, it's quite possible you may be able to speed this up.
>
>[color=green]
> >
> > Thanks,
> >
> > -p
> >
> >[/color]
>
>[/color]
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\)]

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


"Pippen" <123@hotmail.co m> wrote in message
news:9koBb.4845 14$Tr4.1330184@ attbi_s03...[color=blue][color=green]
> >[/color]
> Thanks for the advice. I probably should have mentioned that the data is
> going in to empty non indexed tables...[/color]

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.
[color=blue]
>
> -js
>
>[/color]