JMSM avatar image
JMSM asked

I´ve got the following query that send me a timeout...

I've got the following query that send a Timeout, inside the *.execution plan xmla appears..."StatementOptmEarlyAbortReason="TimeOut"

Can help me to build this query to avoid timeouts.
Thanks and regards.

SELECT t1.[VndID] , t1.[VndID] AS VndCntID , t1.[OprtnddID] , t1.[TpOrdm] , t1.[NmOrdm] , t1.[Prrdd] , t1.[Estd] , t1.[SprvsrID] , t1.[DtCrc] , t1.[DtMdfcc] , t1.[CrdPor] , t1.[MdfidPor] , t1.[MrdID] , t1.[TpOrgmID] , t1.[MrdFctrcID] , t1.[Pmnt_Tp] , t1.[Pmnt_Dc_Tp] , t1.[NmrFctur] , t1.[NmrRcbmnt] , t1.[Pmnt_Type_Chr] , t1.[VndOcsnl] , t1.[SblID] , t1.[PrVnd] , t1.[SstdVndDscrc] , t1.[TpoOrdmVndDscrc] , t1.[CntCltID] , t1.[NmClt] , t1.[OprtnddVndDscrc] , t1.[CmpnhID] , t1.[CmpnhNm] , t1.[AgntID] , t1.[CmrclNm] , t1.[AgntNm] , t1.[CdgAgntPT] , t1.[CntNif] , t1.[CntBI] , t1.[CntPssprt] , t1.[prvndext] , t2.Andr , t2.Aprtmnt , t2.CdgLcldd , t2.CdgLclddPT , t2.CdgPstl , t2.CdgR , t2.DsgncMrd , t2.Edfc , t2.Lcldd , t2.LclddPstl , t2.Mrd , t2.MrdCmplt , t2.MrdCmplt2 , t2.NumPolicia , t2.Ps , t2.Rgo --INTO #temp1 FROM dbo.123_OrdrFrmScriptWrln AS t4 WITH(NOLOCK) INNER JOIN dbo.ItVnd AS t5 (NOLOCK) ON t5.ItmID = t4.Item_ItmID INNER JOIN PrdAssc AS t7 WITH(NOLOCK) ON t5.PrdID = t7.PrdCmpstID INNER JOIN dbo.ItVnd AS t6 (NOLOCK) ON t6.PrdID = t7.PrdCmpnntID INNER JOIN dbo.Prd AS t8. WITH(NOLOCK) ON t11PrdID = t7.PrdCmpnntID INNER JOIN dbo.VwVns AS vv WITH(NOLOCK) ON t11OprtnddID = t1.OprtnddID INNER JOIN 456_Ordr AS t9 WITH(NOLOCK) ON t4.Order_Id=t9.Id INNER JOIN VWMrd AS vm WITH(NOLOCK) ON t2.MrdID = t1.MrdID WHERE NOT EXISTS ( SELECT 1 FROM EstdVWlnWrlss AS t10 (NOLOCK) WHERE t10.EstdVWln = t6.EstdID AND t10.EstVW = t9.stid ) AND t4.del = 0 AND t9.del = 0; --option(reCmpile) --00:03:47 --sem reCmpile --00:02:21 00:01:44 --select * from #temp1 --00:01:45

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JMSM avatar image JMSM commented ·

I forgot to tell, updown are the different times of executions with some modifications done.

-sem reCmpile means Withou recompile

0 Likes 0 ·

1 Answer

Kev Riley avatar image
Kev Riley answered

This 'timeout' is the Query Optimizer telling you that it ran out of 'time' and didn't get to generate and evaluate every possible execution plan - it doesn't mean however that the plan you have is not good for the query. So how does the query perform? Is it within acceptable bounds?

The only way to avoid the "StatementOptmEarlyAbortReason="TimeOut" is to simplify your query - are you using views, nested views, functions etc, - these all add extra complexity.

Alternatively, simplify the optimizer's choices - I've seen this in cases where tables were over-indexed, and the optimizer simply doesn't have enough time to compare all possible access paths

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.