Hello,

Short version of questions: What factors would cause SA to create/use a hash table?

Long version: Using 9.0.2.3924. Large but mature query started performing poorly. Moved to another box, same version of SQL A, same DB, query ran well. Main hardware difference between boxes is CPU speed. Moved to a 3rd box (same CPU as first) and got same poor results.

Used ISQL on both boxes and discovered that on the slower CPU box a table the Plan shows a table that is coded as a LOJ as a Left Outer Hash Join and that the table used scanned sequentially. This table is joined by a FK. On the newer box, the Plan shows a normal LOJ being used.

Update: I simpled the SQL until I got to where the removal of any single join caused the query to run well. Removal of ANY ONE of the items in brackets [] below causes the SQL to drop its Hash Join and it executes instantly, rather than in 4-5 seconds. All joins are FK'd:

Select e.*
from [1]Company c,[2]System s,Events e Force Index(byDate)
left outer join ReseSeats rs on (rs.EventNo=e.Code)
left outer join ReseRates rr on (rr.Seq = rs.ReseRate)
left outer join ProdCatAssigns pca on (pca.Code = rr.ProdCatAssign)
[3]left outer join Categories cat on (cat.code=pca.Category)
[4]left outer join Reservations r on (r.Code=rs.Reservation)
[5]left outer join Tours t on (t.Code=e.Tour)
[6]left outer join Bases b on (b.Code=e.Base)
[7]left outer join Vessels v on (v.Code=e.Vessel)
Where (e.DateValue Between '2012-02-29' AND '2012-02-29')

Any assistance appreciated.

asked 02 Mar '12, 18:38

kmanuel's gravatar image

kmanuel
91228
accept rate: 0%

edited 05 Mar '12, 03:17

Volker%20Barth's gravatar image

Volker Barth
29.9k294446654

Just to understand:

Are the cross joins between Company, System and Events and the index hint on Events done deliberately?

(05 Mar '12, 03:21) Volker Barth
Replies hidden

I guess the next step will be to explain how to upload files with not enough reputation points...

(05 Mar '12, 09:18) Volker Barth
Comment Text Removed

...you mean, "vote up the question so the point is moot?" :)

(05 Mar '12, 09:35) Breck Carter

Not really - I just guess (and tried to point out indirectly) that this limit may be set too high for new users...

"Vote up" would help for the moment. However, in a way I feel that the votes a question (or answer) gets do mean something (i.e. show some kind of value), and therefore a random upvoting to help to step over a nasty limit may be not very reasonable in the long run. - To get the required 100 points, you would need to get as much votes as only a few questions here have today...


I'd think some rep points should be necessary, to prevent "spamming" or whatever - but if 2 or 3 folks would like to help with a plan, their upvotes should be sufficient, methinks. To wait for 7 folks to vote up will usually take some days, if at all.

@Graeme: Do you hear me?

(05 Mar '12, 09:54) Volker Barth

Are the cross joins between Company, System and Events and the index hint on Events done deliberately?

Yes, both Company and System only contain a single record. On occassion we have found SA not using the most optimized index (byEventDate) and found that forcing such works well. However, in this specific installation's case removing the explicit force index does not change the results; we believe because this DB's statistics are sound.

(05 Mar '12, 13:18) kmanuel
showing 3 of 6 show all flat view

Moved to another box, same version of SQL A, same DB, query ran well. Main hardware difference between boxes is CPU speed. Moved to a 3rd box (same CPU as first) and got same poor results.

The database server uses heuristics other than CPU availability to choose access plans (often memory availability, statistics, and current cache status). See: http://dcx.sybase.com/index.html#1201/en/dbusage/peropt.html


The expense between these plans is on the "ReseSeats" table - we're performing an index scan in the "fast" case (on index "EventNo" in 0.0032621s) and a table scan in the "slow" case (in 3.4093s).

There is a difference in how many pages we're estimating that we've already pulled into cache between these cases:

=== Slow ===
Estimated pages: 26040
Estimated pages in cache: 26040

=== Fast ===
Estimated pages: 23838
Estimated pages in cache: 220

You should ensure that you execute your performance testing with the same starting cache conditions, otherwise it would be expected that the optimizer makes different decisions at runtime. See the February 2012 edition of the SQL Anywhere Insider magazine for more details on ensuring starting cache conditions during performance tests: http://www.sybase.com/detail?id=1096331 (e.g. using sa_flush_cache() or otherwise in your test, where appropriate).


A final observation: this doesn't appear to be the same database file (the databases have different recorded sizes):

=== Slow ===
File: C:\home\Activitylink\alprocs\pwf\ALPro_PWF.db
FileSize: 494988

=== Fast ===
File: C:\home\ActivityLink\ALPro\PWF\server\ALPro_PWF.db
FileSize: 1549653

So the databases likely contain different index selectivity estimates for the "ReseSeats" table. The selectivity estimate for the EventNo index in the "fast" database is:

Selectivity:   0.00153% Index

Do you know what it is for the "slow" database?

If you re-run your test using the same database file at each site, do you still see the same results? What happens if you re-create statistics for the ReseSeats table on the 'slow' database?

(Finally, as a last resort:) What happens if you force the EventNo index on the ReseSeats table?

permanent link

answered 07 Mar '12, 12:47

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 08 Mar '12, 15:26

@Jeff: I was about to rant that I've never heard of the "The SQL Anywhere Insider" magazine in all these years - but then found my localized "SQL Anywhere Newsletter" for March (German edition) does contain a link to this...

Nevertheless, another example of the legendary "Stealth Marketing"?

(07 Mar '12, 13:04) Volker Barth
Replies hidden

Jeff, thank you for your responses. We will re-run the test and post results. No sure how the DB sizes got so different as we just copied from one server to another. Perhaps in the process of trying to figure this out we tried re-loading the one.

FYI, we already did try recreating statistics on all tables, including ReseSeats, and forcing the index on ReseSeats (previously posted) with no change in the slow query.

For my edification, are you saying that the larger cache size was a factor in Sybase deciding to do a table scan rather than an index scan?

As far as the "Selectivity", I'll have to figure out how you get/got that information. If you were able to see if from my plans on the "Fast" query, wouldn't you also be able to see it form the plans on the "Slow" query?

(07 Mar '12, 13:42) kmanuel
Replies hidden

Last night I snagged a fresh copy of the data, did a full unload and reload and then placed the exact same db on both of our testing servers. The graphical plans with statistics can be found at:

http://www.activitylink.net/downloads/SQLHelp/FastResults_03_08_12.xml http://www.activitylink.net/downloads/SQLHelp/SlowReulst_03_08_12.xml

Praying that someone can tell me how to resolve this issue.

TIA! Kumar

(08 Mar '12, 14:30) kmanuel
Replies hidden

A larger cache size can make a Table Scan more convenient in certain cases (specifically if the whole table is already in cache). Have you tried running sa_flush_cache() before executing this query on the slow box?

Also, are these test servers virtualized or stand-alone physical hardware?

(08 Mar '12, 19:00) Tyson Lewis

First, all regular stand alone, self-managed servers from a large hosting company, Softlayer.

Well, I'm getting some positive but odd results. On our Q&A slow server the sa_flush_cache seems to have resolved the issue. Don't understand how since I cycled the DB from even running each time before testing...so how could anything be in the cache? Anyway, I was so damn excited I did same on the production slow server and, well...no change there. I will try tonight after the client is done for the evening.

(08 Mar '12, 22:01) kmanuel

Oh, and thank you for your posting!

(08 Mar '12, 22:04) kmanuel

Just a hint: v9.0.1 introduced the "cache warming" feature:

AFAIK a freshly started database will load those pages into the cache that have been loaded during the "first timespan" of the previous database start (I don't know the exact timespan, cf. the doc page). So, when you're doing tests with a "freshly loaded" database, it could just mean you have a pre-loaded cache from your last run.

Therefore sa_flush_cache() is a must for this kind of tests, methinks.

(09 Mar '12, 04:31) Volker Barth

As part of the writing/review team for the articles, I'm pretty sure our marketing group would prefer them to be "less stealth". ;)

I will bring this feedback to the group responsible for putting together the content - thank you!

(09 Mar '12, 12:25) Jeff Albion
1

Well, another 3,567 people per month are hearing about it now! http://sqlanywhere.blogspot.com/2012/03/sql-anywhere-insider-magazine.html

(09 Mar '12, 14:06) Breck Carter

These results still show differing cache contents for the individual "ReseSeats" table from these plans, hence the observation of "sa_flush_cache()" making the tests equal. Furthermore, it looks like the server has already hit its maximum cache size in this test and can potentially start swapping to disk past this point - this means we have to start ejecting things from the cache if we would want to load additional pages from the database to satisfy queries:

(Slow)

 PeakCacheSize: 1046000
 MaxCacheSize: 1046000
 [RS] Estimated pages: 26216
 [RS] Estimated pages in cache: 26216

(Fast)

 PeakCacheSize: 1042812
 MaxCacheSize: 1042812
 [RS] Estimated pages: 26216
 [RS] Estimated pages in cache: 224

Because of this situation, we won't load try to load any additional index pages for the case where the all of the table pages are already in cache - we instead perform a table scan instead and use a different joining strategy (which results in more pages being read from 'RS', overall:)

(Slow)

 [RS] CacheHits: 1.6012e+006

(Fast)

 [RS] CacheHits: 1558


Can you post the updated query plan (as Breck requested in his new answer) if you try to force the EventNo index on the ReseSeats table? Can you now confirm the optimizer is using this index in the plan when looking at the ReseSeats node?

At a guess, it looks like you're running this query as part of a larger performance test (that ends up populating the cache prior to this query being run). What happens if you just execute this query (and only this query) once the database starts-up - do you still see performance differences?

(09 Mar '12, 14:31) Jeff Albion

Placed on our http site are plans for the following query run on both boxes after starting the DB fresh and executing the sa_flush_cache:

Select e.* from Company c,System s,Events e left outer join ReseSeats rs FORCE INDEX ( EventNo ) on (rs.EventNo=e.Code) left outer join ReseRates rr on (rr.Seq = rs.ReseRate) left outer join ProdCatAssigns pca on (pca.Code = rr.ProdCatAssign) left outer join Categories cat on (cat.code=pca.Category) left outer join Reservations r on (r.Code=rs.Reservation) left outer join Tours t on (t.Code=e.Tour) left outer join Bases b on (b.Code=e.Base) left outer join Vessels v on (v.Code=e.Vessel) Where (e.DateValue Between '2012-02-29' AND '2012-02-29')

http://www.activitylink.net/downloads/SQLHelp/SlowBox_03_10_12.xml http://www.activitylink.net/downloads/SQLHelp/FastBox_03_10_12.xml

On both boxes it ran above very fast the first time (for which I exported the plans above) however I don't know for sure what indexices/hash tables it did or did not use. This short query also continued to run fast on both boxes after being run multiple times.

Then, for a test, I did sa_flush_cache's again and ran our full query (including the force index on ReseSeats). It was slow on both boxes (though very slow on the "Slow" box). Here are the plans for this test:

http://www.activitylink.net/downloads/SQLHelp/SlowBox_FullQuery_03_10_12.xml http://www.activitylink.net/downloads/SQLHelp/FastBox_FullQuery_03_10_12.xml

So, I thought things were at least showing consistant results now that we have a more controlled envrironment. So, I decided to try running the full query multiple times on both boxes to see the results. On the "Slow" box, the Full Query remained slow no matter how many times I ran it; though it did get better than the inital run. However, on the "Fast" box, the query only ran slow the first time after the cache flush. On subsequent runs it was fast. Here are the plans after several runs of the full query:

http://www.activitylink.net/downloads/SQLHelp/SlowBox_FullQuery_03_10_12_AfterSeveralRuns.xml http://www.activitylink.net/downloads/SQLHelp/FastBox_FullQuery_03_10_12_AfterSeveralRuns.xml

At a guess, it looks like you're running this query as part of a larger performance test No. Actually, this query is a cut-down version of a query that is run about every 10 seconds by our application to provide updated results for a key feature of the application: Event Availability.

The reseseats table is very large and I'm guessing that SA is not realizing that scanning it sequencially from cache is not faster than applying the index. Also, I don't understand why, even if it was all in cache, wouldn't it still apply the index? Anyway, just looking for someway to resolve this issue. Do appreciate all the time and help!

TIA!

(10 Mar '12, 16:14) kmanuel

Soooo... the plan count has now reached thirteen :)

It's time to start an entirely new question... stuff is getting lost in this question, including a "Thanks for starting over" reply posted two days ago.

Please understand that everyone here is a volunteer; you may need some dedicated help.

(10 Mar '12, 17:58) Breck Carter

I think I understand. More than happy to pay for some one-on-one/dedicated assistance if it is available. Can anyone direct me to same? And yes, thanks to all for your help thus far.

(10 Mar '12, 18:24) kmanuel

Also, sorry, I did incorporate your suggestion under "Thanks for starting over" within the lastests plans (e.g. show us the play for this query...which uses the ReseSeats force index). I just didn't know how to respond to the question in the correct place. I have figured that out now.

(10 Mar '12, 18:28) kmanuel

We have now performed an update to the website so that customers can better locate these documents now.

See: http://www.sybase.com/detail_list?id=9810

They can also now be searched via http://search.sybase.com/search/advanced.do as "Technical Content" and can be found underneath the sub-category "Troubleshooting".

We're still working on making these articles more visible on the SQL Anywhere product page.

(16 Apr '12, 16:22) Jeff Albion
showing 3 of 15 show all flat view

Please post the graphical plans (with actual and estimated statistics) for the various permutations that you've tried.


Here they are, delivered right to your doorstep... :)

FullQueryGraphicalPlan_FastComputer.xml

FullQueryGraphicalPlan_SlowComputer.xml

ShortenedQueryGraphicalPlan_FastComputer.xml

ShortenQueryGraphicalPlan_SlowComputer_NoForceIndex.xml

ShortenQueryGraphicalPlan_SlowComputer.xml

permanent link

answered 05 Mar '12, 07:55

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

edited 05 Mar '12, 16:45

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836

I prepared the graphical plans but it will not allow me to upload as I have less than 100 reputation points. I assume you want me to just try and post them as xml in text format...will attempt.

(05 Mar '12, 13:09) kmanuel

The graphical plan xml is to large and will not allow me to put in text format. Please advise.

(05 Mar '12, 13:11) kmanuel

I have posted the XLM as well as the full query (shortened version in orig. posting) at:

http://www.activitylink.net/downloads/SQLHelp/

Again, TIA

(05 Mar '12, 13:13) kmanuel

Oh, btw, both the Company and System table only contain one record. We could also just join them out like this:

left outer join company on (1=1) left outer join system on (1=1)

(05 Mar '12, 13:16) kmanuel

Just to be ahead of the question: Yes, I tried doing a create statistics on ALL tables, no change. And, again, the only difference we know of between the fast and the slow query is the box it is being run on. Both are hosted servers from Softlayer and of decent quality. One just has a higher CPU speed.

(06 Mar '12, 02:40) kmanuel
Replies hidden

Try to calibrate database or restore default calibration and get us to know if result change.

ALTER DATABASE {

CALIBRATE [ SERVER ]

| CALIBRATE DBSPACE dbspace-name

| CALIBRATE DBSPACE TEMPORARY

| CALIBRATE PARALLEL READ

| RESTORE DEFAULT CALIBRATION

}

(06 Mar '12, 04:06) AlexeyK77

On the fast plans NumProcessorsMax is 1, whereas on the slow plans NumProcessorsMax is 0.

I doubt it will help, but... perhaps running with dbsrv9 -gt 1 will help.

(06 Mar '12, 09:46) Breck Carter

Tried all of above, no change. Should I expect someone from Sybase to figure this out, or just give up and try moving to Sybase 11 or 12?

(06 Mar '12, 16:41) kmanuel

Don't give up! First, try to change optimisation goal parameter for this query to FIRST ROW from All rows as it set now. Second, play with optimisation level parameter, now you have set default value 9. Waiting for results.

(07 Mar '12, 05:42) AlexeyK77

"Should I expect someone from Sybase to figure this out"... only if you call tech support. Access to the schema, the data and the hardware is probably required.

(07 Mar '12, 11:32) Breck Carter

WOW, how messed up is this?

I set the maximum cache to 250m (-ch 250m) and the query flies. Tried again without setting a maximum cache (it indicates this Minimum cache size: 2048K, maximum cache size: 1046000K) and back down to a crawl. So, by allowing too much cache I degrade the DB's performance???

There must be something else we can do to fix the DB up without throttling the cache it would seem...

Even explictly forcing the index on reseseats did not cause SA-9 to use the index.

(07 Mar '12, 15:38) kmanuel

db server don't choose Hash join algorithm then there are low memory resources for server. This optimizer behavior is documented.

(07 Mar '12, 19:15) AlexeyK77

Thank you for your response. I am, however, not quite following. Are you saying that it is expected behavior for SA-9 to not use an index when there is abundant memory to use a has table? If so, is there any way to "instruct" the query not to do so?

Or, are you saying that there are likely low memory resources on the server that had the slow results? Sorry, just a bit lost here.

TIA!

(08 Mar '12, 00:09) kmanuel

read this topic: HashJoin algorithms (JH, JHSP, JHFO, JHAP, JHO, JHPO) http://dcx.sybase.com/index.html#1200/en/dbusage/hash-optimizer-queryopt.html*d5e30984


if HashJoin executes in an environment where there is not enough cache memory to hold all the rows that have a particular value of the join attributes, then it is not able to complete. In this case, HashJoin discards the interim results and an indexed-based NestedLoopsJoin is used instead. All the rows of the smaller table are read and used to probe the work table to find matches. This indexed-based strategy is significantly slower than other join methods, and the optimizer avoids generating access plans using a hash join if it detects that a low memory situation may occur during query execution.

(08 Mar '12, 03:41) AlexeyK77
More comments hidden
showing 5 of 14 show all flat view

The SELECTs in FullQueryGraphicalPlan_FastComputer.xml and FullQueryGraphicalPlan_SlowComputer.xml are identical but the plans look vastly different.

Here are the other plans...

ShortenedQueryGraphicalPlan_FastComputer.xml

ShortenQueryGraphicalPlan_SlowComputer_NoForceIndex.xml

ShortenQueryGraphicalPlan_SlowComputer.xml

What happens when you remove all the "Force Index" clauses?

What happens when you change...

left outer join ReseSeats rs on (rs.EventNo=e.Code)

to add a Force Index to use the index that the fast query uses on that table...

left outer join ReseSeats rs Force Index(EventNo) on (rs.EventNo=e.Code)

This is an amazingly complex query... perhaps it can be simplified:

Select e.DateValue, e.Code, e.TimeValue, e.Tour, e.Vessel, e.Operator, e.OpenVesselNo
,t.ShortName as TourShortName, t.Name as TourName
,IsNull(null,0) as TourSeqOrder
,if o.ShortName is not null then o.ShortName else o.Name endif as PilotShortName
,t.IgnoreOverLaps
,o2.ShortName as CoPilotShortName
,Cast(Null as Integer) as RTSeats
,Cast(Null as DateTime) as ReleaseOn
,Cast(Null as DateTime) as NoBookOn
,Cast(Null as Integer) as RTHolds
,if t.UsesTables=1 then IsNull(ALTables.Capacity,0) else
if e.UseVesselCapacity=0 then e.Capacity else if t.UseVesselCapacity=0 then t.Capacity else if e.Vessel=-1 then 6 else v.Capacity endif endif endif endif as Capacity
,if e.UseVesselCapacity=0 then e.Threshold else if (e.UseVesselCapacity=1 and s.UseTourThreshold<>1) then v.Threshold else if s.UseTourThreshold=1 and t.UseVesselCapacity<>1 then t.Threshold else v.Threshold endif endif endif as Threshold
,t.DisplayColor, e.LastUpdate
,(if e.Operator is Null then v.DefaultPilotWeight else o.Weight endif) as OperatorWeight
,(if v.UsesCoPilot=0 then 0 else if e.Operator2 is Null then v.DefaultPilotWeight else o2.Weight endif endif) as OperatorWeight2
,0.00 as AftBaggageWeight
,v.ReserveMinutes as ReserveMinutes
,if s.UseDurationMPHAdjust=1 and b.StandardMPH is not null and b.StandardMPH>0 and v.UseDurationAdjust=1 and v.MilesPerHour is not null and v.MilesPerHour>0 then b.StandardMPH else 0 endif  as BaseMPH
,if s.UseDurationMPHAdjust=1 and b.StandardMPH is not null and b.StandardMPH>0 and v.UseDurationAdjust=1 and v.MilesPerHour is not null and v.MilesPerHour>0 then if t.UseDurationMPHAdjust=0
then IsNull(t.StandardMPH,0) else IsNull(v.MilesperHour,0) endif else 0 endif as EventMPH
,if BaseMPH=0 or BaseMPH is null or EventMPH=0 or EventMPH is Null then 1.00 else (IsNull(BaseMPH,0)/IsNull(EventMPH,0)) endif as SpeedDelta
,if s.useheadtailwindmphadj=1 and e.HeadTailWindMPH is not null and e.HeadTailWindMPH<>0 and EventMPH<>0 and EventMPH is not null and (IsNull(EventMPH,0)-IsNull(e.HeadTailWindMPH,0))<>0 then 1/((IsNull(EventMPH,0)-IsNull(e.HeadTailWindMPH,0))*1.00/EventMPH) else 1.00 endif as WindDelta
,(if e.UseTourDefaults=1 then Round(IsNull(t.WBDefaTachMinutes,0)*IsNull(SpeedDelta,0),0) else IsNull(e.WBDefaTachMinutes,0) endif) as WBTachMinutes
, (if e.ActualTach is not null then Round(IsNull(e.ActualTach,0)*60,0) else if (t.TourLands=1) or (e.ActualDeparture is Null) or (e.ActualReturn is Null) or (e.ActualReturn<e.actualdeparture) then="" if="" e.usetourdefaults="0" then="" isnull(e.tachminutes,0)="" else="" cast(round(isnull(t.tachminutes,0)*isnull(speeddelta,0)*isnull(winddelta,0),0)="" as="" integer)="" endif="" else="" cast(datediff(minute,="" e.actualdeparture,cast(dateadd(hour,isnull(e.timedelta*-1,0),e.actualreturn)="" as="" time))="" as="" integer)="" endif="" endif)="" as="" tachminutes="" ,v.fuelgalperhour,v.fuellbspergallon="" ,if="" (v.code<="">-1) then v.MaxGrossWeight else t.DefaultWeightAvail endif as MaxGrossWeight
,Cast(0 as Integer) as RefuelMinutes
,(if e.UseConfig is Null then if v.Code=-1 then Cast(0 as Integer) else if v.CurrentConfig is null then v.ConfigGrossWeight else vac.ConfigGrossWeight endif endif else eac.ConfigGrossWeight endif) as ConfigGrossWeight
,(if e.UseConfig is Null then if v.Code=-1 then Cast(null as Char(30)) else if v.CurrentConfig is null then Null else vac.ShortName endif endif else eac.ShortName endif) as ConfigurationName
,IsNull(blk.Quantity,0)+IsNull(adj.Quantity,0) as BlockQuantity
,Sum(if (rs.Seq is null or rs.Seq=0 or r.Active=0) then 0 else 1 endif)+IsNull(BlockQuantity,0) as Seats
,Sum(if (rs.CheckedIn Is Null or rs.CheckedIn=1 or r.Active=0) then 0 else 1 endif)+IsNull(BlockQuantity,0) as NotCheckedIn
,Sum(if (rs.Weight is not null and r.Active=1) then (rs.Weight*IsNull(wt.PoundMultiplier,0))+if rs.scaled=0 then IsNull(0,0) else 0 endif else if cat.UsesDefaultWeight=1 then IsNull(cat.DefaultWeight,0) else 0 endif endif) as PaxWeight
,Cast('' as Char(1)) as Status
,(if e.UseTourDefaults=1 then Cast(Round(IsNull(t.DurationMinutes,0)*IsNull(SpeedDelta,0)*IsNull(WindDelta,0),0) as Integer) else IsNull(e.DurationMinutes,0) endif) as DurationMinutes
,e.ActualDeparture,e.ActualReturn,e.ServerDepartTime
,e.ReadyForDispatch
,wbd.WeightData,wbd.DefaultWeights
,e.DisableLateEventWarning
,IsNull(e.TimeDelta,0) as TimeDelta
,Cast(DateAdd(Hour,IsNull(IsNull(e.Zulu,0)*-1,0),e.DateValue+e.TimeValue) as DateTime) as ZuluDateTime
from Company c,System s,Events e Force Index(byDate)
left outer join ReseSeats rs on (rs.EventNo=e.Code)
left outer join ReseRates rr on (rr.Seq = rs.ReseRate)
left outer join ProdCatAssigns pca on (pca.Code = rr.ProdCatAssign)
left outer join Categories cat on (cat.code=pca.Category)
left outer join Reservations r on ((r.Code=rs.Reservation) and (r.Active=1) and (r.NoShow=0) and (r.WaitList=0) and (r.GiftCert=0))
left outer join Tours t on (t.Code=e.Tour)
left outer join Bases b on (b.Code=e.Base)
left outer join Vessels v on (v.Code=e.Vessel)
left outer join VesselConfigs vac on (vac.Code=v.CurrentConfig)
left outer join VesselConfigs eac on (eac.Code=e.UseConfig and eac.Vessel=e.Vessel)
left outer join WeightTypes wt on (wt.Code = rs.WeightType)
left outer join Operators o on (o.Code = e.Operator)
left outer join Operators o2 on (o2.Code = e.Operator2)
left outer join EventSpcInd esi on (esi.code=e.EventSpcInd)
left outer join
(select e.code,sum(if altadj.seq is not null and altadj.SeatNo is null and altadj.newcapacity is not null then IsNull(altadj.newcapacity,0) else IsNull(tt.capacity,0) endif) as Capacity
from Events e Force Index(byDate)
left outer join ALTables alt on (alt.TableLayOut=e.TableLayOut)
left outer join ALTableAdj altadj on (altadj.EventNo=e.code and altadj.TableLayout=alt.TableLayOut and altadj.altable=alt.code)
left outer join TableTypes tt on (tt.Code=alt.TableType)
Where e.Active=1
and e.TableLayout=alt.TableLayout and tt.Seat=1
and (e.DateValue Between '2012-02-29' AND '2012-02-29')
and e.Base=1
and ((altadj.seq is not null and altadj.newactive=1) or (alt.active=1 and altadj.seq is null))
and altadj.SeatNo is null
group by e.code)
as ALTables (EventNo,Capacity) on (ALTables.EventNo=e.Code)
left outer join 
 (  SELECT ee.Code as EventNo,Sum(IsNull(WBData.Weight,0)) as WeightData,Sum(IsNull(WBWPs.DefaultWeight,0)) as DefaultWeights, WBWPS.Vessel
     From Events ee Force Index(byDate)
     Left outer join Vessels v1 on (v1.code=ee.Vessel)
              Left Outer Join WBWPs on (WBWPs.Vessel=v1.Code)
              Left outer join WBData on (WBData.EventNo=ee.Code) and (WBData.WeightPoint=WBWPs.Code)
             Where ee.Active=1 and (ee.DateValue BETWEEN '2012-02-29' AND '2012-02-29') and ee.Base=1
    GROUP BY EventNo, WBWPs.Vessel)
    AS wbd (EventNo,WeightData, DefaultWeights, WBWPsVessel)
    ON (wbd.EventNo=e.code and WBWPsVessel=e.Vessel)
 left outer join 
(    Select e1.Code as BlkEvent, sum(if sbr1.EventNo is null then IsNull(blk.Quantity,0) else 0 endif) as Quantity
     from Events e1 Force Index(byDate)
     left outer join Blocks blk on (blk.Tour=e1.Tour and blk.Active=1 and (blk.BlockTime Is Null or blk.BlockTime=e1.TimeValue) and (blk.Vessel Is Null or blk.Vessel=e1.Vessel)
     and (blk.StartDate is Null or blk.StartDate<=e1.Datevalue)
     and (blk.EndDate is Null or blk.EndDate>=e1.Datevalue)
       and ((blk.Sun=1 and DOW(e1.DateValue)=1) or (blk.Mon=1 and DOW(e1.DateValue)=2) or (blk.Tue=1 and DOW(e1.DateValue)=3)
       or (blk.Wed=1 and DOW(e1.DateValue)=4) or (blk.Thu=1 and DOW(e1.DateValue)=5) or (blk.Fri=1 and DOW(e1.DateValue)=6) or (blk.Sat=1 and DOW(e1.DateValue)=7)))
     left outer join SeatBlockReleases sbr1 on (sbr1.EventNo=e1.Code and sbr1.Block=blk.Code)
     where (e1.DateValue Between '2012-02-29' AND '2012-02-29')
     and e1.Base=1
     and (blk.Active=1)
     and e1.Active=1
     Group by e1.Code
)
AS blk (blkEvent, Quantity)
On (blk.BlkEvent=e.Code)
 left outer join 
(    Select e2.Code as AdjEventNo, e2.Tour,sum(if sbr2.EventNo is not null or adj.Quantity is null then 0 else Adj.Quantity endif) as Quantity
     from Events e2 Force Index(byDate)
     left outer join BlockAdj Adj on (adj.EventNo=e2.Code  and adj.Tour=e2.Tour)
     left outer join Blocks blk2 on (blk2.code=adj.block and blk2.Tour=e2.Tour and blk2.Active=1 and (blk2.BlockTime Is Null or blk2.BlockTime=e2.TimeValue) and (blk2.Vessel Is Null or blk2.Vessel=e2.Vessel)
     and (blk2.StartDate is Null or blk2.StartDate<=e2.DateValue)
     and (blk2.EndDate is Null or blk2.EndDate>=e2.DateValue)
       and ((blk2.Sun=1 and DOW(e2.DateValue)=1) or (blk2.Mon=1 and DOW(e2.DateValue)=2) or (blk2.Tue=1 and DOW(e2.DateValue)=3)
       or (blk2.Wed=1 and DOW(e2.DateValue)=4) or (blk2.Thu=1 and DOW(e2.DateValue)=5) or (blk2.Fri=1 and DOW(e2.DateValue)=6) or (blk2.Sat=1 and DOW(e2.DateValue)=7)))
     left outer join SeatBlockReleases sbr2 on (sbr2.EventNo=e2.Code and sbr2.Block=blk2.Code)
     where (e2.DateValue Between '2012-02-29' AND '2012-02-29')
     and e2.Base=1
     and (blk2.Active=1 and blk2.code is not null)
     and e2.Active=1
     Group by e2.Code, e2.Tour
)
AS Adj (AdjEventNo,AdjTour,Quantity)
On (AdjEventNo=e.Code and AdjTour=e.Tour)
Where e.Active=1
and (e.DateValue Between '2012-02-29' AND '2012-02-29')
and e.Base=1
Group By e.DateValue,e.Code, e.TimeValue, e.Tour, e.Vessel, e.Operator, e.OpenVesselNo
,Capacity, Threshold, t.DisplayColor, e.LastUpdate
,t.ShortName, t.Name,OperatorWeight,OperatorWeight2
,TourSeqOrder
,v.ReserveMinutes,TachMinutes,WBTachMinutes
,v.FuelGalPerHour,v.FuelLbsPerGallon
,MaxGrossWeight
,ConfigGrossWeight
,DurationMinutes,e.ActualDeparture,e.ActualReturn,e.ServerDepartTime
,e.ReadyForDispatch,PilotShortName,t.IgnoreOverLaps,WeightData,DefaultWeights,ConfigurationName
,BlockQuantity, e.DisableLateEventWarning,RefuelMinutes
,Indicator,IndicatorColor
,o2.ShortName,e.TimeDelta,ZuluDateTime,BaseMPH,EventMPH,SpeedDelta,WindDelta
permanent link

answered 05 Mar '12, 15:40

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

edited 05 Mar '12, 16:43

What happens when you change...left outer join ReseSeats rs on (rs.EventNo=e.Code) to add a Force Index to use the index that the fast query uses on that table...left outer join ReseSeats rs Force Index(EventNo) on (rs.EventNo=e.Code)

rs.EventNo is FK to Events and I don't know how to force it to use a FK. I did, however, try adding an index to rs.EventNo and forcing its usage. Results were the same.

This is an amazingly complex query... perhaps it can be simplified: True, but we have been using it for years without problems. That aside, I also provided a simplifed queries that still cause the same issue.

(05 Mar '12, 15:57) kmanuel
Replies hidden

The SELECTs in FullQueryGraphicalPlan_FastComputer.xml and FullQueryGraphicalPlan_SlowComputer.xml are identical but the plans look vastly different.

Yep, and we have no idea why. Try out the smaller, shortened versions!

What happens when you remove all the "Force Index" clauses? I have put up the shortened Graphical Plan with the Force Index removed on the http site.

(05 Mar '12, 16:06) kmanuel

Is that a hand-coded query? I'm really impressed!

(05 Mar '12, 16:11) Volker Barth

Thanks, I think ;-)

Not sure what you mean by "hand-coded"...this query just started as a pretty simple one, getting events for a date range. As we added features over the years, we needed additional fields pulled in...so we kept adding more LOJ's.

But, as the shortened examples show, the SA issue isn't realted to it being a complex query ;-)

(05 Mar '12, 16:20) kmanuel

FWIW, when creating a foreign key, an appropriate index is created automatically. You may use that index (as shown in SYSINDEX or SYSINDEXES) as a hint - though it won't probably have a different effect than the one you have tried...

(05 Mar '12, 16:23) Volker Barth
1

WOW, how messed up is this?

I set the maximum cache to 250m (-ch 250m) and the query flies. Tried again without setting a maximum cache (it indicates this Minimum cache size: 2048K, maximum cache size: 1046000K) and back down to a crawl. So, by allowing too much cache I degrade the DB's performance???

There must be something else we can do to fix the DB up without throttling the cache it would seem...

(07 Mar '12, 15:32) kmanuel
showing 5 of 6 show all flat view

Thanks for starting over, with a new query and new plans...

FastResults_03_08_12.saplan

SlowReulst_03_08_12.saplan

One big difference is the fast plan shows this for table rs

Index Scan 
   Scan rs using index EventNo

and the slow plan shows this

Table Scan 
   Scan rs sequentially

So... please show us the plan for this query (I know this has been asked 
before, but the plan might help)...

Select e.*
from Company c,System s,Events e
left outer join ReseSeats rs FORCE INDEX ( EventNo ) on (rs.EventNo=e.Code)
left outer join ReseRates rr on (rr.Seq = rs.ReseRate)
left outer join ProdCatAssigns pca on (pca.Code = rr.ProdCatAssign)
left outer join Categories cat on (cat.code=pca.Category)
left outer join Reservations r on (r.Code=rs.Reservation)
left outer join Tours t on (t.Code=e.Tour)
left outer join Bases b on (b.Code=e.Base)
left outer join Vessels v on (v.Code=e.Vessel)
Where (e.DateValue Between '2012-02-29' AND '2012-02-29')
permanent link

answered 08 Mar '12, 17:03

Breck%20Carter's gravatar image

Breck Carter
27.3k424585836
accept rate: 21%

edited 08 Mar '12, 17:04

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×241
×97
×21
×5

question asked: 02 Mar '12, 18:38

question was seen: 1,925 times

last updated: 16 Apr '12, 16:22