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 100000+ 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
PM 100000+ 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
100000+
>records
>Date: Wed, 30 Jun 2004 11:01:29 -0400
>
>
>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
> AM 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]
>
>
>
>
>
>
_________________________________________________________________ Get fast, reliable Internet access with MSN 9 Dial-up ? now 3 months FREE! http://join.msn.click-url.com/go/onm00200361ave/direct/01/
_________________________________________________________________
Make the most of your family vacation with tips from the MSN Family Travel Guide! http://dollar.msn.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
