MySQL Bugs: #14305: Memory usage enormous increases after SQLExecute call

Bug #14305	Memory usage enormous increases after SQLExecute call
Submitted:	25 Oct 2005 20:39	Modified:	13 Jul 2007 21:47
Reporter:	Vladimir Loubenski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
We noted that memory which are using by client application enormous grow in case reading data from a big table. Detail investigation shoes that this behavior happens after call ODBC function SQLExecute. It looks like all data, which is result of select statment, is sent to client after SQLExecute call.

How to repeat:
1. CREATE TABLE qq_big (F1 INTEGER NOT NULL, F2 VARCHAR(255) NULL )
2. Insert 100000 records into table.
3. Try to read data from the table. Note that memory usage increased on 55 MB after call SQLExecute for select statement even before fist call SQLFetch happened

I created test case and my memory usage increased on 6 MB. I think It is not fatality for query to big table. Could you please build and run my test case on your computer ? My test case is attached.

Test case

Attachment: test.c (text/plain), 4.88 KiB.

Hi Vasily,
Thank you for investigation.
I slightly modify the test case to clarify the problem.
1.During filling table string with length 255 bytes is added.
2.Select statement reads both column F1 and F2 because first column is integer and does not contains much actual data.

After such modification I got following results:
Number records in table       Virtual memory usage (Bytes)
100                                         1,048,576
1000                                       1,048,576
10,000                                    7,340,032
100,000                                  32,505,856

As we can see memory usage increases based on actual data in table and if number records will be big enough (>1,000,000) we will have serous problem with consumption memory.

Regards,
Vladimir.

Test case

Attachment: test.c (text/plain), 4.94 KiB.

Thanks for corrected test case. I was able to reproduce it.

All result set data is read into memory unless the "Don't Cache Result (forward only cursors)" option is enabled. This happens when the statement is executed, which is usually when SQLExecDirect() or SQLExecute() is called.