Drivers to use with VB6
I am using VB6 and ADO in conjunction with ODBC inorder to connect to a MySQL database. This is seems to be considerably slowing down my application. Is there anyother mode of connecting besides ODBC - does anybody have any experience with MyVBQL.dll or MYOLEDB - if so, please share your experiences. Thanks Greg _ Get tips for maintaining your PC, notebook accessories and reviews in Technology 101. http://special.msn.com/tech/technology101.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and VBquestion - problem with query that returns 100000+ records
Shawn I took your advice and let the app run for a good 12+ minutes - miraculously it worked and the resulting report did show up.I am now convinced that the status shown in the task manager is misleading. I am trying to implement your suggestion of using an asynchronous call. So I am doing something like Set Rs = New ADODB.Recordset Rs.CursorLocation = adUseServer 'adUseClient Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch Question is how do i poll for the status of adStateExecuting and adStateFetching ? Will the execution get to the 'do loop' at all (before the Rs.Open has completed) if I do something like: Rs.Open SQL, connection, adOpenForwardOnly, adLockReadOnly, adAsyncFetch do while (adStateExecuting and adStateFetching ) . . DoEvents loop OR Do I need to do something like Dim WithEvents Rs as ADODB.recordset and then use the corresponding event handler. The irony of the entire situation is that we were thinking of migrating to MySQL in hopes of improving the performance over MS Access. While there might me other advantages of using MySQL over MS Access, it is definitely failing on the performance front and thats a shame. I am also going to try your modified query and will post the results of the EXPLAIN soon. Thanks a lot for all your help; Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 12:37:16 -0400 Greg, I think what you are calling hung may really be a blocked condition. Using ADO, you have the option of your queries being synchronous or asynchronous. A synchronous query will wait for the results before returning control to your program. In other words, your application hands over total control of it's execution to ADO. ADO submits your query then goes to sleep until one of several things occurs: it gets a response from the server, it times out, or any of the other events that will also rouse ADO from its slumber. The task manager in windows checks the status of the various executing applications by sending them windows messages. Since your application is in essence frozen until ADO returns from its call, your application will not respond to any windows messages. Other things like move window, resize window, and close window are also windows messages so it will seem to be completely unresponsive to any action you take until ADO releases its control over the situation. Try setting up the request as an asynchronous call (set the option on your open to adAsynchFetch) and periodically monitoring the state of your recordset for adStateExecuting and adStateFetching (may I suggest a do...loop, a while...wend, or a timer object? ) to see if you are bound by the query's execution or the data retrieval. Your application will also be able to respond to the windows messages as its default message handler will not be waiting on ADO to return. (You can force the default message handler to deal with any outstanding messages by executing a DoEvents call.) You said earlier that if you tried the query manually, you only saw 1000 rows. That clues me in that you are using one of the GUI admin tools as your manual interface. Some of them limit database responses to only the first 1000 rows. I don't know if you can change that value or not, you will have to consult the docs for your tool. However, in the same location where you execute your SQL by hand just add the word EXPLAIN to the beginning of it - EXPLAIN SELECT . (rest of statement) - and you will get a response from the server detailing how the engine plans to deal with that particular query. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Greg Zimmermack [EMAIL PROTECTED]To: [EMAIL PROTECTED] ail.com cc: Fax to: 06/30/2004 12:00 Subject: Re: MySQL and VBquestion - problem with query that returns PM10+ records Thanks for your reply Shawn I will try running the modified query you sent me and check if that helps - meanwhile here are the replies to some of your questions. *I know the application is hung by checking its status in the Windows Task manager( it shows the status as 'Not Responding'). It looks like the app hangs before the query is done executing *The version of MySQL is 4.0.16-nt *I am not sure how to do the EXPLAIN you mentioned. *I removed the ORDERBY clause but that did not make any difference. Appreciate your help Greg From: [EMAIL PROTECTED] To: Greg Zimmermack [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 11:01:29 -0400 Your SQL syntax
MySQL and VBquestion - problem with query that returns 100000+ records
I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and VBquestion - problem with query that returns 100000+ records
Victor Thanks for your reply. I checked the processlist and it continues to execute even after the VB application is hung. I know the application is hung by checking its status in the Windows Task manager. The 'hostname.err' file did not show any errors. Is there another file i should be looking at? The error handler in in VB could not handle the error. Is there some ODBC setting I need to optimize? Any help will be appreciated. Thanks Greg From: Victor Pendleton [EMAIL PROTECTED] To: 'Greg Zimmermack ' [EMAIL PROTECTED],'[EMAIL PROTECTED] ' [EMAIL PROTECTED] Subject: RE: MySQL and VBquestion - problem with query that returns 10+ records Date: Wed, 30 Jun 2004 09:19:11 -0500 While the Visual Basic application is querying the database can you log into the MySQL via the mysql monitor and do a show processlist to see if the query is executing? If not I would suggest you view the mysql error log or set up Visual basic to throw and error when it is getting disconnected. -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED] Sent: 6/30/04 9:13 AM Subject: MySQL and VBquestion - problem with query that returns 10+ records I have an SQL which looks something like: SELECT S.FirstName, T.CreateDateTime, T.TranDateTime, P.FirstName, P.LastName, D.DiagnosisIDString, T.CheckNumber, T.StmtDesc, T.ServPayAdjIDString, T.PatientAmt, T.InsuranceAmt, T.ClaimID, T.AuditDateTime, T.TransactionType, T.ProviderID, PR.FirstName, PR.LastName FROM (((Provider PR INNER JOIN (Person P INNER JOIN Transaction T ON P.PersonID = T.PersonID) ON PR.ProviderID = T.ProviderID) LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID) LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID) INNER JOIN Staff S ON T.CreatedBy = S.StaffID WHERE (((T.ServPayAdjIDString)'inibal') AND ((T.IsDeleted)=0)) ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC This query is invoked from a Visual Basic 6.0 application that uses ADO ODBC to connect to a MySQL database. The query runs fine on a small test database but when i run it on the production database the application just hangs up without generating any error messages. The query is expected to return some 196000 records from the production database. Can this (large number of records) be causing the problem? Is there any setting in MySQL that I could change ? If i run the query on the server i can see upto 1000 records but no more. This is my first attempt at MySQL hence the newbie type questions. I should also mention that the application in question originally used an MS Access database and it can pull all the 196000 records from it. Any help will be greatly appreciated. Thanks Greg _ Get fast, reliable Internet access with MSN 9 Dial-up - now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ From will you? to I do, MSN Life Events is your resource for Getting Married. http://lifeevents.msn.com/category.aspx?cid=married -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
