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 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 100000+ >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 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] > >-- >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]
