Iterate a mysql resultset which has 10000 rows, it takes 3.5 seconds. Is it normal?

Question

I use C++ and mysql++ to get a resultset and iterate it with fetch_row()

The resultset has 10000 rows, this "While" Loop took nearly 3.5 seconds.(I have comment out the content in while loop.)

is this situation normal? I thought it should completed much faster!

Connection* conn = ConnPool::getSingletonPtr()->getConn();
Query qr = conn->query(sql);

SYSTEMTIME lpsystime;  
GetLocalTime(&lpsystime);  

UseQueryResult res = qr.use();
while(FryRow row = res.fetch_row())
{
    /*
    MyObject obj;
    for(int i=0; i<row.size(); i++)
    {
        obj.setValue(res.fetch_field(i).name(), row[i]);
    }
    objList->push_back(obj);
    */
}

GetLocalTime(&lpsystime);  

MyObject has properties:

 int procedureNo;
 int index;
 int employeeNo;
 int procCount;
 int state;
 int procPermission;
 int procDeadline;
 int advanceAlert;
 DateTime procTime;
 int resultFlag;
 string comment;
 int flowDirection;
 int isHideComment;
 int isTrack;
 DateTime arriveTime;
 string preNodesJsonStr;
 string nextNodesJsonStr;
 string attachStr;
 string employeeName;
 DateTime employeeBirthDay;

**************************Spliter***************************** Thank you guys! I modified the code and measured the time again like this:

Connection* conn = ConnPool::getSingletonPtr()->getConn();
Query qr = conn->query(sql);

SYSTEMTIME lpsystime;  
GetLocalTime(&lpsystime);  // get the time before use().

UseQueryResult res = qr.use();

GetLocalTime(&lpsystime);  // get the time before While loop.


while(FryRow row = res.fetch_row())
{
        /*
        MyObject obj;
        for(int i=0; i<row.size(); i++)
        {
            obj.setValue(res.fetch_field(i).name(), row[i]);
        }
        objList->push_back(obj);
        */
}

GetLocalTime(&lpsystime);  // get the time when While loop finished.

the use() function only costs 10 ms.

ant the While loop costs 1.677 seconds.

If I don't comment out the content in while Loop. It costs 3.386 seconds.

The setValue() function is defined as below:

MyObject::setValue(const char * colName, const mysqlpp::String& ele)
{ 
 if(strcmp(colName,"column010") == 0)
      procedureNo = ele;
 else if(strcmp(colName,"column020") == 0)
      index = ele;
 else if(strcmp(colName,"column030") == 0)
      employeeNo = ele;
 else if(strcmp(colName,"column040") == 0)
      procCount = ele;
 else if(strcmp(colName,"column050") == 0)
      state = ele;
 else if(strcmp(colName,"column060") == 0)
      procPermission = ele;
 else if(strcmp(colName,"column070") == 0)
      procDeadline = ele;
 else if(strcmp(colName,"column080") == 0)
      advanceAlert = ele;
 else if(strcmp(colName,"column090") == 0)
      procTime = ele;
 else if(strcmp(colName,"column100") == 0)
      resultFlag = ele;
 else if(strcmp(colName,"column110") == 0)
      comment = ele;
 else if(strcmp(colName,"column120") == 0)
      flowDirection = ele;
 else if(strcmp(colName,"column130") == 0)
      isHideComment = ele;
 else if(strcmp(colName,"column140") == 0)
      isTrack = ele;
 else if(strcmp(colName,"column150") == 0)
      arriveTime = ele;
 else if(strcmp(colName,"column160") == 0)
      preNodesJsonStr = ele;
 else if(strcmp(colName,"column170") == 0)
      nextNodesJsonStr = ele;
 else if(strcmp(colName,"column180") == 0)
      attachStr = ele;
 else if(strcmp(colName,"column190") == 0)
      employeeName = ele;
 else if(strcmp(colName,"column200") == 0)
      employeeBirthDay = ele;
}

Answer 1

Your problem is that you also measure the execution time of your query. conn->query(sql); does not execute the query. It just builds a Query object. The use method then actually executes the query. I.e., in your code, this line actually executes the query:

UseQueryResult res = qr.use();

From the mysql++ documentation:

 UseQueryResult mysqlpp::Query::use ()  

Execute a query that can return rows, with access to the rows in sequence.Use one of the use() overloads if memory efficiency is important. They return an object that can walk through the result records one by one, without fetching the entire result set from the server.

But note that even if you pull the use call out, you might still get some query execution time in your loop, because use pulls the rows one by one. Consequently, the database executes the query tuple by tuple and only computes the next tuple when you take it from the result. If you really want to measure a loop through all of the results, you must use the store function instead. It executes the query thoroughly and stores the result in a block of memory. Then, your loop will be blazingly fast. However, you should still prefer use because first storing all results into memory is usually just a waste of time and memory.