[ODBC-92] Array fetch performance degrades drastically for smaller array size ... Created: 2017-04-07  Updated: 2018-05-16

Status: Open
Project: MariaDB Connector/ODBC
Component/s: None
Affects Version/s: 2.0.13
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Rahul Dhuvad Assignee: Lawrin Novitsky
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 64-bit

Attachments: File maodbc.dll     File maodbc.dll    

 Description   

MariaDB ODBC driver performance gets degraded drastically when array size (SQL_ATTR_ROWSET_SIZE) is reduced to 1000 or less. For example, to fetch 500K records (3 columns):

SQL_ATTR_ROWSET_SIZE: 100000, took 4.8 seconds
SQL_ATTR_ROWSET_SIZE: 10000, took 5 seconds
SQL_ATTR_ROWSET_SIZE: 1000, took 8.7 seconds
*SQL_ATTR_ROWSET_SIZE: 100, took 55 seconds
SQL_ATTR_ROWSET_SIZE: 10, took 516 seconds (that's close to 9 mins!)*

If I compare same with MySQL 5.3.7 ODBC driver,
7.3 sec, 6.5 sec, 6.6 sec, 6.8 sec, 8.2 sec respectively, which is fairly working.

I don't think table schema really matters, but this is what I've:
mysql> desc sampleTable2;
---------------------------------------------------------------------------+

Field Type Null Key Default Extra

---------------------------------------------------------------------------+

COL_INT int(11) YES   NULL  
COL_CHAR250 char(250) YES   NULL  
COL_TIMESTAMP timestamp NO   CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

---------------------------------------------------------------------------+

 Comments   
Comment by Lawrin Novitsky [ 2017-04-07 ]

Hi Rahul,

Thanks for the report. Would it be possible to try if the attached library makes difference? I fixed something that looks like most probably cause of the described performance degradation.

The library is 32b windows. If you need smth else - please let me know.

Best regards,
Lawrin

Comment by Rahul Dhuvad [ 2017-04-07 ]

Looks like it's 32-bit dll? Can you please upload 64-bit one?

Comment by Lawrin Novitsky [ 2017-04-07 ]

Yes. The new file is 64bit

Comment by Rahul Dhuvad [ 2017-04-07 ]

Thanks Lawrin; I tried the new one, but I don't see any improvement to the earlier performance. In fact for array size 100, it's now taking 77 seconds for fetching 500K rows (which used to take 55 seconds with 2.0.13 dll ).

Comment by Lawrin Novitsky [ 2017-04-07 ]

Hmm. Well, I can understand that there is no improvement. I tried the most low hanging fruit. But why would it be slower. Although you are comparing against 2.0.13, and there were many changes since then, not only the one I wanted to try.

Comment by Rahul Dhuvad [ 2017-04-10 ]

Yeah; that's surprising to me as well! Let me know if you seek any other details from me for this issue? Issue is crucial for us because our array size is dynamic and that depends on our cache size and row-size of the row-set. So if row-size is more than array size will be reduced and for larger row-size, array size may get reduced to 1000 or less where this issue kills the performance as you can imagine.

Generated at Thu Feb 08 03:26:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.