Board index » cppbuilder » Re: Slow

Re: Slow


2006-02-24 11:32:46 PM
cppbuilder82
i need solution not laughing :)
 
 

Re:Re: Slow

it could NOT write to txt file, too big for it!
 

Re:Re: Slow

"Cenk" < XXXX@XXXXX.COM >wrote in message
Quote
i need solution not laughing :)
We need technical accuracy to solve your problem. It is physically impossible
to store a single database record in less than a byte (that's why we were
laughing). One suggestion is to use fopen, fwrite, and fclose instead of the
C++ file stream operations. But what Jonathan is trying to establish is
whether the time is being taken writing to file, or navigating the database.
Which one?
If navigating is taking the time, then copy the database onto your local C:
drive and do the export from there - it should go 10 to 100 times faster than
over a 10MBps network connection.
--
Mark Jacobs
DK Computing
www.dkcomputing.co.uk
 

{smallsort}

Re:Re: Slow

One suggestion is to use fopen, fwrite, and fclose instead of the
Quote
C++ file stream operations. But what Jonathan is trying to establish is
whether the time is being taken writing to file, or navigating the
database. Which one?
i think it is NOT navigating because my DB is on the C drive already , we
are not doing this over network!
then there is one option for me. How to change the code to fwrite & fread?
 

Re:Re: Slow

"Cenk" < XXXX@XXXXX.COM >wrote:
Quote
i need solution not laughing :)
Well, if you give us bad data, we're going to have problems helping. Any
laughter is that of despair, not of mockery.
So, given that "file size is 2,5 GB and row size is app 34 billions" is
incorrect, what are the _correct_ figures? Do you mean that there are
3.4 x 10^10 rows in the database, and that the output file broke at
about 2.5 GB?
If you're trying to copy 3.4 x 10^10 rows to file, and your code can
manage 10^6 records per second, then it'd take an hour to do. But it
might be your code can only do 10^5 records per second, so it would take
10 hours.
What you need to do is to find out what is taking the time. Is it the
CountQuery->Next()? Or is it the writefile << tmp9.c_str() << "\t" ...?
Try timing it over 1,000,000 rows, with one or the other.
If you're hitting the maximum output file size problem, then you need to
change how you handle that output file. I'd guess that you're looking at
something like a multi-terabyte output file. How large is your disc?
Usually, large data like this should be kept in databases, which have
been optimised over decades to be able to cope. Why do you need to
extract all this data from the database?
Alan Bellingham
--
ACCU Conference 2006 - 19-22 April, Randolph Hotel, Oxford, UK
 

Re:Re: Slow

