Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-07-23 : 14:52:16
|
| Hiya,Here is a query: SELECT P.ID AS PatID,P.Account, (RTRIM (P.LastName) + ',' + RTRIM (P.FirstName) + ' ' + P.MidInitial) AS PName,P.Address AS PtAddress,P.City AS PtCity,P.State AS PtState,P.ZipCode AS PtZipCode,P.HomePhone,P.DOB,(CASE P.Sex WHEN 'F' THEN 'X' END) AS PFemale,(CASE P.Sex WHEN 'M' THEN 'X' END) AS PMale, (CASE P.MaritalStatus WHEN 'Married' THEN 'X' END) AS PMarried, (CASE WHEN P.MaritalStatus = 'Single' THEN 'X' END) AS PSingle, (CASE P.MaritalStatus WHEN 'Other' THEN 'X' END) AS POther, PA.DateFirstSymptom, PA.DateSimilarSymptom, PA.DateDisabledFrom, PA.DateDisabledTo, (CASE WHEN PA.IsLabWork = 0 THEN 'X' END) AS NLabWork, (CASE WHEN PA.IsLabWork = 1 THEN 'X' END) AS YLabWork, (CASE WHEN PA.EmpOrCrime = 'E' THEN 'X' END) AS YEmployment,(CASE PA.EmpOrCrime WHEN 'C' THEN 'X' END) AS NEmployment, Pti.SubscriberNo, Pti.PHName, PtI.PHAddress, PtI.PHCity, PtI.PHState, Pti.PHZip, PtI.PHDOB,(CASE PtI.PHSex WHEN 'M' THEN 'X' END) AS IMale, (CASE PtI.PHSex WHEN 'F' THEN 'X' END) AS IFemale, PtI.PHEmployer, (CASE PtI.PHRelation WHEN 'Self' THEN 'X' END) AS ISelf, (CASE PtI.PHRelation WHEN 'Child' THEN 'X' END) AS IChild, (CASE WHEN PtI.PHRelation = 'Wife' OR PtI.PHRelation = 'Husband' THEN 'X' END) ASISpouse, (CASE WHEN PtI.PHRelation = '0ther' OR PtI.PHRelation = 'Legal Guardian' OR PtI.PHRelation = 'Parent' THEN 'X' END) AS IOther, PtI.GroupNo,(CASE WHEN H.Assignment = 1 THEN 'X' END) AS YAssign, (CASE WHEN H.Assignment = 0 THEN 'X' END) AS NAssign, H.ProNumber, H.VoucherNo,H.Insco, (CASE WHEN I.SSNorEIN = 'S' THEN D.SSN ELSE D.TaxID END) AS TaxID, (CASE WHEN I.SSNorEIN = 'S' THEN 'X' END) AS SSN, (CASE WHEN I.SSNorEIN = 'T' THEN 'X' END) AS EIN, I.Name AS InsName,I.Address As InsAddress, (RTRIM (I.City) + ', ' + I.State + ' ' + I.Zip) AS InsCSZ, (CASE WHEN I.InsType <> 1 AND I.InsType <> 2 THEN 'X' END) AS OtherIns, (CASEWHEN I.InsType = 1 THEN 'X' END) AS MCIns, (CASE WHEN I.InsType = 2 THEN 'X' END) AS MDIns, I.Attention, I2.I2Name, I2.I2PHName, I2.I2GroupNo, I2.I2PhDob,I2.I2PhEmp, I2.I2Male, I2.I2Female, (CASE WHEN I2Name IS NOT NULL THEN 'X' END) AS YAnotherIns, (CASE WHEN I2Name IS NULL THEN 'X' END) AS NAnotherIns, (CASE WHEN D.GroupName IS NOT NULL THEN D.GroupName ELSE RTRIM (D.FirstName) + ' ' + D.LastName END) AS DrName, D.Address AS DrAddress, D.City AS DrCity, (',' + D.State + ' ' + D.ZipCode) AS DrStateZip, D.Phone AS DrPhone, D.MedicareLic AS License, D.GroupLic, H.POSName, H.POSAddress, H.POSCity, H.POSState, H.POSZip, H.TypeOfService, H.CptDesc, H.IsConsult, H.IsUPIN, (CASE WHEN H.IsConsult = 1 THEN (RTRIM(RP.FirstName) + ' ' + RP.LastName) ELSE D.UPINName END) AS RefDr, (CASE WHEN H.IsConsult = 1 THEN RP.MedicareLic ELSE D.MedicareLic END) AS RefUPIN, (H.VoucherNo + ((H.LineNumber - 1) / 6)) AS GVoucherNo, H.ServiceDate,H.ToDate, H.PlaceOfservice, H.ProcCode, H.Modifier1, H.Modifier2, H.Modifier3, H.Diag1, H.Diag2, H.Diag3, H.Diag4, H.Charge, H.AmountPaid,H.Units, H.SingleBalance, ##T.TDiag1, ##T.TDiag2, ##T.TDiag3, ##T.TDiag4, ##T.TDesc1, ##T.TDesc2, ##T.TDesc3, ##T.TDesc4 FROM (SELECT Insname I2Name, [Expiredate], PatID, Inscocode, PHName I2PhName, PHDOB I2PhDob, CASE WHEN PHsex = 'F' THEN 'X' END I2Female, CASE WHEN PHsex = 'M' THEN 'X' END I2Male, PHEmployer I2PhEmp, GroupNo I2GroupNo, PrimaryOrSec AS PriSec FROM InsuranceTabRS) I2 RIGHT JOIN PatientIns PtI RIGHT JOIN RelatedPatInfo PA RIGHT JOIN ReferringPhysicians RP RIGHT JOIN Patients P INNER JOIN Doctors D INNER JOIN Inscompanies I RIGHT JOIN vwHCFAVouch H INNER JOIN ##T ON H.VoucherNo = ##T.Voucher ON I.Code = H.InscoON D.Code = H.DrCode ON P.[ID] = H.PatID ON RP.Code = P.ReferralID ON PA.[ID] = P.[ID] ON PtI.PatID = P.[ID] AND PtI.InsCoCode = H.Insco ON I2.PatID = P.[ID] AND I2.InsCoCode <> H.Insco AND I2.[ExpireDate] > H.ServiceDate AND I2.PriSec = (CASE WHEN PtI.PrimaryOrSec = 'P' THEN 'S' ELSE 'P' END)WHERE H.ServiceDate BETWEEN @Start AND @End AND (PtI.PrimaryOrSec = @Primary OR PtI.PrimaryOrSec = @Secondary)Optimally,it takes a mere 1-2 seconds to run to return about 10 rows. The problem: The PtI.PrimaryOrSec field may be null in the instance that the patient has no rows in that table, i.e. not the actual field is null, but there are no rows, so using ISNULL in the SELECT for that field won't work. The PrimaryOrSec criteria in the WHERE clause will presently exclude null data,which is not what I want. But adding an ISNULL(Pti.PrimaryOrSec,@Primary)...in the WHERE clause slows down the query to approx. 35 seconds, and the plan changes. Adding another OR in the WHERE clause to (Pti.PrimaryOrSec = .... OR PtI.PrimaryOrSec IS NULL) slows to approx. 30 seconds, same plan as using ISNULL. Using COALESCE in the WHERE clause does something similar. Moving this part of the WHERE clause into the JOIN part "ON PtI.PatID = P.[ID] AND PtI.InsCoCode = H.Insco AND (Pti.PrimaryOrSec.....)" slows to approx. 25 seconds.What's a good way to do this?Sarah Berger MCSD |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 15:10:30
|
| Since its an outer join, you probably want the condition (PtI.PrimaryOrSec = @Primary OR PtI.PrimaryOrSec = @Secondary) in the JOIN. Maybe try pti.primaryorsec in (@primary,@secondary). Also, review the execution plan for indexing opportunities.<O> |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-23 : 15:27:42
|
See this: quote:
Moving this part of the WHERE clause into the JOIN part "ON PtI.PatID = P.[ID] AND PtI.InsCoCode = H.Insco AND (Pti.PrimaryOrSec.....)" slows to approx. 25 seconds.
This is unacceptable timing. And just about every possible column is indexed. The plan shows no table scans except for the ##T table, which is small anyway.Sarah Berger MCSD |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 15:50:36
|
quote:
it takes a mere 1-2 seconds to run to return about 10 rows
If it takes 2 seconds to conjure up the execution plan and begin spooling records, but takes 25 seconds to finish returning all the records, then that would point to I/O or network bottleneck.<O> |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-23 : 16:02:09
|
| Nah, it takes about 2 seconds without the tests for NULL put in. But then it won't return all the data I need. Please see the lengthy problem description.Sarah Berger MCSD |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-25 : 12:35:29
|
| Hiya, I have narrowed down the problem, and it is not so much the nulls as there is a bigger problem now. I have taken all the tables and put them into a view, except for the I2 derived table, and the ##T table. When I query the view like this:SELECT * FROM vw WHERE Voucher = 30000, I get the data in one second. Good.But when I try to join the view on the ##T table which only has one record with a voucher number of 30000, it takes 30 seconds to return the data. I am having difficulty reading the showplan output, so I'll just write what I guess is happening: when I select the data directly from the view, the optimizer goes directly to the large Vouchers and Vouchersdetail tables and fishes out the correct row/rows, and then selects the rest of the data from the other tables, and so it does one large filter operation, and then estimates low numbers of rows from the other tables, which are all indexed anyway. OTOH, when the query is run to join on the ##T, I dunno what the optimizer thinks. How can I improve this situation?Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-25 : 12:55:03
|
| Are there any table scans in your showplan output? They'll be marked pretty clearly as table scans. Also look for index scans and make a note of which index is scanned.Also, have you indexed the temp table at all? How many rows are in the temp table? Also, find out what kind of join the optimizer is using to join to the temp table (loop, hash, merge, etc.) You might want to try some join hints to see if they improve the performance. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-25 : 13:50:08
|
| There are no table scans at all. There are two index scans, one if which is 15% of the cost. It is on the Vouchers table which is in the view, so even though I specified a different index WITH(Index (indexname)) after the view name, the optimizer still chose the other one.The temp table is indexed. Doubtfully, it will ever hold more than a couple of hundred rows. Presently, it has one row. The join between the temp table and the vouchers table is a Nested Loops/Left Semi Join (what's that?).Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-25 : 14:18:34
|
| The 3 join types (loop, merge, and hash) are processed differently in order to take advantage of certain favorable conditions in the tables being SELECTed from. If the tables are properly indexed, then a merge join is usually the fastest method, but the optimizer is not likely to favor a merge join. Under certain other conditions, a hash join performs best. Loop joins are standard.The joins types are (sort of) documented in Books Online, at least hash and merge are. Try using a merge join between the temp table and Vouchers, then try a hash join. Make sure you perform a DBCC DROPCLEANBUFFERS before you run each variation, to flush out the data cache and get more accurate results. Also make sure showplan is on, even if you indicate a specific join type the optimizer might override it somehow (like it did with the index hint)You might want to consider running the Index Tuning Wizard too, but I don't think it will optimize for this particular query unless it represents the majority of the requests made. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-25 : 16:44:58
|
| Using HASH and MERGE joins have only slowed down the query, because it is not affecting the crucial innermost join between the temp table and the view, which remains a Nested loops, and only affects the other joins which are mostly OUTER joins and HASH and MERGE don't seem to be so great with those.(I understand the join types, but what I was wondering about the Left Semi Join is: If this particular join is supposed to be an INNER JOIN, why the Left Semi Join?)--I get this now. It is a sort of inner join by eliminating all rows from the right table with no match in the left by using the join predicate as a filter.Sarah Berger MCSDEdited by - simondeutsch on 07/25/2002 16:51:18 |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2002-07-25 : 16:54:43
|
| Wow havn't seen a select clause so much more complicated than the from or where clause like that before. The only queries I've ever made with select clauses larger than from/where clauses were for data imports. Health cares gotta suck. Hope you never have to import anything from Suburban Otstomy, or worse, MedLine. The SHRMs are much more pleasant.It would be a lot easier to read if the outer joins were left outer joins and that the join fields were in sql-92 format (in the from clause).If you don't need any data from one of the outer joins and only need to know if rows exists try an correlated subquery in an EXISTS() function (it would be cool if sql server let you define the semi-joins in the from clause).Oh yeah try to avoid ORs unless they are on the same field, but that could be bad for performance too espcially when mixed with 'IS NULL'ANd try tweaking the Join order. The optimizer doesn't spend forever optimizing join and on queries with many joins it often needs your help. If the optimizer is way over or under estimating the row counts in one or more of the joins then you might have to force the join order and optimize it all yourself (I find that forcing the join order is adequate though it takes more work/time to use only force order and not also hints bt it also has a tendency to go outdated less too). |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-07-25 : 17:17:57
|
Health care is pretty complicated. This information is for a HCFA form (red and white form). If you have ever seen one, it has very many fields.As I mentioned before, I put all tables into a view,except the derived and temp tables. The view is very fast, so indexes must be OK. Existence tests are no help here- all selected data is used. And ORs aren't bad in CASEs, as the view's performance shows. And I have gotten rid of the Null problem too, as far as the view is concerned, with not much performance hit. The query is much more streamlined now.quote:
ANd try tweaking the Join order
I won't touch the JOIN order now,it's all in a view, which runs great. At one point I tried FORCE ORDER, with a nice bit of speed degradation. It's a delicate thing.Sarah Berger MCSD |
|
|
|
Lana
Starting Member
6 Posts |
Posted - 2002-08-02 : 10:31:44
|
| You were saying your view was fast, and the derived table seems to be OK than it's possible that the problem is in the ##T the temp table. If selecting the data into the temp table does not require a dynamic SQL and you are running on SQL Server 2000 you could try using a table variable instead of the temp table to see if it makes any difference. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-08-04 : 14:04:37
|
| Hiya!I did a sitting session on this one, and got a really great but somewhat convoluted solution. I select into a new temp table all the rows I need from the view, and then join that with the global temp table, and after returning the output, drop both of them. This process now takes 5 seconds.Seems the problem was the join between the view and the global temp table as the plan wasn't optimized enough to eliminate enough rows from the start, and work with only that small rowset.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
Problem with nulls