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


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