November 27, 2008 at 1:04 am
Plan guides don't need changes to the source query. Basically, it's an object in SQL that tells the optimiser 'when you see a query of this form, apply these hints'
It's limited to hints that can be included in the option clause (recompile, maxdop, optimise for, ...) and requires that the query matches exactly. It has limited uses, but this is one of the places it was designed to help - queries that really need a hint but can't be changed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 3:45 am
My $0.02 input.
I think OP is one join from solving the unified field theory. The query is 19852 characters.
The query returns 290 columns and joins 62 tables. Under these cirumstances I think 7 seconds is quite good. Why is dynamic SQL even used?
selectpilot.dbo.CUSTOMERS.CUSTNAME,
case when ( ( coalesce( pilot.dbo.NSCUST.CUSTDES , '' ) = rtrim(ltrim(reverse( @P1 ))) ) ) then ( pilot.dbo.CUSTOMERS.CUSTDES ) else ( coalesce( pilot.dbo.NSCUST.CUSTDES , '' ) ) end ,
pilot.dbo.ORDERS.CURDATE ,
pilot.dbo.ORDERS.ORDNAME ,
pilot.dbo.ORDERS.BOOKNUM ,
pilot.dbo.DOCUMENTS.DOCNO ,
pilot.dbo.ORDSTATUS.ORDSTATUSDES ,
pilot.dbo.ORDSTATUS.XVR_GRADING ,
case when ( ( pilot.dbo.ORDERS.CLOSED = @P2 ) ) then ( @P3 ) else ( @P4 ) end ,
case when ( ( pilot.dbo.ORDERS.PCLOSED = @P5 ) ) then ( @P6 ) else ( @P7 ) end ,
pilot.dbo.CPROF.CPROFNUM ,
pilot.dbo.DEAL.ORDNAME ,
pilot.dbo.ORDERS.DETAILS ,
pilot.dbo.ORDERS.REFERENCE ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.QPRICE) )) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.XVR_ESTQPRICE) )) ,
(0.0 + ( case when ( ( pilot.dbo.XVR_ORDERS.GLOBAL = @P8 ) ) then ( convert(decimal(19,2), pilot.dbo.ORDERS.QPRICE) ) else ( convert(decimal(19,2), pilot.dbo.ORDERS.XVR_ESTQPRICE) ) end )) ,
(0.0 + ( convert(decimal(11,2), pilot.dbo.ORDERS.T$PERCENT) )) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.DISPRICE) )) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.VAT) )) ,
coalesce( pilot.dbo.TAXES.TAXCODE , '''' ) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.TOTPRICE) )) ,
pilot.dbo.CURRENCIES.CODE ,
(0.0 + ( coalesce( convert(decimal(19,2), pilot.dbo.ORDERSA.TOTPURCHASEPRICE) , 0.0 ) )) ,
pilot.dbo.PAY.PAYCODE ,
pilot.dbo.PAY.PAYDES ,
pilot.dbo.XVR_ORDERSB.INV_NUM ,
coalesce( pilot.dbo.ORDERSA.CPAY , 0 ) ,
coalesce( pilot.dbo.ORDERSA.CPAYDES , '' ) ,
pilot.dbo.XVR_ORDERSB.COMMITMONTHS ,
pilot.dbo.XVR_ORDERSB.COURSEDAYS ,
pilot.dbo.XVR_ORDERSB.COURSEDEMAND ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.ORDERS.XVR_TRSREQUANT) )) ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.ORDERS.XVR_TRSTQUANT) )) ,
system.dbo.USERS.USERLOGIN ,
substring( system.dbo.USERS.USERNAME , 1, 12) ,
pilot.dbo.XVR_CONTRACT.CONTRACTCODE ,
pilot.dbo.XVR_CONTRACT.INV_DAYS ,
coalesce( WAREHOUSES1.WARHSNAME , '' ) ,
coalesce( WAREHOUSES1.LOCNAME , '' ) ,
pilot.dbo.WAREHOUSES.WARHSNAME ,
DESTCODES5.CODE ,
pilot.dbo.XVR_ORDERS.WORKADDRESS ,
XVR_SETTLEMENT8.SETCODE ,
XVR_SETTLEMENT8.SETDES ,
ZONES7.ZONECODE ,
pilot.dbo.XVR_ORDERS.POSTBOX ,
pilot.dbo.XVR_ORDERS.WORKZIP ,
pilot.dbo.DESTCODES.CODE ,
pilot.dbo.PHONEBOOK.NAME ,
pilot.dbo.PHONEBOOK.PHONENUM ,
pilot.dbo.BRANCHES.BRANCHNAME ,
USERS5.USERLOGIN ,
substring( USERS5.USERNAME , 1, 12) ,
pilot.dbo.CPROFTYPES.TYPECODE ,
pilot.dbo.CPROFTYPES.TYPEDES ,
ORDERS9.ORDNAME ,
pilot.dbo.XVR_ORDERS.JOBNAME ,
pilot.dbo.XVR_ORDERS.JOBSTART ,
JOBTYPES6.JOBTYPE ,
pilot.dbo.ORDERS.XVR_STARTIMM ,
ZONES8.ZONECODE ,
PART5.PARTNAME ,
pilot.dbo.XVR_ORDERS.DURATION ,
substring( PART5.PARTDES , 1, 16) ,
pilot.dbo.XVR_ORDERS.ENDDATE ,
pilot.dbo.XVR_ORDERS.MANQUANT ,
pilot.dbo.XVR_ORDERS.DISCRET ,
pilot.dbo.XVR_ORDERSB.WANTEDAGE ,
pilot.dbo.XVR_ORDERSB.WANTEDTOAGE ,
pilot.dbo.XVR_ORDERS.SPEAKTO ,
pilot.dbo.XVR_ORDERS.DISCRETINVOICE ,
pilot.dbo.XVR_FAILREASON.FAILREASONNAME ,
pilot.dbo.XVR_FAILREASON.FAILREASONDES ,
pilot.dbo.XVR_ORDERS.NEWORDRDATE ,
pilot.dbo.XVR_REOPENREASON.REOPENRNAME ,
pilot.dbo.XVR_ORDERS.COMPLEXORD ,
pilot.dbo.XVR_WAGETYPES.WAGETYPECODE ,
(0.0 + ( convert(decimal(6,2), pilot.dbo.XVR_ORDERS.ASSIGNPERCENT) )) ,
(0.0 + ( convert(decimal(13,2), pilot.dbo.XVR_ORDERSB.FIXEDPRICE) )) ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.HOURWAGE) )) ,
pilot.dbo.XVR_ORDERSB.GLOBALFLAG ,
(0.0 + ( convert(decimal(12,2), pilot.dbo.XVR_ORDERS.GLOBALWAGE) )) ,
(0.0 + ( case when ( ( convert(decimal(6,2), pilot.dbo.XVR_ORDERS.ASSIGNPERCENT) <> 0.000000000 ) ) then ( case when ( ( ( convert(decimal(6,2), pilot.dbo.XVR_ORDERS.ASSIGNPERCENT) * convert(decimal(12,2), pilot.dbo.XVR_ORDERS.GLOBALWAGE) ) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) >= convert(decimal(12,2),pilot.dbo.XVR_ORDERSB.MIN_COMISSION) ) ) then ( ( convert(decimal(6,2), pilot.dbo.XVR_ORDERS.ASSIGNPERCENT) * convert(decimal(12,2), pilot.dbo.XVR_ORDERS.GLOBALWAGE) ) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) ) else ( convert(decimal(12,2), pilot.dbo.XVR_ORDERSB.MIN_COMISSION) ) end ) when ( ( convert(decimal(13,2), pilot.dbo.XVR_ORDERSB.FIXEDPRICE) <> 0.000000000 ) ) then ( convert(decimal(13,2), pilot.dbo.XVR_ORDERSB.FIXEDPRICE) ) when ( ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.HOURWAGE) <> 0.000000000 ) ) then ( ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.HOURWAGE) * convert(decimal(14,3), pilot.dbo.XVR_ORDERS.CUSTDEBITBASIS) ) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) ) else ( ( convert(decimal(12,2), pilot.dbo.XVR_ORDERS.GLOBALWAGE) * convert(decimal(14,3), pilot.dbo.XVR_ORDERS.CUSTDEBITBASIS) ) / (case when ( 100.000000000 ) = 0 then 1 else ( 100.000000000 ) end) ) end )) ,
pilot.dbo.XVR_ORDERS.CUSTFIXEDTARIFF ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.CUSTHOURTARIFF) )) ,
pilot.dbo.XVR_ORDERS.PERHRTARIFF ,
(0.0 + ( convert(decimal(14,3), pilot.dbo.XVR_ORDERS.CUSTDEBITBASIS) )) ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.JOURNEYSUM1) )) ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.JOURNEYSUM2) )) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.XVR_ORDERS.HOFSHIHODSHI) )) ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.XVR_ORDERS.DAYJOURNEYMAX) )) ,
pilot.dbo.XVR_ORDERS.LOGICONDITION ,
pilot.dbo.XVR_ORDERS.IVTYPE ,
pilot.dbo.XVR_ORDERSB.NAKED ,
pilot.dbo.XVR_ORDERSB.OUTSOURCING ,
pilot.dbo.XVR_ORDERSB.PAYROLL ,
pilot.dbo.ORDERS.XVR_EXTRAHCONTRACT ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.MONTHTOT) )) ,
pilot.dbo.ORDERS.XVR_SPLITCONTRACT ,
pilot.dbo.XVR_ORDERS.WEEKENDWORK ,
pilot.dbo.XVR_ORDERS.SHIFTCONTRACT ,
pilot.dbo.XVR_ORDERS.MIXEDCONTRACT ,
pilot.dbo.XVR_ORDERS.EXTRACONTRACT ,
(0.0 + ( convert(decimal(11,2), pilot.dbo.XVR_ORDERS.FRIDAYTOT) )) ,
(0.0 + ( convert(decimal(8,2), pilot.dbo.XVR_ORDERS.NORMALHOURADD) )) ,
pilot.dbo.XVR_ORDERS.PWEEKSHIFTNUM ,
pilot.dbo.XVR_ORDERS.RIDEADD ,
(0.0 + ( convert(decimal(11,2), pilot.dbo.XVR_ORDERS.SPLITADD) )) ,
pilot.dbo.XVR_ORDERS.HOURSUMMARY ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.COSTCOEFF) )) ,
pilot.dbo.XVR_ORDERS.REQDATE ,
pilot.dbo.XVR_TRSTOPIC.TRSTOPICNAME ,
substring( pilot.dbo.XVR_TRSTOPIC.TRSTOPICDES , 1, 12) ,
pilot.dbo.XVR_TOTRS.TOTRSNAME ,
pilot.dbo.XVR_ORDERSB.URGENT ,
pilot.dbo.ORDERS.XVR_DAYSEVALUATION ,
pilot.dbo.XVR_ORDERS.GETWORKDATE ,
pilot.dbo.SHIPTYPES.STCODE ,
pilot.dbo.SHIPTYPES.STDES ,
pilot.dbo.XVR_ORDERSB.SENDWORKDATE ,
SHIPTYPES6.STCODE ,
SHIPTYPES6.STDES ,
pilot.dbo.XVR_ORDERS.GLOBAL ,
pilot.dbo.PRICELIST.PLNAME ,
pilot.dbo.XVR_ORDERS.EVENTDATEA ,
XVR_EVENTSTYPES1.EVENTTYPECODE ,
USERSB5.IDNUMBER ,
USERSB5.SNAME ,
pilot.dbo.XVR_ACTFIELDS.ACTFIELDCODE ,
ZONES5.ZONECODE ,
pilot.dbo.COMPANIES.COMPANYNAME ,
USERS6.USERLOGIN ,
pilot.dbo.ORDERS.ORD ,
pilot.dbo.XVR_ORDERSB.XVR_MANFLAG ,
pilot.dbo.XVR_ORDERSB.ORDTRANSPORT ,
pilot.dbo.CUSTOMERS.CUSTDES ,
pilot.dbo.ORDERS.TYPE ,
pilot.dbo.ORDERS.CLOSED ,
pilot.dbo.ORDERS.PCLOSED ,
pilot.dbo.ORDERS.FORECASTFLAG ,
pilot.dbo.AGENTS.AGENTCODE ,
(0.0 + ( convert(decimal(27,9), pilot.dbo.ORDERS.LEXCHANGE) )) ,
coalesce( pilot.dbo.MODELS.MODELNAME , '''' ) ,
coalesce( pilot.dbo.ORDERSA.QUANT , 0 ) ,
(0.0 + ( convert(decimal(19,2),
pilot.dbo.ORDERS.QPROFIT) )) ,
(0.0 + ( ( 100.000000000 * convert(decimal(19,2), pilot.dbo.ORDERS.QPROFIT) ) / (case when ( case when ( ( @P9 = @P10 ) ) then ( coalesce( convert(decimal(19,2), pilot.dbo.ORDERSA.TOTPURCHASEPRICE) , 0.0 ) ) else ( convert(decimal(19,2), pilot.dbo.ORDERS.DISPRICE) ) end ) = 0 then 1 else ( case when ( ( @P11 = @P12 ) ) then ( coalesce( convert(decimal(19,2), pilot.dbo.ORDERSA.TOTPURCHASEPRICE) , 0.0 ) ) else ( convert(decimal(19,2), pilot.dbo.ORDERS.DISPRICE) ) end ) end) )) ,
CURRENCIES1.CODE ,
(0.0 + ( ( convert(decimal(27,9), pilot.dbo.ORDERS.LEXCHANGE) * CURRENCIES1.EXCHQUANT ) )) ,
(0.0 + ( coalesce( convert(decimal(19,2), pilot.dbo.ORDERSA.LEXCHTOL) , 0.0 ) )) ,
coalesce( pilot.dbo.ORDERSA.LEXCHNEG , '' ) ,
pilot.dbo.ORDERS.ADJPRICEFLAG , coalesce( pilot.dbo.ORDERSA.LINKOPTIONS , '' ) ,
pilot.dbo.ORDERS.DOER ,
case when ( ( coalesce( pilot.dbo.ORDERSA.OBLIGOFLAG , '' ) = @P13 ) ) then ( @P14 ) else ( @P15 ) end ,
(0.0 + ( convert(decimal(19,2), pilot.dbo.ORDERS.ADVBAL) )) ,
(0.0 + ( convert(decimal(9,2), pilot.dbo.ORDERS.ADVPERCENT) )) ,
case when ( ( CUSTOMERS1.ACCOUNT <> @P16 ) ) then ( CUSTOMERS1.ACCOUNT ) else ( pilot.dbo.CUSTOMERS.ACCOUNT ) end ,
pilot.dbo.PHONEBOOK.NAME ,
case when ( ( coalesce( pilot.dbo.NSCUST.FAX , '' ) <> @P17 ) ) then ( coalesce( pilot.dbo.NSCUST.FAX , '' ) ) when ( ( pilot.dbo.PHONEBOOK.FAX <> @P18 ) ) then ( pilot.dbo.PHONEBOOK.FAX ) else ( pilot.dbo.CUSTOMERS.FAX ) end , pilot.dbo.ORDERS.AGENT , case when ( ( coalesce( pilot.dbo.NSCUST.EMAIL , '' ) <> @P19 ) ) then ( coalesce( pilot.dbo.NSCUST.EMAIL , '' ) ) when ( ( pilot.dbo.PHONEBOOK.EMAIL <> @P20 ) ) then ( pilot.dbo.PHONEBOOK.EMAIL ) else ( coalesce( pilot.dbo.CUSTOMERSA.EMAIL , '' ) ) end ,
pilot.dbo.ORDERS.BRANCH ,
pilot.dbo.ORDSTATUS.CHANGEFLAG ,
pilot.dbo.CPROF.CURRENCY ,
pilot.dbo.CPROF.CPROFSTAT ,
pilot.dbo.ORDERS.CURRENCY ,
pilot.dbo.ORDERS.CUST ,
pilot.dbo.CUSTOMERS.CUST ,
pilot.dbo.CUSTOMERS.CURRENCY ,
pilot.dbo.CUSTOMERS.LINKDATE ,
pilot.dbo.CUSTOMERS.PAY ,
pilot.dbo.CUSTOMERS.SHIPTYPE ,
pilot.dbo.ORDERS.DEAL ,
pilot.dbo.DEAL.CLOSED ,
pilot.dbo.DEAL.CURRENCY ,
pilot.dbo.DEAL.CUST ,
pilot.dbo.ORDERS.DESTCODE ,
coalesce( pilot.dbo.ORDERSA.DIALOGFLAG , '' ) ,
pilot.dbo.DOCUMENTS.DOC ,
coalesce( pilot.dbo.ORDERSCHED.DOER2 , 0 ) ,
coalesce( pilot.dbo.ORDERSCHED.DOER3 , 0 ) ,
pilot.dbo.DOCUMENTS.TYPE ,
@P21 ,
coalesce( pilot.dbo.ORDERSA.DIALOGFLAG , '' ) ,
(0.0 + ( convert(decimal(27,9), pilot.dbo.CURRENCIES.EXCHANGE) )) ,
CURRENCIES1.EXCHQUANT ,
@P22 ,
pilot.dbo.ORDERS.LCURRENCY ,
pilot.dbo.ORDERS.ORD ,
@P23 ,
pilot.dbo.CUSTOMERS.MCUST ,
coalesce( pilot.dbo.ORDERSA.MODEL , 0 ) ,
pilot.dbo.DEAL.NOCOPY ,
pilot.dbo.ORDERS.ORD ,
coalesce( pilot.dbo.NSCUST.CUSTDES , '' ) ,
pilot.dbo.CUSTOMERS.NSFLAG ,
coalesce( pilot.dbo.NSCUST.TYPE , '' ) ,
coalesce( pilot.dbo.ORDERSA.OBLIGOFLAG , '' ) ,
pilot.dbo.ORDSTATUS.OPENDOCFLAG ,
@P24 ,
pilot.dbo.ORDERS.ORD ,
pilot.dbo.ORDSTATUS.CLOSED ,
pilot.dbo.ORDERS.ORDSTATUS ,
pilot.dbo.ORDERS.ORDTYPE ,
pilot.dbo.ORDERS.PAY ,
pilot.dbo.CUSTOMERS.PAYCUST ,
pilot.dbo.ORDERS.PHONE ,
pilot.dbo.ORDERS.PROF ,
pilot.dbo.CPROF.PROF ,
pilot.dbo.CPROFA.PROJ ,
pilot.dbo.ORDERS.PROJ ,
pilot.dbo.DOCUMENTS.CUST ,
pilot.dbo.DOCUMENTS.PLIST ,
pilot.dbo.CUSTOMERS.SECONDLANGTEXT ,
coalesce( pilot.dbo.CPROFSTATS.STATDES , '' ) ,
@P25 ,
pilot.dbo.CPROF.SUP ,
coalesce( pilot.dbo.ORDERSA.TAX , 0 ) ,
pilot.dbo.ORDERS.T$USER ,
pilot.dbo.CUSTOMERS.VATFLAG ,
pilot.dbo.ORDERS.WARHS ,
pilot.dbo.BRANCHES.XVR_ACTFIELD ,
pilot.dbo.ORDERS.XVR_CONTRACT ,
pilot.dbo.XVR_ORDERS.CREATEUSER ,
pilot.dbo.XVR_ORDERS.FAILREASON ,
pilot.dbo.XVR_ORDERS.HOWHEARD ,
pilot.dbo.XVR_ORDERS.JOBT ,
pilot.dbo.XVR_ORDERS.LASTORD ,
USERSB8.T$USER ,
USERSB8.USERB ,
pilot.dbo.ORDERS.ORD ,
pilot.dbo.ORDERS.ORD ,
pilot.dbo.XVR_ORDERS.PART ,
pilot.dbo.XVR_ACTFIELDS.PROFFLAG ,
pilot.dbo.XVR_ORDERS.REOPENR ,
coalesce( SHIPTO5.XVR_SETTLEMENT , 0 ) ,
coalesce( SHIPTO5.TYPE , '' ) ,
@P26 ,
XVR_USERSB8.PROFFLAG ,
pilot.dbo.XVR_ORDERS.WAGETYPE ,
pilot.dbo.XVR_ORDERS.WORKSETTLEMENT ,
pilot.dbo.XVR_ORDERS.WORKSITE ,
pilot.dbo.XVR_ORDERS.WORKZONE ,
pilot.dbo.BRANCHES.XVR_ZONE ,
pilot.dbo.DOCUMENTS.FLAG ,
pilot.dbo.DOCUMENTS.TOWARHS ,
pilot.dbo.XVR_ORDERSB.ORD ,
coalesce( USERS2.USERLOGIN , '' ) ,
coalesce( pilot.dbo.CUSTOMERSA.MAILINTERFACE , '' ) ,
case when ( ( pilot.dbo.ORDERS.REFERENCE <> @P27 ) ) then ( @P28 ) else ( @P29 ) end ,
coalesce( pilot.dbo.ORDERSA.CHANGESTATFLAG , '' ) ,
coalesce( pilot.dbo.CUSTOMERSA.EMAIL , '' ) ,
case when ( ( pilot.dbo.PHONEBOOK.EMAIL <> @P30 ) ) then ( pilot.dbo.PHONEBOOK.EMAIL ) else ( coalesce( pilot.dbo.CUSTOMERSA.EMAIL , '' ) ) end ,
@P31 ,
@P32 ,
@P33 ,
coalesce( USERS3.USERLOGIN , '' ) ,
- ( @P34 ) ,
coalesce( pilot.dbo.ORDERSCHED.SDATE , 0 ) ,
pilot.dbo.COMPDATA.COMP ,
case when ( ( coalesce( pilot.dbo.ORDERSCHED.SDATE , 0 ) <> @P35 ) ) then ( substring( system.dbo.tabula_dtoa( coalesce( pilot.dbo.ORDERSCHED.SDATE , 0 ) , 'day' , '' , 'à ,á ,â ,ã ,ä ,å ,ù ' ) , 1, 3) ) else ( @P36 ) end ,
@P37 ,
coalesce( pilot.dbo.ORDERSCHED.STIME , 0 ) ,
coalesce( pilot.dbo.ORDERSCHED.EDATE , 0 ) ,
case when ( ( coalesce( pilot.dbo.ORDERSCHED.EDATE , 0 ) <> @P38 ) ) then ( substring( system.dbo.tabula_dtoa( coalesce( pilot.dbo.ORDERSCHED.EDATE , 0 ) , 'day' , '' , 'à ,á ,â ,ã ,ä ,å ,ù ' ) , 1, 3) ) else ( @P39 ) end ,
coalesce( pilot.dbo.ORDERSCHED.ETIME , 0 ) ,
coalesce( pilot.dbo.ORDERSA.EXTFILEFLAG , '' ) ,
pilot.dbo.WAREHOUSES.LOCNAME ,
coalesce( pilot.dbo.ORDERSA.BONUSFLAG , '' ) ,
coalesce( pilot.dbo.CUSTCAMPAIGNS.CCNUM , '' ) ,
@P40 ,
(0.0 + ( coalesce( convert(decimal(11,2), pilot.dbo.ORDERSA.CPERCENT) , 0.0 ) )) ,
@P41 ,
pilot.dbo.CUSTTOPICS.MAILFLAG ,
pilot.dbo.CUSTTOPICS.TOPIC ,
@P42 ,
coalesce( pilot.dbo.ORDERSA.CC , 0 ) ,
coalesce( pilot.dbo.CUSTCAMPAIGNS.CUST , 0 ) ,
coalesce( pilot.dbo.CUSTCAMPAIGNS.CCSTATUS , 0 ) ,
coalesce( pilot.dbo.ORDERSA.FROMWARHS , 0 ) ,
pilot.dbo.XVR_ORDERS.ZONE ,
pilot.dbo.XVR_ORDERSB.KIBUTS_FLAG ,
pilot.dbo.XVR_ORDERS.TRSTOPIC ,
pilot.dbo.XVR_ORDERS.TOTRS ,
pilot.dbo.ORDERS.SHIPTYPE ,
pilot.dbo.XVR_ORDERS.HEVERSHIPTYPE ,
pilot.dbo.ORDERS.PLIST ,
pilot.dbo.XVR_ORDERS.EVENTTYPE ,
pilot.dbo.XVR_ORDERS.RECORDERA ,
pilot.dbo.BRANCHES.COMPANY ,
pilot.dbo.BRANCHES.XVR_MANAGER
frompilot.dbo.ORDERS
inner joinpilot.dbo.COMPDATA on ( pilot.dbo.COMPDATA.COMP = - ( @P43 ) )
inner joinpilot.dbo.CUSTTOPICS on ( pilot.dbo.CUSTTOPICS.TOPIC = - ( @P44 ) )
inner joinpilot.dbo.BRANCHES on ( pilot.dbo.BRANCHES.BRANCH = pilot.dbo.ORDERS.BRANCH )
inner joinpilot.dbo.XVR_ORDERS on ( pilot.dbo.XVR_ORDERS.ORD = pilot.dbo.ORDERS.ORD )
inner joinpilot.dbo.CUSTOMERS on ( pilot.dbo.CUSTOMERS.CUST = pilot.dbo.ORDERS.CUST )
inner joinsystem.dbo.USERSB USERSB8 on ( USERSB8.T$USER = @P45 )
inner joinpilot.dbo.CPROF on ( pilot.dbo.CPROF.PROF = pilot.dbo.ORDERS.PROF )
inner joinpilot.dbo.ORDSTATUS on ( pilot.dbo.ORDSTATUS.ORDSTATUS = pilot.dbo.ORDERS.ORDSTATUS )
inner joinsystem.dbo.XVR_USERSB XVR_USERSB8 on ( XVR_USERSB8.USERB = USERSB8.USERB )
inner joinpilot.dbo.DOCUMENTS on ( pilot.dbo.DOCUMENTS.DOC = pilot.dbo.ORDERS.PROJ )
inner joinpilot.dbo.XVR_SETTLEMENT on 1 = 1
inner joinpilot.dbo.HOWHEARD HOWHEARD6 on ( HOWHEARD6.HOWHEARD = pilot.dbo.XVR_ORDERS.HOWHEARD )
inner joinpilot.dbo.DEAL on ( pilot.dbo.DEAL.DEAL = pilot.dbo.ORDERS.DEAL )
inner joinpilot.dbo.CPROFA on ( pilot.dbo.CPROFA.PROF = pilot.dbo.CPROF.PROF )
inner joinpilot.dbo.CURRENCIES on ( pilot.dbo.CURRENCIES.CURRENCY = pilot.dbo.ORDERS.CURRENCY )
inner joinpilot.dbo.PAY on ( pilot.dbo.PAY.PAY = pilot.dbo.ORDERS.PAY )
inner joinpilot.dbo.CURRENCIES CURRENCIES1 on ( CURRENCIES1.CURRENCY = pilot.dbo.ORDERS.LCURRENCY )
inner joinsystem.dbo.USERS on ( system.dbo.USERS.T$USER = pilot.dbo.ORDERS.DOER )
inner joinpilot.dbo.AGENTS on ( pilot.dbo.AGENTS.AGENT = pilot.dbo.ORDERS.AGENT )
inner joinpilot.dbo.XVR_ORDERSB on ( pilot.dbo.XVR_ORDERSB.ORD = pilot.dbo.ORDERS.ORD )
inner joinsystem.dbo.USERS USERS6 on ( USERS6.T$USER = pilot.dbo.BRANCHES.XVR_MANAGER )
inner joinpilot.dbo.COMPANIES on ( pilot.dbo.COMPANIES.COMPANY = pilot.dbo.BRANCHES.COMPANY )
inner joinpilot.dbo.ZONES ZONES5 on ( ZONES5.ZONE = pilot.dbo.BRANCHES.XVR_ZONE )
inner joinpilot.dbo.XVR_ACTFIELDS on ( pilot.dbo.XVR_ACTFIELDS.ACTFIELD = pilot.dbo.BRANCHES.XVR_ACTFIELD )
inner joinsystem.dbo.USERSB USERSB5 on ( USERSB5.USERB = pilot.dbo.XVR_ORDERS.RECORDERA )
inner joinpilot.dbo.XVR_EVENTSTYPES XVR_EVENTSTYPES1 on ( XVR_EVENTSTYPES1.EVENTTYPE = pilot.dbo.XVR_ORDERS.EVENTTYPE )
inner joinpilot.dbo.PRICELIST on ( pilot.dbo.PRICELIST.PLIST = pilot.dbo.ORDERS.PLIST )
inner joinpilot.dbo.SHIPTYPES SHIPTYPES6 on ( SHIPTYPES6.SHIPTYPE = pilot.dbo.XVR_ORDERS.HEVERSHIPTYPE )
inner joinpilot.dbo.SHIPTYPES on ( pilot.dbo.SHIPTYPES.SHIPTYPE = pilot.dbo.ORDERS.SHIPTYPE )
inner joinpilot.dbo.XVR_TOTRS on ( pilot.dbo.XVR_TOTRS.TOTRS = pilot.dbo.XVR_ORDERS.TOTRS )
inner joinpilot.dbo.XVR_TRSTOPIC on ( pilot.dbo.XVR_TRSTOPIC.TRSTOPIC = pilot.dbo.XVR_ORDERS.TRSTOPIC )
inner joinpilot.dbo.XVR_WAGETYPES on ( pilot.dbo.XVR_WAGETYPES.WAGETYPE = pilot.dbo.XVR_ORDERS.WAGETYPE )
inner joinpilot.dbo.XVR_REOPENREASON on ( pilot.dbo.XVR_REOPENREASON.REOPENR = pilot.dbo.XVR_ORDERS.REOPENR )
inner joinpilot.dbo.XVR_FAILREASON on ( pilot.dbo.XVR_FAILREASON.FAILREASON = pilot.dbo.XVR_ORDERS.FAILREASON )
inner joinpilot.dbo.PART PART5 on ( PART5.PART = pilot.dbo.XVR_ORDERS.PART )
inner joinpilot.dbo.ZONES ZONES8 on ( ZONES8.ZONE = pilot.dbo.XVR_ORDERS.ZONE )
inner joinpilot.dbo.JOBTYPES JOBTYPES6 on ( JOBTYPES6.JOBT = pilot.dbo.XVR_ORDERS.JOBT )
inner joinpilot.dbo.ORDERS ORDERS9 on ( ORDERS9.ORD = pilot.dbo.XVR_ORDERS.LASTORD )
inner joinpilot.dbo.CPROFTYPES on ( pilot.dbo.CPROFTYPES.CPROFTYPE = pilot.dbo.ORDERS.ORDTYPE )
inner joinsystem.dbo.USERS USERS5 on ( USERS5.T$USER = pilot.dbo.XVR_ORDERS.CREATEUSER )
inner joinpilot.dbo.CUSTOMERS CUSTOMERS1 on ( CUSTOMERS1.CUST = pilot.dbo.CUSTOMERS.PAYCUST )
inner joinpilot.dbo.PHONEBOOK on ( pilot.dbo.PHONEBOOK.PHONE = pilot.dbo.ORDERS.PHONE )
inner joinpilot.dbo.DESTCODES on ( pilot.dbo.DESTCODES.DESTCODE = pilot.dbo.ORDERS.DESTCODE )
inner joinpilot.dbo.ZONES ZONES7 on ( ZONES7.ZONE = pilot.dbo.XVR_ORDERS.WORKZONE )
inner joinpilot.dbo.XVR_SETTLEMENT XVR_SETTLEMENT8 on ( XVR_SETTLEMENT8.SETTLEMENT = pilot.dbo.XVR_ORDERS.WORKSETTLEMENT )
inner joinpilot.dbo.DESTCODES DESTCODES5 on ( DESTCODES5.DESTCODE = pilot.dbo.XVR_ORDERS.WORKSITE )
inner joinpilot.dbo.WAREHOUSES on ( pilot.dbo.WAREHOUSES.WARHS = pilot.dbo.ORDERS.WARHS )
inner joinpilot.dbo.XVR_CONTRACT on ( pilot.dbo.XVR_CONTRACT.CONTRACT = pilot.dbo.ORDERS.XVR_CONTRACT )
inner joinpilot.dbo.ADJPRICES on ( pilot.dbo.ADJPRICES.ADJPRICEFLAG = pilot.dbo.ORDERS.ADJPRICEFLAG )
left joinpilot.dbo.SHIPTO SHIPTO5 on ( SHIPTO5.TYPE = @P46 )
and ( SHIPTO5.IV = pilot.dbo.ORDERS.ORD )
left joinpilot.dbo.NSCUST on ( pilot.dbo.NSCUST.TYPE = @P47 )
and ( pilot.dbo.NSCUST.IV = pilot.dbo.ORDERS.ORD )
left joinpilot.dbo.ORDERSCHED on ( pilot.dbo.ORDERSCHED.ORD = pilot.dbo.ORDERS.ORD )
left joinpilot.dbo.ORDERSA on ( pilot.dbo.ORDERSA.ORD = pilot.dbo.ORDERS.ORD )
left joinpilot.dbo.TAXES on ( pilot.dbo.TAXES.TAX = coalesce( pilot.dbo.ORDERSA.TAX , 0 ) )
left joinpilot.dbo.CPROFSTATS on ( pilot.dbo.CPROFSTATS.CPROFSTAT = pilot.dbo.CPROF.CPROFSTAT )
left joinpilot.dbo.MODELS on ( pilot.dbo.MODELS.MODEL = coalesce( pilot.dbo.ORDERSA.MODEL , 0 ) )
left joinsystem.dbo.USERS USERS2 on ( USERS2.T$USER = coalesce( pilot.dbo.ORDERSCHED.DOER2 , 0 ) )
left joinpilot.dbo.CUSTOMERSA on ( pilot.dbo.CUSTOMERSA.CUST = pilot.dbo.CUSTOMERS.CUST )
left joinsystem.dbo.USERS USERS3 on ( USERS3.T$USER = coalesce( pilot.dbo.ORDERSCHED.DOER3 , 0 ) )
left joinpilot.dbo.CUSTCAMPAIGNS on ( pilot.dbo.CUSTCAMPAIGNS.CC = coalesce( pilot.dbo.ORDERSA.CC , 0 ) )
left joinpilot.dbo.WAREHOUSES WAREHOUSES1 on ( WAREHOUSES1.WARHS = coalesce( pilot.dbo.ORDERSA.FROMWARHS , 0 ) )
where( ( USERSB8.XVR_VIEW_COMBEMP = @P48 ) or ( coalesce( pilot.dbo.CUSTOMERSA.XVR_HEVERCUST , '' ) <> @P49 ) )
and ( coalesce( SHIPTO5.XVR_SETTLEMENT , 0 ) = pilot.dbo.XVR_SETTLEMENT.SETTLEMENT )
and ( ( XVR_USERSB8.PROFFLAG = @P50 ) or ( pilot.dbo.XVR_ACTFIELDS.PROFFLAG <> @P51 ) )
and ( ( XVR_USERSB8.XVR_SALLIMITFLAG = @P52 ) or ( pilot.dbo.CUSTOMERS.XVR_SALLIMITFLAG <> @P53 ) )
and ( pilot.dbo.ORDERS.ORDNAME <> @P54 )
and ( pilot.dbo.ORDERS.TYPE <> @P55 )
and ( pilot.dbo.ORDERS.ORDNAME = @P56 )
order by3 desc ,
4 desc
N 56°04'39.16"
E 12°55'05.25"
November 27, 2008 at 4:05 am
Yes Lowell is correct. You can use showplan... and check you are missing any index where you are joining. Try to find missing index.
If you are running this query frequently... you can create an index specifically for this query... or create a view.
November 27, 2008 at 6:53 am
Your ERD program HAS to have the ability to define joins between the tables it queries against....I think the place to fix your issue is in the application itself, rather than at the server side. Get with your vendor or person who administrates the program.
One program that I used before used the FK's in the database to define most of the relationships, but then we had to add additional ones...for example a view might have a key in it, but there was no FK for it to know what table that key referenced...we added a lot of stuff like that. we also had to tweak some of the realtionsships so it know that the relationship it grabbed from the FK's were outer joins or inner joins.
those 1=1 joins, along with parameter sniffing are your problem, so although you can't tweak the actual SQL's, you can make the ERD program better by adding those joins to it's array of relationships.
Lowell
November 27, 2008 at 10:30 am
Peso (11/27/2008)
The query returns 290 columns and joins 62 tables. Under these cirumstances I think 7 seconds is quite good.
Ditto.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2008 at 1:18 am
There are two ways to solve this problem. Either contact the vendor of your system and require changes, or (if it is a report) build this report outside the system.
We had some similar problems with our ERP system, and used both ways. Sometimes we succeeded in forcing the authors of code to rewrite it, or wrote the code ourselves as a stored procedure and asked them for a function that will call this procedure from ERP, sometimes we had to place the solution on the intranet.
And, of course, index tuning helped a lot.
November 28, 2008 at 1:24 am
Thanks for your replays.
i'll try to talk with the ERP developer.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply