SA-9-Plan shows hash table being used, different r...

Former Member · ‎2012 Mar 02

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.

jeff_albion · ‎2012 Mar 07

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?

Breck_Carter · ‎2012 Mar 08

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')

Breck_Carter · ‎2012 Mar 05

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