MySQL very slow, AdUseServer, AdUseClient (Data from access) 2

[x508]

Hi.

I migrated my data from access to MySQL.

Everything seems to work fine when My conenction curser is set to UseServer.

The problem however is, that this does not seem to support abilities such as .RecordCount, Which allways return -1

I can while...rs.movenext...wend through it, but the .RecordCount always shows -1.

This would not do cause I need to use datagrids etc. in my app.

When I set the connection to UseClient, I can get the .RecordCount, but It takes 20 times longer(than with UseServer) to return the data (4000 Rows).

I'm really stumped......I have allready tried to change the recordset opject cursor, from OpenDynamic to OpenKeyset.



Any Help would be GRREATLY appreciated.

P.S.>Might it be a problem with indexes or something that did not import correctly?

My acces Tables had an autocreated PrimpraryKey column "ID", which auto-increments in each table.

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[johnwm]

Why do you need .Recordcount to use a DataGrid? How are you loading your DataGrid?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[x508]

Set dgReports.DataSource = rs

It says "The rowset is not bookmarkable"
This seems to happen when the rs.RecordCount returns -1

When I use adUseClient, .RecordCount returns fine and datagrid works fine, but it takes 20 seconds to open recordset

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[BobRodes]

I wrote an FAQ called "a nickel tour of ADO recordsets" or some such a while ago, that explains some of this. (Sorry, I haven't figured out yet how to put links to FAQs.)

Your rowset is not bookmarkable on the server side because the server side defaults to a forwardonly cursor, whereas the client side defaults to a static cursor. If you want the server side cursor to do a recordcount, use

myserversideRS.CursorType = adOpenStatic

which should work. Forwardonly only supports MoveNext, and recordcount has an implicit MoveLast.

Also, Keyset cursors don't do a correct recordcount until the recordset is "fully populated". A keyset cursor only returns the keys to the records in the recordset, and then fetches the data associated with a given key on an as needed basis. To get a proper recordcount with a keyset cursor, do a movelast and then try it.

Now, the reason that the client side cursor takes longer is because the recordset has to be sent to the client machine. You might find that stored procedures run faster, if you're not using them now.

 

[LostInCode]

When a Client cursor is used, all the data is sent to the client, as mentioned.
With a Server cursor, only a list of the indexes are sent to the client, and a small number of (current) records depending on the CacheSize setting.

You can use a client side cursor where the initial data is fetched, and the remaining rows are fetched asynchron, populated in the background (Optional argument on the rs.Open method, which is set to adAsyncFetch).
Then you shouldn't see a delay hardly at all over a server side cursor in showing the initial data.

 

[x508]

Ahhh thanks lost, this looks promising

Bob

>>>myserversideRS.CursorType = adOpenStatic

I have tried this and still it did not work, on the MySQL site they say that there is a problem with the MySQL recordcount, as MySQL does not represent it corretly, and that you should use UseClient cursor to get that functionality, thus...I think that L-in-C's post might be helpfull in this scenario.

Thanks for all yar help


*****************************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[johnwm]

BobRodes
To link to a faq just type in faq 222-2244. Miss out the space between faq and 222 and you get faq222-2244

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[LostInCode]

In a data grid you can also check it's ApproxRows property to find a recordcount, or just issue a second COUNT() select statement with the same criteria.

 

[BobRodes]

Thanks for the info, johnwm. I had the feeling you would weigh in with that!

And thank you, lost, for the tip about adAsyncFetch. I didn't fully grasp how that worked. Now, in my understanding, the only type of recordset that only sends keys is a keyset type, whereas static or dynamic will actually send the whole record. Am I wrong about this?

Bob