Cenk wrote:
Quote
i think it is NOT navigating because my DB is on the C drive already , we
are not doing this over network!
The TQuery still may be the slow part even though the db is on your C drive.
That is what I wanted to find out.
Quote
then there is one option for me. How to change the code to fwrite & fread?
This code is untested.
You'll need to #include <stdio.h>
Change this:
ofstream writefile ("eurusdfull.txt",ios::app);
if (writefile.is_open())
{
to:
FILE *writefile = fopen( "eurusdfull.txt", "wb" );
if( writefile )
{
Then this:
writefile <<
tmp9.c_str()<<"\t"<<tmp1.c_str()<<"\t"<<tmp2.c_str()<<"\t"<<tmp3.c_str()<<"\t"<<tmp4.c_str()<<"\t"<<tmp5.c_str()<<"\t"<<tmp6.c_str()<<"\t"<<tmp7.c_str()<<"\t"<<tmp8.c_str()<<"\n";
to:
tmp9 +=
"\t"+tmp1+"\t"+tmp2+"\t"+tmp3+"\t"+tmp4+"\t"+tmp5+"\t"+tmp6+"\t"+tmp7+"\t"+tmp8+"\n";
fwrite( writefile, tmp9.c_str(), tmp9.Length() );
Then finally this:
writefile.close();
to:
fclose( writefile );
Jonathan
 

Re:Re: Slow

Alan Bellingham wrote:
Quote
So, given that "file size is 2,5 GB and row size is app 34 billions" is
incorrect, what are the _correct_ figures? Do you mean that there are
3.4 x 10^10 rows in the database, and that the output file broke at
about 2.5 GB?
If it breaks at 2.5GB: 2^31 = signed int max maybe ?
Jonathan
 

Re:Re: Slow

Quote
So, given that "file size is 2,5 GB and row size is app 34 billions" is
incorrect, what are the _correct_ figures? Do you mean that there are
3.4 x 10^10 rows in the database, and that the output file broke at
about 2.5 GB?
file size is 2,512,977,920 bayt and number of rows 35021367
Quote
If you're hitting the maximum output file size problem, then you need to
change how you handle that output file. I'd guess that you're looking at
something like a multi-terabyte output file. How large is your disc?
2 x 200 GB
Usually, large data like this should be kept in databases, which have
been optimised over decades to be able to cope. Why do you need to
extract all this data from the database?
i m trying to find a way (a faster one would be better) to get datas from
DB then process them and finally write into another DB.
 

Re:Re: Slow

"Cenk" < XXXX@XXXXX.COM >wrote in message
Quote

file size is 2,512,977,920 bayt and number of rows 35021367

i m trying to find a way (a faster one would be better) to get
datas from DB then process them and finally write into another DB.
Does your database engine support stored procedures?
--
Bruce
 

Re:Re: Slow

yes SP supported
 

Re:Re: Slow

"Cenk" < XXXX@XXXXX.COM >wrote:
Quote
file size is 2,512,977,920 bayt and number of rows 35021367
Ah. The number of rows was out by a factor of a thousand - that was what
was confusing us. But writing a 2.5 GB file will take time - even
copying one from one place on a disk to another place on the same disc
takes time, and that's with the fastest, rawest writes available.
Quote
i m trying to find a way (a faster one would be better) to get datas from
DB then process them and finally write into another DB.
The best way is usually to get the database to do that processing
itself. I rather suspect that the right SQL would do this much faster
than extracting all the data from the database, processing it outside,
and then stuffing it back into another one. (35 million inserts will
take time. A lot of time.)
You might try asking in the databases newsgroup.
Alan Bellingham
--
ACCU Conference 2006 - 19-22 April, Randolph Hotel, Oxford, UK
 

Re:Re: Slow

On Fri, 24 Feb 2006 18:07:02 +0200, Cenk wrote:
Quote
i m trying to find a way (a faster one would be better) to get datas from
DB then process them and finally write into another DB.
You might look into bcp also.
--
liz
 

Re:Re: Slow

"Cenk" < XXXX@XXXXX.COM >wrote in message
Quote
yes SP supported
The fastest way to manipulate datasets is on the server side, so I'm
thinking that you might try writing SPs to calculate your stats. You
would then use SQL to select the SP results into a new table, and it
is all handled by the database server(which hopefully is designed for
this kind of thing). Of course, writing and debugging SPs is another
matter, but if speed is what you need, I think you should look into
this option.
--
Bruce
 

Re:Re: Slow

Alan Bellingham wrote:
Quote
The best way is usually to get the database to do that processing
itself. I rather suspect that the right SQL would do this much faster
than extracting all the data from the database, processing it outside,
and then stuffing it back into another one. (35 million inserts will
take time. A lot of time.)
No to mention all of those string conversions before writting to the
text file. If this is an intermediate file, only being read back, and
the numbers re-converted to floats and such, it would likely be much
more efficient to have a structure filled by the DB that could then be
written/re-read directly (in binary mode).
 

Re:Re: Slow

At 16:14:48, 24.02.2006, Cenk wrote:
Quote
file size is 2,5 GB and row size is app 34 billions
2.5 GB is approx 2.5 billion bytes. How do you put 34 billion rows in
there? Or did you mean 34 million rows?
--
Rudy Velthuis [TeamB] rvelthuis.de/
"If you were plowing a field, which would you rather use? Two strong oxen
or 1024 chickens?"
-- Seymour Cray (1925-1996), father of supercomputing