1. MySql handler syntax
- Posted by George Walters <gwalters at sc.rr.com> Oct 25, 2004
- 655 views
Matt, I'm still having memory problem with a large inventory database (22,000 records). I'm concerned because I do have some customers with more than 80,000 records.I've increased the computer memory to 750MB but although it reads farther, it still crashed w/o enough memory. So there's a problem somewhere that's way over my head. Would it help if I emailed you the mysql table to play with? I'm also wondering if I can use mysql "HANDLER syntax" (page 521 in the mysql manual). It seems it would be more appropriate to the type of interactive file maintenance my software is using. The reports should still use a cursor. This would be for the file maitenance and data entry programs. It would solve my "READPREVIOUS" problem which there's no realistic SQL syntax to handle it. george
2. Re: MySql handler syntax
- Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Oct 25, 2004
- 632 views
- Last edited Oct 26, 2004
George Walters wrote: > > Matt, I'm still having memory problem with a large inventory database > (22,000 records). I'm concerned because I do have some customers with > more than 80,000 records.I've increased the computer memory to 750MB but > although > it reads farther, it still crashed w/o enough memory. So there's a problem > somewhere that's way over my head. Would it help if I emailed you the mysql > table to play with? OK, I found a couple of memory leaks in getColumnHeaders() and getData(). I allocate(h_ptr) but never free it unless there is an error. Add in a call to free in both of those functions after the line:
cols = peek4u( h_ptr ) free( h_ptr )
Since you make lots more calls than just the 22,000, this could be the issue. > I'm also wondering if I can use mysql "HANDLER syntax" (page 521 in the mysql > manual). It seems it would be more appropriate to the type of interactive > file maintenance my software is using. The reports should still use a cursor. > This would be for the file maitenance and data entry programs. It would solve > my "READPREVIOUS" problem which there's no realistic SQL syntax to handle it. I tried using HANDLER, and it works fine...faster, in fact, than a series of SELECTs. Basically, it's just another piece of SQL that returns a recordset, as far as my code is concerned. I used:
void = execDirectODBC( mysql, "handler test open;") void = execDirectODBC( mysql, "handler test read first;") puts(1,"\n") for i = 1 to 100000 do void = execDirectODBC( mysql, sprintf("handler test read next;", {i-1})) end for
Also, if you use the sequence.e file I sent you to compress/decompress Euphoria objects, you can store them in BLOB fields. I created a table named test: +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | number | varchar(50) | YES | | NULL | | | seq | mediumblob | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ I added 100000 records (while trying to run myself out of memory), before I actually created the seq field. Then I did the following to put some BLOB data in there:
stmt = prepareSQL( mysql, "update test set seq=? where id=?") void = bindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_BINARY, #FFFF, 0, 0 ) void = bindParameter( stmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 4, 0, 0 ) for i = 1 to 100000 do setParameter( stmt, 1, compress({sprint( i )}), SQL_C_DEFAULT, SQL_BINARY ) setParameter( stmt, 2, i, SQL_C_LONG, SQL_INTEGER ) void = executeSQL( stmt ) end for
The data comes and goes just fine. Also tried running through records with HANDLER and with a cursor, and HANDLER seems faster up to about 3000 records (on my machine), but this is basically the overhead of the initial select statement. Subsequent calls to moveToRecord() are extremely fast, since the data is already prefetched. Of course, this may cause memory problems, and your speed might be fine as it is, so I'll leave this to you. Just thought it was interesting, because I've never worked with MySQL before, and the HANDLER statement was interesting. Matt Lewis Matt Lewis
3. Re: MySql handler syntax
- Posted by George Walters <gwalters at sc.rr.com> Oct 26, 2004
- 621 views
Neat!! Thanks for the help. I'll try them out... george PS: I don't remember sequence.e, couldn't find it in the emails. could you send it again?
4. Re: MySql handler syntax
- Posted by George Walters <gwalters at sc.rr.com> Oct 27, 2004
- 665 views
Just wanted to post the rest of this story. The memory problem I've been having was in my program, not freeing some handles. Matt's ODBC routines were solid and not the culprit. My lack of understanding how they should be used was the problem....So on with development. Matt found the problem in my code. Thanks for the help Matt!! george