March 24, 2010 at 3:53 pm
I have a query that does a number of left outer joins. The query returns only a single row and is fairly quick running between .5 and 2 seconds. When I insert the results into a table vairable the query goes off into la-la land and never comes back. It seems like the query doesn't even get to a point where it runs because I can put a RETURN statement immediately after declaring the table vairable, and it never gets executed. Any ideas on why this could be happening?
DECLARE @t_LiftCharge TABLE(t_LiftSegment_pk INT, LeadingChassis VARCHAR(25), LeadingContainer VARCHAR(25),Chassis VARCHAR(25), Container VARCHAR(25), TrailingChassis VARCHAR(25), TrailingContainer VARCHAR(25), HasLiftOn BIT, HasLiftOff BIT, ExpectedLiftOn BIT, ExpectedLiftOff BIT,IsOrigPortOrRamp BIT, IsDestPortOrRamp BIT,t_LeadingSegment_pk INT, t_TrailingSegment_pk INT,t_LiftOnChargeGroup_pk INT, t_LiftOffChargeGroup_pk INT)
--RETURN --Even with this return uncommented, if the INSERT on the next line is in there, this code takes forever to run.
INSERT @t_LiftCharge --Remove this line and the results are back within 2 seconds
SELECT
liftSegment.t_Segment_pk
,leadingSegmentChassisE.EquipmentID
,leadingSegmentContainerE.EquipmentID
,liftSegmentChassisE.EquipmentID
,liftSegmentContainerE.EquipmentID
,trailingSegmentChassisE.EquipmentID
,trailingSegmentContainerE.EquipmentID
,CASE WHEN liftOnCharge.t_Charge_pk IS NULL THEN 0 ELSE 1 END
,CASE WHEN liftOffCharge.t_Charge_pk IS NULL THEN 0 ELSE 1 END
,0
,0
,CASE WHEN OriginFacility.FacilityFlag & 1 = 1 OR OriginFacility.FacilityFlag & 16 = 16 THEN 1 ELSE 0 END
,CASE WHEN DestFacility.FacilityFlag & 1 = 1 OR DestFacility.FacilityFlag & 16 = 16 THEN 1 ELSE 0 END
,leadingSegment.t_Segment_pk
,trailingSegment.t_Segment_pk
,liftOnCharge.t_ChargeGroup_pk
,liftOffCharge.t_ChargeGroup_pk
FROM
t_Segment liftSegment
JOIN t_SegmentStop liftSegmentStopDest
JOIN t_Facility DestFacility ON DestFacility.t_Facility_pk = liftSegmentStopDest.t_Facility_pk
ON liftSegmentStopDest.t_SegmentStop_pk = liftSegment.t_DestinationSegmentStop_pk
JOIN t_SegmentStop liftSegmentStopOrig
JOIN t_Facility OriginFacility ON OriginFacility.t_Facility_pk = liftSegmentStopOrig.t_Facility_pk
ON liftSegmentStopOrig.t_SegmentStop_pk = liftSegment.t_OriginSegmentStop_pk
LEFT OUTER JOIN
t_SegmentReservation liftSegmentChassis
JOIN t_Equipment liftSegmentChassisE ON liftSegmentChassisE.t_Equipment_pk = liftSegmentChassis.t_Equipment_pk
JOIN t_EquipmentType liftSegmentChassisET ON liftSegmentChassisET.t_EquipmentType_pk = liftSegmentChassisE.t_EquipmentType_pk AND liftSegmentChassisET.t_EquipmentCategory_pk = 2
ON liftSegment.t_Segment_pk = liftSegmentChassis.t_Segment_pk
LEFT OUTER JOIN
t_SegmentReservation liftSegmentContainer
JOIN t_Equipment liftSegmentContainerE ON liftSegmentContainerE.t_Equipment_pk = liftSegmentContainer.t_Equipment_pk
JOIN t_EquipmentType liftSegmentContainerET ON liftSegmentContainerET.t_EquipmentType_pk = liftSegmentContainerE.t_EquipmentType_pk AND liftSegmentContainerET.t_EquipmentCategory_pk = 1
ON liftSegment.t_Segment_pk = liftSegmentContainer.t_Segment_pk
JOIN t_Segment leadingSegment ON leadingSegment.t_Segment_pk = 306065
LEFT OUTER JOIN
t_SegmentReservation leadingSegmentChassis
JOIN t_Equipment leadingSegmentChassisE ON leadingSegmentChassisE.t_Equipment_pk = leadingSegmentChassis.t_Equipment_pk
JOIN t_EquipmentType leadingSegmentChassisET ON leadingSegmentChassisET.t_EquipmentType_pk = leadingSegmentChassisE.t_EquipmentType_pk AND leadingSegmentChassisET.t_EquipmentCategory_pk = 2
ON leadingSegment.t_Segment_pk = leadingSegmentChassis.t_Segment_pk
LEFT OUTER JOIN
t_SegmentReservation leadingSegmentContainer
JOIN t_Equipment leadingSegmentContainerE ON leadingSegmentContainerE.t_Equipment_pk = leadingSegmentContainer.t_Equipment_pk
JOIN t_EquipmentType leadingSegmentContainerET ON leadingSegmentContainerET.t_EquipmentType_pk = leadingSegmentContainerE.t_EquipmentType_pk AND leadingSegmentContainerET.t_EquipmentCategory_pk = 1
ON leadingSegment.t_Segment_pk = leadingSegmentContainer.t_Segment_pk
JOIN t_Segment trailingSegment ON trailingSegment.t_Segment_pk = 307578
LEFT OUTER JOIN
t_SegmentReservation trailingSegmentChassis
JOIN t_Equipment trailingSegmentChassisE ON trailingSegmentChassisE.t_Equipment_pk = trailingSegmentChassis.t_Equipment_pk
JOIN t_EquipmentType trailingSegmentChassisET ON trailingSegmentChassisET.t_EquipmentType_pk = trailingSegmentChassisE.t_EquipmentType_pk AND trailingSegmentChassisET.t_EquipmentCategory_pk = 2
ON trailingSegment.t_Segment_pk = trailingSegmentChassis.t_Segment_pk
LEFT OUTER JOIN
t_SegmentReservation trailingSegmentContainer
JOIN t_Equipment trailingSegmentContainerE ON trailingSegmentContainerE.t_Equipment_pk = trailingSegmentContainer.t_Equipment_pk
JOIN t_EquipmentType trailingSegmentContainerET ON trailingSegmentContainerET.t_EquipmentType_pk = trailingSegmentContainerE.t_EquipmentType_pk AND trailingSegmentContainerET.t_EquipmentCategory_pk = 1
ON trailingSegment.t_Segment_pk = trailingSegmentContainer.t_Segment_pk
LEFT OUTER JOIN
t_Charge liftOnCharge
JOIN t_ChargeGroup liftOnChargeGroup ON liftOnChargeGroup.t_ChargeGroup_pk = liftOnCharge.t_ChargeGroup_pk AND liftOnChargeGroup.t_ChargeType_pk = 415
ON liftOnCharge.t_Segment_pk = liftSegment.t_Segment_pk
LEFT OUTER JOIN
t_Charge liftOffCharge
JOIN t_ChargeGroup liftOffChargeGroup ON liftOffChargeGroup.t_ChargeGroup_pk = liftOffCharge.t_ChargeGroup_pk AND liftOffChargeGroup.t_ChargeType_pk = 416
ON liftOffCharge.t_Segment_pk = liftSegment.t_Segment_pk
WHERE
liftSegment.t_Segment_pk = 306066
March 25, 2010 at 5:41 am
Can you publish the 2 execution plans?
March 25, 2010 at 5:55 am
Can you get an estimated plan, or does that appear to freeze as well?
It is possible that the plan is taking a long time to optimize and compile, or it is being blocked somehow.
Try generating an estimated execution plan, and then check to see what the process is waiting on.
Another alternative is that statistics are being updated and you do not have async stats enabled.
Another is that it is waiting on a large memory grant.
In fact, there are lots of possible explanations...
March 25, 2010 at 8:23 am
I can't get an execution plan for the call with the insert in there, nor can I get an estimated execution plan. We did find a work around by adding a Primary Key, Identity column to the table variable. I don't understand why that would make a difference but it seems to, any input would be appreciated.
March 25, 2010 at 8:26 am
Alternatively you can try working with a temporary table, instead of a table variable. You can further index the temp table, also statistics will be available.
March 25, 2010 at 8:27 am
See also if this helps
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx
March 25, 2010 at 8:41 am
stroebele (3/25/2010)
I can't get an execution plan for the call with the insert in there, nor can I get an estimated execution plan. We did find a work around by adding a Primary Key, Identity column to the table variable. I don't understand why that would make a difference but it seems to, any input would be appreciated.
Adding the primary key changed the conditions and produced a different plan.
My suggestion was to attempt to produce an estimated plan, and then check what waits were associated with the session.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply