List:MySQL++« Previous MessageNext Message »
From:Alex Vishnev Date:October 26 2005 12:10am
Subject:RE: Retrieving 300K+ records
View as plain text  
All,

It looks like Earl is correct. I modified my code based on his suggestion to
avoid using mysql++ fetch_row from ResUse object and used mysql_fetch_row
instead. That cut my record reading time in 1/2. Now it takes approximately
6-7sec to load the data, instead of original 12-14s. Even though the time to
load records is significantly less, it is still not the same as with using
flat file (2-3sec). is there anything else I can change to improve the
performance?

Alex

-----Original Message-----
From: Earl Miles [mailto:merlin@stripped] 
Sent: Tuesday, October 25, 2005 3:27 PM
To: plusplus@stripped
Subject: Re: Retrieving 300K+ records

Alex Vishnev wrote:
> Hello,
> 
> I am not sure if this is OT for this list. If so, please direct me to the
> proper place. I am using mysql++ to retrieve rows from a table. The table
> has between 300K-400K rows in it. the table is not indexed. I need to
> retrieve all the rows and load them into memory. I am using ResUse class
to
> receive the records from the query. See below
> 
>         Query q << "select * from table";
> 
>         mysqlpp::ResUse use = q.use();
>         while ((row = use.fetch_row()))
>         {
>                 string col1 = (string)row.lookup_by_name("col1");
>                 string col2 = (string)row.lookup_by_name("col2")
>                 float col3 = row.lookup_by_name("col3");
>         }
> 
> It takes approx 12-14sec to read thru all the records. At the same time, I
> have similar application that reads 400K from a flat file and parses the
> data prior to loading into memory and it takes 2-3s. BTW, the process of
> loading the records into memory is the same for both applications. In both
> cases I am using the same computer (processor), same disks, same file
> system. In both cases the request to load file does not traverse the
network
> as both db and files are local to the system. So I am a little confused
why
> flat file loading is so much faster. I tried to increase per-connection
> buffers in my.cnf, but don't seem to matter so much. Here is what I
adjusted
> so far:
> 
> key_buffer = 128M
> max_allowed_packet = 1M
> table_cache = 256
> sort_buffer_size = 64M 
> read_buffer_size = 64M
> read_rnd_buffer_size = 4M
> myisam_sort_buffer_size = 64M
> thread_cache = 8
> query_cache_size= 16M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 4
> 
> has anyone experimented with something like that? can anyone explain why
it
> takes 3/4 times longer to load data from mysql then it is from a flat
file?
> Can anything be done to speed it up?
> 
> MySQL = 4.1.12-standard
> MySQL++ = 1.7.32
> OS =RH Linux ES3.0

In my application, for very very large queries I find that I have to bypass 
MySQL++ and go directly to the mysql layer. I find the Row object to be a
little 
slow; I don't notice it in smaller queries (under 1,000 records) but on very

large queries...it becomes noticeable.

-- 
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsub=1



Thread
SSQLS contained within class definition compilation issueswilliam.lieberman25 Oct
  • Retrieving 300K+ recordsAlex Vishnev25 Oct
    • Re: Retrieving 300K+ recordsEarl Miles25 Oct
      • RE: Retrieving 300K+ recordsAlex Vishnev25 Oct
        • Re: Retrieving 300K+ recordsWarren Young27 Oct
      • RE: Retrieving 300K+ recordsAlex Vishnev26 Oct
        • Re: Retrieving 300K+ recordsWarren Young27 Oct
          • RE: Retrieving 300K+ recordsAlex Vishnev27 Oct
    • Re: Retrieving 300K+ recordsChris Frey26 Oct
      • Re: Retrieving 300K+ recordsEarl Miles26 Oct
        • Re: Retrieving 300K+ recordsChris Frey26 Oct
          • RE: Retrieving 300K+ recordsAlex Vishnev26 Oct
            • Re: Retrieving 300K+ recordsEarl Miles26 Oct
            • Re: Retrieving 300K+ recordsChris Frey26 Oct
  • Re: SSQLS contained within class definition compilation issuesahnkle28 Oct
    • New FeaturesFabricio Mota29 Oct
RE: SSQLS contained within class definition compilation issueswilliam.lieberman25 Oct
  • Re: SSQLS contained within class definition compilation issuesWarren Young27 Oct
Re: SSQLS contained within class definition compilation issuesWarren Young27 Oct