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 is
RE: MySQL and VBquestion - problem with query that returns 100000 + records
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]
Re: MySQL and VBquestion - problem with query that returns 100000+ records
Your SQL syntax is fine, I just get a better view of what is joining to what if I format it a little differently (I also avoid a lot of line wrapping this way too): 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 Transaction T ON PR.ProviderID = T.ProviderID INNER JOIN Person P ON P.PersonID = T.PersonID INNER JOIN Staff S ON T.CreatedBy = S.StaffID LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID WHERE T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC It may not be hung but just taking a *long* time to resolve this query. Did the error log show anything? Do all of your tables have the appropriate indexes to speed up your JOIN statements? Could you post the results of an EXPLAIN on your query? What version of MySQL are you using? You might be able to speed this up by running it as two queries (using a temp table to store the results of the first part of the query) rather than one large query. Depending on how many columns you have in each table, your internal tableset (the result of all of those joins) could contain hundreds of columns. By splitting it into a couple of smaller steps you help keep the number of unused columns per stage to a reasonable number. You may also get more speed out of this if you move your WHERE constraints into the appropriate ON clauses: FROM Provider PR INNER JOIN Transaction T ON PR.ProviderID = T.ProviderID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 INNER JOIN Person P ON P.PersonID = T.PersonID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 INNER JOIN Staff S ON T.CreatedBy = S.StaffID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 LEFT JOIN PatientService PS ON T.TransactionID = PS.TransactionID AND T.ServPayAdjIDString)'inibal' AND T.IsDeleted=0 LEFT JOIN Diagnosis D ON PS.DiagnosisID1 = D.DiagnosisID ORDER BY PR.LastName, PR.FirstName, T.TranDateTime DESC It looks bulkier but if you do it this way you give the MySQL engine every possible opportunity to pare down your JOINed datasets. Generally, the less data you have to process, the faster everything will go. You *cannot* always move a WHERE condition into an ON clause, but in this case it was possible to do so with all of your WHERE conditions. This *is* a case-by-case optimization. And, last but not least, you may want to read up on an ongoing discussion of ways to reduce the speed hit when using ORDER BY...DESC. It is a known issue. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Greg Zimmermack [EMAIL PROTECTED]To: [EMAIL PROTECTED] ail.com cc: Fax to: 06/30/2004 10:13 Subject: MySQL and VBquestion - problem with query that returns AM10+ 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.
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]
RE: MySQL and VBquestion - problem with query that returns 100000 + records
In ODBC applications, I have seen the application give a not responding message when the database is taking longer than expected to return a result set. ... You say you see the query still executing? So the select statement is just taking a long time to process? If this is so I would try to optimize the query. Have you run an explain plan on the query to see the execution path? ... You could also try using the ODBC trace feature in the 'ODBC Data Source Administrator' -Original Message- From: Greg Zimmermack To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: 6/30/04 10:52 AM Subject: RE: MySQL and VBquestion - problem with query that returns 10+ 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]
Re: MySQL and VBquestion - problem with query that returns 100000+ records
I have to ask, why would you even want to pull that many records at once? No user would want to sift through that many records. I always add a limit clause to all my queries. There is no harm in having a limit 500 when you are just receiving 10 records. But it's good to have the limit there in case your query has a bug. It may be that things are hanging on the transfer of that much data from MySQL to ODBC to VB. If you add a LIMIT 25 at the end of the query statement, does it work then? On Jun 30, 2004, at 11:52 AM, Greg Zimmermack wrote: 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 -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
