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-06-24 : 22:42:20
|
| If this is the place to discuss nasty SELECTs with 20 tables, here's one: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) AS ISpouse, (CASE WHEN PtI.PHRelation = '0ther' OR PtI.PHRelation = 'Legal Guardian' OR PtI.PHRelation = 'Parent' THEN 'X' END) AS IOther, PtI.GroupNo, (CASE WHEN V.Assignment = 1THEN 'X' END) AS YAssign, (CASE WHEN V.Assignment = 0 THEN 'X' END) AS NAssign, V.ProNumber, V.VoucherNo,V.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, S.Name AS POSName, S.Address AS POSAddress, S.City AS POSCity, S.State AS POSState, S.Zip AS POSZip, C.TypeOfService, C.CptDesc, C.IsConsult, C.IsUPIN, (CASE WHEN C.IsConsult = 1 THEN (RTRIM(RP.FirstName) + ' ' + RP.LastName) ELSE D.UPINName END) AS RefDr,(CASE WHEN C.IsConsult = 1 THEN RP.MedicareLic ELSE D.MedicareLic END) AS RefUPIN, (V.VoucherNo + ((VD.LineNumber - 1) / 6)) AS GVoucherNo, VD.ServiceDate,VD.ToDate, VD.PlaceOfservice, VD.ProcCode, VD.Modifier1, VD.Modifier2, VD.Modifier3, PC.Diag1, PC.Diag2, PC.Diag3, PC.Diag4, VD.Charge, VD.AmountPaid,VD.Units, VD.SingleBalance, ##T.TDiag1, ##T.TDiag2, ##T.TDiag3, ##T.TDiag4, ##T.TDesc1, ##T.TDesc2, ##T.TDesc3, ##T.TDesc4 FROM (SELECT Insname ASI2Name, Expiredate, PatID, Inscocode, PHName AS I2PhName, PHDOB AS I2PhDob, (CASE WHEN PHsex = 'F' THEN 'X' END) AS I2Female, (CASE WHEN PHsex = 'M' THEN 'X' END) AS I2Male, PHEmployer AS I2PhEmp, GroupNo AS I2GroupNo,PrimaryOrSec FROM InsuranceTabRS)I2 RIGHT JOIN(PatientIns PtI RIGHT JOIN(RelatedPatInfo PA RIGHT JOIN(ReferringPhysicians RP RIGHT JOIN((Patients P INNER JOIN((Inscompanies I RIGHT JOIN((Doctors D INNER JOIN((InsAndCPT CRIGHT JOIN ((POSandIns S RIGHT JOIN(vwPrintCodes PC INNER JOIN (Vouchersdetail VD INNER JOIN(Vouchers V INNER JOIN ##T ON V.VoucherNo = ##T.Voucher AND V.HoldClaim = 0 AND V.Printed = 0)ON V.VoucherNo = VD.Voucher AND VD.ServiceDate BETWEEN @Start AND @End) ON PC.Voucher = VD.Voucher AND PC.LineNumber = VD.LineNumber) ON VD.PlaceOfService = S.Code AND V.Insco = S.InsCode))ON C.Code = VD.ProcCode AND C.InsCode = V.Insco)) ON D.Code = V.DrCode)) ON I.Code = V.Insco)) ON P.ID = V.PatID)) ON RP.Code = P.ReferralID) ON PA.ID = P.ID) ON PtI.PatID = P.ID AND PtI.InsCoCode = V.Insco AND (PtI.PrimaryOrSec = @Primary OR PtI.PrimaryOrSec = @Secondary)) ON I2.PatID = P.ID AND I2.InsCoCode <> V.Insco AND I2.ExpireDate > VD.ServiceDate AND I2.PrimaryOrSec = (CASE WHEN PtI.PrimaryOrSec = 'P' THEN 'S' ELSE 'P' END) ORDER BY InsName, P.Account, GVoucherNo, VD.LineNumber Anyway, pay no attention to what it does. It's a real query, and it works, but extremely slow. The main focus are the bolded inner joins: The Vouchersdetail table currently has approx. 450,000 rows, the Vouchers table 45,000, and the ##T table only the necessary rows for this query, maybe up to several hundred. Indexes are all OK. Showplan is showing it starts off by expecting a rowcount of nearly 40,000 from the Vouchers table, so somewhere is a problem. So I'm wondering, how are joins evaluated, if not innermost first?Sarah Berger MCSD |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-06-24 : 23:08:20
|
| >> So I'm wondering, how are joins evaluated, if not innermost first? The server will execute the joins as it thinks best unless set the option to follow the join order (can't remember what it is force plan or something).Are your statistics up to date and do you have an index on the ##T table?You can get rid of the brackets on the inner joins - they don't do anything.It is probably having to scan the VD table (index scan?) due to the date range check which is causing it to do that join first.Otherwise try giving it optimisation hints or splitting the query up using temp tables.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-24 : 23:13:54
|
OK, I imagine, based on the abundance of parentheses, that this query originated in Access and was copied over to SQL Server. The parentheses are most likely what's killing this from being better optimized, because it will prioritize each clause that's in parentheses over those that are not and never give anything an even break. If you're a heavy Access query writer and you're moving to SQL Server, the first habit you need to break is the excessive parentheses that Access generates, especially that nested crap it uses in the JOIN clauses; it's death.Seriously, paste this into Word or Notepad and do a search and replace on parentheses and get rid of all of them. The only place you need them is the RTRIM function, the (V.VoucherNo + ((VD.LineNumber - 1) / 6)) AS GVoucherNo expression, and around the SELECT subquery for I2. All other parentheses in this query are superfluous.Once you get that cleaned up and fix any minor syntax errors, check the plan on it. I wouldn't be surprised if it works better. If not, you can try using some index hints on the tables, but first you should move these (in red):vwPrintCodes PC INNER JOIN (Vouchersdetail VD INNER JOIN(Vouchers V INNER JOIN ##T ON V.VoucherNo = ##T.Voucher AND V.HoldClaim = 0 AND V.Printed = 0) ON V.VoucherNo = VD.Voucher AND VD.ServiceDate BETWEEN @Start AND @End) ON PC.Voucher = VD.Voucher AND PC.LineNumber = VD.LineNumber...out of the JOIN clause and into the WHERE clause, because only one side of the join expression refers to a table and none of them can utilize an index since they over-complicate the join expression. It might also help to create index(es) on the joined columns in the ##T table.HTH Yeah, do what Nigel said!  Edited by - robvolk on 06/24/2002 23:14:41 |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-24 : 23:20:58
|
| The hint is FORCE ORDER, but it does the opposite: forces join order in the order the tables appear in the SELECT. I tried it, and got a different plan, with Vouchersdetail being evaluated first, but with a very high estimated row count (350,000+) when the query's actual output will be 7(!) rows. When using force order, I guess one must be very careful with the order of the table names.Statistics are up to date.##T is a temp. table. Query Optimizer has specified indexing it will not affect performance. Besides, as aforementioned, it's a small table.I use brackets for organization purposes. For that, they help. Otherwise, I'd drown in JOINs.Oddly, it is scanning the table on the date range. But the column is indexed.Sarah Berger MCSD |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-24 : 23:37:53
|
| Sarah,Maybe create a few views to help readability?DavidM"SQL-3 is an abomination.." |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-06-24 : 23:56:13
|
| try using indentation instead of brackets to formatfromtbl1-----inner join tbl2------------on tbl1.fld = tbl2.fld------------and tbl1.fld = tbl2.fld-----left outer join ------------(tbl3 -------------------inner join tbl4--------------------------on tbl3.fld = tbl4.fld--------------------------and tbl3.fld = tbl4.fld------------)------------on tbl1.fld = tbl3.fld==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-25 : 03:58:06
|
| 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 FROM InsuranceTabRS) I2RIGHT JOIN PatientIns PtIRIGHT JOIN RelatedPatInfo PA RIGHT JOIN ReferringPhysicians RP RIGHT JOIN Patients P INNER JOIN Inscompanies I RIGHT JOIN Doctors D INNER JOIN InsAndCPT C RIGHT JOIN POSandIns S RIGHT JOIN vwPrintCodes PC INNER JOIN Vouchersdetail VD INNER JOIN Vouchers V INNER JOIN ##T ON V.VoucherNo = ##T.Voucher ON V.VoucherNo = VD.Voucher ON PC.Voucher = VD.Voucher AND PC.LineNumber = VD.LineNumber ON VD.PlaceOfService = S.Code AND V.Insco = S.InsCode ON C.Code = VD.ProcCode AND C.InsCode = V.Insco ON D.Code = V.DrCode ON I.Code = V.Insco ON P.[ID] = V.PatID ON RP.Code = P.ReferralID ON PA.[ID] = P.[ID] ON PtI.PatID = P.[ID] AND PtI.InsCoCode = V.Insco ON I2.PatID = P.[ID]WHERE V.HoldClaim = 0 AND V.Printed = 0 AND VD.ServiceDate BETWEEN @Start AND @End AND (PtI.PrimaryOrSec = @Primary OR PtI.PrimaryOrSec = @Secondary) AND I2.InsCoCode <> V.Insco AND I2.[ExpireDate] > VD.ServiceDate AND I2.PrimaryOrSec = (CASE WHEN PtI.PrimaryOrSec = 'P' THEN 'S' ELSE 'P' END) ORDER BY InsName, P.Account, GVoucherNo, VD.LineNumber This is what your from clause looks like, with the part that should be in a where clause put there...try this in the query plan, instead of what you have..PeaceRick |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-25 : 10:35:29
|
| Hiya!Thanks, everyone. Special thanks, Rick, for laying out the TSQL for me to paste. The query time is cut in half, approx. 25 seconds. The first step in the plan is a clustered index scan on Vouchers which accounts for 41% of the query cost because it estimates approx. 40,000 rows. The next step is a filter, cost 2%, which is correctly estimating only 1 row. Is there a way to speed up the first step, ie the query optimizer should have a better conception of how many rows it actually needs?Sarah Berger MCSD |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-25 : 10:55:07
|
| Try using the PINTABLE option of DBCC, I remember it worked well for me with cacheing small tables, but be sure to unpin them when you are finished with your query. Run some stats on it again and you may be pleasantly surprised...I do subscribe to the recommendation that your query is overly large and you may benefit from several optimised views particularly if they can be 'black boxed' for use in other scenarios.My quids worth.Daniel Small MIAPwww.danielsmall.com<<monet makes money>> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-25 : 13:33:44
|
quote:
Try using the PINTABLE option of DBCC, I remember it worked well for me with cacheing small tables, but be sure to unpin them when you are finished with your query. Run some stats on it again and you may be pleasantly surprised...
Danny,DBCC PINTABLE doesn't quite work in that fashion. When you 'PIN' a table, you tell SQL Server not to release any pages from memory that it loads from that point forward from a given table. It doesn't cause SQL Server to immediately cache the table. So the procedure of running DBCC PINTABLE, then a query, then DBCC UNPINTABLE, is worthless.Jonathan Boott, MCDBA |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-25 : 13:45:17
|
quote:
Is there a way to speed up the first step, ie the query optimizer should have a better conception of how many rows it actually needs?Sarah Berger MCSD
Probably not, since the optimizer likely has no way of knowing what is in ##t. Out of curiousity, what's the reasoning for having a global temp table?Jonathan Boott, MCDBA |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-25 : 14:08:44
|
The temp. table is global because it is shared across 2 stored procedures which call each other. Procedure 1 creates it and fills it, and procedure 2 uses it and drops it. (If it will give a big performance boost I'll merge the two  )I followed some advice and merged 5 tables from the nasty SELECT into a view, and now the query is really fast; maybe 2-3 seconds.Sarah Berger MCSD |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-25 : 15:53:03
|
| I see. Well, a global temp table will certainly work there, though you don't need one - a temp table created in procedure A is visible in B if A calls B. Nor do you need to drop it explicitly; SQL Server will drop it automatically when procedure A completes. Sounds like you've got it wrapped up nicely though w/ a 2-3 second execution time.Jonathan Boott, MCDBA |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-06-25 : 16:09:11
|
| I tried to make it local: #T, but when Procedure B runs it gives the error "Invalid object name #T". I am only myself, the current user, and even though I ran Procedure A from Query Analyzer, when I type SELECT * FROM #T I get the same error message.Actually, I see now that Proc A doesn't call Proc B, they are called in succession. Reason being, not alway will Proc B be called after A.If it's a global temp. table, it must be dropped, if not it causes concurrency problems. E.G. User A creates it, and is still connected when User B tries to create it again. This will happen anyway if the users will try (nearly) simultaneously, but has a smaller chance of happening.Sarah Berger MCSD |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-26 : 05:43:59
|
| Sarah,Not a problem, it took about 5 minutes... PeaceRick |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-26 : 05:55:03
|
| Sorry about that, i'm my scenario i did have more than one query in an sp using a ref table three times and a prior PINTABLE option. Yes, in your situ, PINTABLE is of no consequence.Well spotted jonathan.Dan<<monet makes money>> |
|
|
|
|
|
|
|
|
Nasty SELECT