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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
