List:MySQL++« Previous MessageNext Message »
From:Earl Miles Date:October 25 2005 7:26pm
Subject:Re: Retrieving 300K+ records
View as plain text  
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.
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