Blob in 60 seconds

[BeeerFizz]

configuration:

server:
MySql 5.0.15-nt via tcp/ip
Client:
MySql Client Version 5.0.11
Operating system:
XP
Startup Variables:
Max Packet Size 1mb
Net Buffer Length 16k

I am using Inodb and the c/c++ api



Let me know if you need additional config info.

OK.

I have an app which scans documents and then stores them as a blob. The typical size of an image is around 22k.

During storage, the app batches about 100 of these images along with other information in structures in memory and then initiates a transaction and stores all 100, committing when finished.

This works as expected and the time to do this commit is a couple of seconds, anyway, it is fast enough that it is not an issue.

My problem is this:

When I go to retrieve a single image, it can take as long as 60 seconds. I've played around with this quite a lot and when monitoring the network, to see when the image is downloaded, the image appears to come down right away and then the statement hangs/waits for a timeout before returning... as long as 60 seconds.

The statement which hangs is the:
imageRes_set = mysql_store_result(m_mysqlConn);

QStr.Format("select Image from docimages where BatchNum = %s and ItemId = %s and Side = \"%c\"", BatchNum, ItemId, 'F'); 

mysql_query(m_mysqlConn, LPCSTR(QStr)); 
int ret = mysql_errno(m_mysqlConn); 
if (mysql_errno(m_mysqlConn) != 0) { 
str.Format("Error doing Preset Query 51 - %s\nFailure to do Select on Image", mysql_error(m_mysqlConn)); 
AfxMessageBox(str); 
Close_Database_Connection(); 
return(false); 
} 


imageRes_set = mysql_store_result(m_mysqlConn); 
row = mysql_fetch_row(imageRes_set); 
if (row == NULL) { 
// Does not exist -- So inform and leave 
Mess1.Format("************ The Image, in batch num (%s) does not exist in the Data Base ************\n\n ", BatchNum); 
Mess2 = "\tIt is not possible to View and Key an non existing Image\n"; 
AfxMessageBox (Mess1 + Mess2, MB_OK | MB_APPLMODAL | MB_ICONQUESTION, 0 ); 
Close_Database_Connection(); 
return(false); 
}

 

[TonyGroves]

Do you have an index on the search field-set (batchnum, itemid, side)?

 

[BeeerFizz]

Hi Tony,

thanks for the response...

yes, all three columns are indexed.

DROP TABLE IF EXISTS docimages;

CREATE TABLE docimages (
	BatchNum 	varchar(8) NOT NULL,
	ItemId 		int NOT NULL,
	Side		varchar(1),			#	f - front, b - back

	Image		blob,				#	Good for 64k (my tiffs are less than 30k)
	
	PRIMARY KEY (Batchnum, ItemId, Side)

)	TYPE = innoDB;

 

[TonyGroves]

OK, and of course the lack of index would only cause a delay at the server end, whereas your problem seems to lie in the client.

Are you freeing the result set when you're finished with it, by calling mysql_free_result()? If not, you could be eating up memory, and eventually turning to swap, which would make mysql_store_result() very slow.

 

[BeeerFizz]

Thanks Tony,

No, I am not calling mysql_free_result(). I need to put that in.

However, the issue happens with the first retrieval of a blob.

Any other ideas or things I might look at?

 

[BeeerFizz]

Is there something I'm missing on the setup of the server??

Anyone any ideas?

 

[TonyGroves]

Not really, but you could try reordering the table:
[tt]
ALTER TABLE tblname ORDER BY fld1,fld2,fld3
[/tt]
This will cause a physical reordering of the records, which might produce some speed improvement, but probably not dramatically.