SQL Server Performance

can i improve performance in this query

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by avipenina, Nov 23, 2008.

  1. avipenina New Member

    Hi,

    we use an ERP program that users run queries likes that very often.
    i run this query in the DTA and its say that nothing needs to be changed in the indexes (0% improve)
    can i improve this syntax in any way(this query run for 7SEC)?
    THX
    exec sp_executesql N'select pilot.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.db:confused:RDERS.CURDATE , pilot.db:confused:RDERS.ORDNAME , pilot.db:confused:RDERS.BOOKNUM , pilot.dbo.DOCUMENTS.DOCNO , pilot.db:confused:RDSTATUS.ORDSTATUSDES , pilot.db:confused:RDSTATUS.XVR_GRADING , case when ( ( pilot.db:confused:RDERS.CLOSED = @P2 ) ) then ( @P3 ) else ( @P4 ) end , case when ( ( pilot.db:confused:RDERS.PCLOSED = @P5 ) ) then ( @P6 ) else ( @P7 ) end , pilot.dbo.CPROF.CPROFNUM , pilot.dbo.DEAL.ORDNAME , pilot.db:confused:RDERS.DETAILS , pilot.db:confused:RDERS.REFERENCE , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.QPRICE) )) , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.XVR_ESTQPRICE) )) , (0.0 + ( case when ( ( pilot.dbo.XVR_ORDERS.GLOBAL = @P8 ) ) then ( convert(decimal(19,2), pilot.db:confused:RDERS.QPRICE) ) else ( convert(decimal(19,2), pilot.db:confused:RDERS.XVR_ESTQPRICE) ) end )) , (0.0 + ( convert(decimal(11,2), pilot.db:confused:RDERS.T$PERCENT) )) , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.DISPRICE) )) , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.VAT) )) , coalesce( pilot.dbo.TAXES.TAXCODE , '''' ) , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.TOTPRICE) )) , pilot.dbo.CURRENCIES.CODE , (0.0 + ( coalesce( convert(decimal(19,2), pilot.db:confused:RDERSA.TOTPURCHASEPRICE) , 0.0 ) )) , pilot.dbo.PAY.PAYCODE , pilot.dbo.PAY.PAYDES , pilot.dbo.XVR_ORDERSB.INV_NUM , coalesce( pilot.db:confused:RDERSA.CPAY , 0 ) , coalesce( pilot.db:confused:RDERSA.CPAYDES , '''' ) , pilot.dbo.XVR_ORDERSB.COMMITMONTHS , pilot.dbo.XVR_ORDERSB.COURSEDAYS , pilot.dbo.XVR_ORDERSB.COURSEDEMAND , (0.0 + ( convert(decimal(9,2), pilot.db:confused:RDERS.XVR_TRSREQUANT) )) , (0.0 + ( convert(decimal(9,2), pilot.db:confused:RDERS.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.db:confused:RDERS.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.db:confused:RDERS.XVR_EXTRAHCONTRACT , (0.0 + ( convert(decimal(9,2), pilot.dbo.XVR_ORDERS.MONTHTOT) )) , pilot.db:confused:RDERS.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.db:confused:RDERS.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.db:confused:RDERS.ORD , pilot.dbo.XVR_ORDERSB.XVR_MANFLAG , pilot.dbo.XVR_ORDERSB.ORDTRANSPORT , pilot.dbo.CUSTOMERS.CUSTDES , pilot.db:confused:RDERS.TYPE , pilot.db:confused:RDERS.CLOSED , pilot.db:confused:RDERS.PCLOSED , pilot.db:confused:RDERS.FORECASTFLAG , pilot.dbo.AGENTS.AGENTCODE , (0.0 + ( convert(decimal(27,9), pilot.db:confused:RDERS.LEXCHANGE) )) , coalesce( pilot.dbo.MODELS.MODELNAME , '''' ) , coalesce( pilot.db:confused:RDERSA.QUANT , 0 ) , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.QPROFIT) )) , (0.0 + ( ( 100.000000000 * convert(decimal(19,2), pilot.db:confused:RDERS.QPROFIT) ) / (case when ( case when ( ( @P9 = @P10 ) ) then ( coalesce( convert(decimal(19,2), pilot.db:confused:RDERSA.TOTPURCHASEPRICE) , 0.0 ) ) else ( convert(decimal(19,2), pilot.db:confused:RDERS.DISPRICE) ) end ) = 0 then 1 else ( case when ( ( @P11 = @P12 ) ) then ( coalesce( convert(decimal(19,2), pilot.db:confused:RDERSA.TOTPURCHASEPRICE) , 0.0 ) ) else ( convert(decimal(19,2), pilot.db:confused:RDERS.DISPRICE) ) end ) end) )) , CURRENCIES1.CODE , (0.0 + ( ( convert(decimal(27,9), pilot.db:confused:RDERS.LEXCHANGE) * CURRENCIES1.EXCHQUANT ) )) , (0.0 + ( coalesce( convert(decimal(19,2), pilot.db:confused:RDERSA.LEXCHTOL) , 0.0 ) )) , coalesce( pilot.db:confused:RDERSA.LEXCHNEG , '''' ) , pilot.db:confused:RDERS.ADJPRICEFLAG , coalesce( pilot.db:confused:RDERSA.LINKOPTIONS , '''' ) , pilot.db:confused:RDERS.DOER , case when ( ( coalesce( pilot.db:confused:RDERSA.OBLIGOFLAG , '''' ) = @P13 ) ) then ( @P14 ) else ( @P15 ) end , (0.0 + ( convert(decimal(19,2), pilot.db:confused:RDERS.ADVBAL) )) , (0.0 + ( convert(decimal(9,2), pilot.db:confused:RDERS.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.db:confused:RDERS.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.db:confused:RDERS.BRANCH , pilot.db:confused:RDSTATUS.CHANGEFLAG , pilot.dbo.CPROF.CURRENCY , pilot.dbo.CPROF.CPROFSTAT , pilot.db:confused:RDERS.CURRENCY , pilot.db:confused:RDERS.CUST , pilot.dbo.CUSTOMERS.CUST , pilot.dbo.CUSTOMERS.CURRENCY , pilot.dbo.CUSTOMERS.LINKDATE , pilot.dbo.CUSTOMERS.PAY , pilot.dbo.CUSTOMERS.SHIPTYPE , pilot.db:confused:RDERS.DEAL , pilot.dbo.DEAL.CLOSED , pilot.dbo.DEAL.CURRENCY , pilot.dbo.DEAL.CUST , pilot.db:confused:RDERS.DESTCODE , coalesce( pilot.db:confused:RDERSA.DIALOGFLAG , '''' ) , pilot.dbo.DOCUMENTS.DOC , coalesce( pilot.db:confused:RDERSCHED.DOER2 , 0 ) , coalesce( pilot.db:confused:RDERSCHED.DOER3 , 0 ) , pilot.dbo.DOCUMENTS.TYPE , @P21 , coalesce( pilot.db:confused:RDERSA.DIALOGFLAG , '''' ) , (0.0 + ( convert(decimal(27,9), pilot.dbo.CURRENCIES.EXCHANGE) )) , CURRENCIES1.EXCHQUANT , @P22 , pilot.db:confused:RDERS.LCURRENCY , pilot.db:confused:RDERS.ORD , @P23 , pilot.dbo.CUSTOMERS.MCUST , coalesce( pilot.db:confused:RDERSA.MODEL , 0 ) , pilot.dbo.DEAL.NOCOPY , pilot.db:confused:RDERS.ORD , coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) , pilot.dbo.CUSTOMERS.NSFLAG , coalesce( pilot.dbo.NSCUST.TYPE , '''' ) , coalesce( pilot.db:confused:RDERSA.OBLIGOFLAG , '''' ) , pilot.db:confused:RDSTATUS.OPENDOCFLAG , @P24 , pilot.db:confused:RDERS.ORD , pilot.db:confused:RDSTATUS.CLOSED , pilot.db:confused:RDERS.ORDSTATUS , pilot.db:confused:RDERS.ORDTYPE , pilot.db:confused:RDERS.PAY , pilot.dbo.CUSTOMERS.PAYCUST , pilot.db:confused:RDERS.PHONE , pilot.db:confused:RDERS.PROF , pilot.dbo.CPROF.PROF , pilot.dbo.CPROFA.PROJ , pilot.db:confused:RDERS.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.db:confused:RDERSA.TAX , 0 ) , pilot.db:confused:RDERS.T$USER , pilot.dbo.CUSTOMERS.VATFLAG , pilot.db:confused:RDERS.WARHS , pilot.dbo.BRANCHES.XVR_ACTFIELD , pilot.db:confused:RDERS.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.db:confused:RDERS.ORD , pilot.db:confused:RDERS.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.db:confused:RDERS.REFERENCE <> @P27 ) ) then ( @P28 ) else ( @P29 ) end , coalesce( pilot.db:confused:RDERSA.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.db:confused:RDERSCHED.SDATE , 0 ) , pilot.dbo.COMPDATA.COMP , case when ( ( coalesce( pilot.db:confused:RDERSCHED.SDATE , 0 ) <> @P35 ) ) then ( substring( system.dbo.tabula_dtoa( coalesce( pilot.db:confused:RDERSCHED.SDATE , 0 ) , ''day'' , '''' , ''א ,ב ,ג ,ד ,ה ,ו ,ש '' ) , 1, 3) ) else ( @P36 ) end , @P37 , coalesce( pilot.db:confused:RDERSCHED.STIME , 0 ) , coalesce( pilot.db:confused:RDERSCHED.EDATE , 0 ) , case when ( ( coalesce( pilot.db:confused:RDERSCHED.EDATE , 0 ) <> @P38 ) ) then ( substring( system.dbo.tabula_dtoa( coalesce( pilot.db:confused:RDERSCHED.EDATE , 0 ) , ''day'' , '''' , ''א ,ב ,ג ,ד ,ה ,ו ,ש '' ) , 1, 3) ) else ( @P39 ) end , coalesce( pilot.db:confused:RDERSCHED.ETIME , 0 ) , coalesce( pilot.db:confused:RDERSA.EXTFILEFLAG , '''' ) , pilot.dbo.WAREHOUSES.LOCNAME , coalesce( pilot.db:confused:RDERSA.BONUSFLAG , '''' ) , coalesce( pilot.dbo.CUSTCAMPAIGNS.CCNUM , '''' ) , @P40 , (0.0 + ( coalesce( convert(decimal(11,2), pilot.db:confused:RDERSA.CPERCENT) , 0.0 ) )) , @P41 , pilot.dbo.CUSTTOPICS.MAILFLAG , pilot.dbo.CUSTTOPICS.TOPIC , @P42 , coalesce( pilot.db:confused:RDERSA.CC , 0 ) , coalesce( pilot.dbo.CUSTCAMPAIGNS.CUST , 0 ) , coalesce( pilot.dbo.CUSTCAMPAIGNS.CCSTATUS , 0 ) , coalesce( pilot.db:confused:RDERSA.FROMWARHS , 0 ) , pilot.dbo.XVR_ORDERS.ZONE , pilot.dbo.XVR_ORDERSB.KIBUTS_FLAG , pilot.dbo.XVR_ORDERS.TRSTOPIC , pilot.dbo.XVR_ORDERS.TOTRS , pilot.db:confused:RDERS.SHIPTYPE , pilot.dbo.XVR_ORDERS.HEVERSHIPTYPE , pilot.db:confused:RDERS.PLIST , pilot.dbo.XVR_ORDERS.EVENTTYPE , pilot.dbo.XVR_ORDERS.RECORDERA , pilot.dbo.BRANCHES.COMPANY , pilot.dbo.BRANCHES.XVR_MANAGER
    from pilot.db:confused:RDERS inner join pilot.dbo.COMPDATA on ( pilot.dbo.COMPDATA.COMP = - ( @P43 ) ) inner join pilot.dbo.CUSTTOPICS on ( pilot.dbo.CUSTTOPICS.TOPIC = - ( @P44 ) ) inner join pilot.dbo.BRANCHES on ( pilot.dbo.BRANCHES.BRANCH = pilot.db:confused:RDERS.BRANCH ) inner join pilot.dbo.XVR_ORDERS on ( pilot.dbo.XVR_ORDERS.ORD = pilot.db:confused:RDERS.ORD ) inner join pilot.dbo.CUSTOMERS on ( pilot.dbo.CUSTOMERS.CUST = pilot.db:confused:RDERS.CUST ) inner join system.dbo.USERSB USERSB8 on ( USERSB8.T$USER = @P45 ) inner join pilot.dbo.CPROF on ( pilot.dbo.CPROF.PROF = pilot.db:confused:RDERS.PROF ) inner join pilot.db:confused:RDSTATUS on ( pilot.db:confused:RDSTATUS.ORDSTATUS = pilot.db:confused:RDERS.ORDSTATUS ) inner join system.dbo.XVR_USERSB XVR_USERSB8 on ( XVR_USERSB8.USERB = USERSB8.USERB ) inner join pilot.dbo.DOCUMENTS on ( pilot.dbo.DOCUMENTS.DOC = pilot.db:confused:RDERS.PROJ ) inner join pilot.dbo.XVR_SETTLEMENT on 1 = 1 inner join pilot.dbo.HOWHEARD HOWHEARD6 on ( HOWHEARD6.HOWHEARD = pilot.dbo.XVR_ORDERS.HOWHEARD ) inner join pilot.dbo.DEAL on ( pilot.dbo.DEAL.DEAL = pilot.db:confused:RDERS.DEAL ) inner join pilot.dbo.CPROFA on ( pilot.dbo.CPROFA.PROF = pilot.dbo.CPROF.PROF ) inner join pilot.dbo.CURRENCIES on ( pilot.dbo.CURRENCIES.CURRENCY = pilot.db:confused:RDERS.CURRENCY ) inner join pilot.dbo.PAY on ( pilot.dbo.PAY.PAY = pilot.db:confused:RDERS.PAY ) inner join pilot.dbo.CURRENCIES CURRENCIES1 on ( CURRENCIES1.CURRENCY = pilot.db:confused:RDERS.LCURRENCY ) inner join system.dbo.USERS on ( system.dbo.USERS.T$USER = pilot.db:confused:RDERS.DOER ) inner join pilot.dbo.AGENTS on ( pilot.dbo.AGENTS.AGENT = pilot.db:confused:RDERS.AGENT ) inner join pilot.dbo.XVR_ORDERSB on ( pilot.dbo.XVR_ORDERSB.ORD = pilot.db:confused:RDERS.ORD ) inner join system.dbo.USERS USERS6 on ( USERS6.T$USER = pilot.dbo.BRANCHES.XVR_MANAGER ) inner join pilot.dbo.COMPANIES on ( pilot.dbo.COMPANIES.COMPANY = pilot.dbo.BRANCHES.COMPANY ) inner join pilot.dbo.ZONES ZONES5 on ( ZONES5.ZONE = pilot.dbo.BRANCHES.XVR_ZONE ) inner join pilot.dbo.XVR_ACTFIELDS on ( pilot.dbo.XVR_ACTFIELDS.ACTFIELD = pilot.dbo.BRANCHES.XVR_ACTFIELD ) inner join system.dbo.USERSB USERSB5 on ( USERSB5.USERB = pilot.dbo.XVR_ORDERS.RECORDERA ) inner join pilot.dbo.XVR_EVENTSTYPES XVR_EVENTSTYPES1 on ( XVR_EVENTSTYPES1.EVENTTYPE = pilot.dbo.XVR_ORDERS.EVENTTYPE ) inner join pilot.dbo.PRICELIST on ( pilot.dbo.PRICELIST.PLIST = pilot.db:confused:RDERS.PLIST ) inner join pilot.dbo.SHIPTYPES SHIPTYPES6 on ( SHIPTYPES6.SHIPTYPE = pilot.dbo.XVR_ORDERS.HEVERSHIPTYPE ) inner join pilot.dbo.SHIPTYPES on ( pilot.dbo.SHIPTYPES.SHIPTYPE = pilot.db:confused:RDERS.SHIPTYPE ) inner join pilot.dbo.XVR_TOTRS on ( pilot.dbo.XVR_TOTRS.TOTRS = pilot.dbo.XVR_ORDERS.TOTRS ) inner join pilot.dbo.XVR_TRSTOPIC on ( pilot.dbo.XVR_TRSTOPIC.TRSTOPIC = pilot.dbo.XVR_ORDERS.TRSTOPIC ) inner join pilot.dbo.XVR_WAGETYPES on ( pilot.dbo.XVR_WAGETYPES.WAGETYPE = pilot.dbo.XVR_ORDERS.WAGETYPE ) inner join pilot.dbo.XVR_REOPENREASON on ( pilot.dbo.XVR_REOPENREASON.REOPENR = pilot.dbo.XVR_ORDERS.REOPENR ) inner join pilot.dbo.XVR_FAILREASON on ( pilot.dbo.XVR_FAILREASON.FAILREASON = pilot.dbo.XVR_ORDERS.FAILREASON ) inner join pilot.dbo.PART PART5 on ( PART5.PART = pilot.dbo.XVR_ORDERS.PART ) inner join pilot.dbo.ZONES ZONES8 on ( ZONES8.ZONE = pilot.dbo.XVR_ORDERS.ZONE ) inner join pilot.dbo.JOBTYPES JOBTYPES6 on ( JOBTYPES6.JOBT = pilot.dbo.XVR_ORDERS.JOBT ) inner join pilot.db:confused:RDERS ORDERS9 on ( ORDERS9.ORD = pilot.dbo.XVR_ORDERS.LASTORD ) inner join pilot.dbo.CPROFTYPES on ( pilot.dbo.CPROFTYPES.CPROFTYPE = pilot.db:confused:RDERS.ORDTYPE ) inner join system.dbo.USERS USERS5 on ( USERS5.T$USER = pilot.dbo.XVR_ORDERS.CREATEUSER ) inner join pilot.dbo.CUSTOMERS CUSTOMERS1 on ( CUSTOMERS1.CUST = pilot.dbo.CUSTOMERS.PAYCUST ) inner join pilot.dbo.PHONEBOOK on ( pilot.dbo.PHONEBOOK.PHONE = pilot.db:confused:RDERS.PHONE ) inner join pilot.dbo.DESTCODES on ( pilot.dbo.DESTCODES.DESTCODE = pilot.db:confused:RDERS.DESTCODE ) inner join pilot.dbo.ZONES ZONES7 on ( ZONES7.ZONE = pilot.dbo.XVR_ORDERS.WORKZONE ) inner join pilot.dbo.XVR_SETTLEMENT XVR_SETTLEMENT8 on ( XVR_SETTLEMENT8.SETTLEMENT = pilot.dbo.XVR_ORDERS.WORKSETTLEMENT ) inner join pilot.dbo.DESTCODES DESTCODES5 on ( DESTCODES5.DESTCODE = pilot.dbo.XVR_ORDERS.WORKSITE ) inner join pilot.dbo.WAREHOUSES on ( pilot.dbo.WAREHOUSES.WARHS = pilot.db:confused:RDERS.WARHS ) inner join pilot.dbo.XVR_CONTRACT on ( pilot.dbo.XVR_CONTRACT.CONTRACT = pilot.db:confused:RDERS.XVR_CONTRACT ) inner join pilot.dbo.ADJPRICES on ( pilot.dbo.ADJPRICES.ADJPRICEFLAG = pilot.db:confused:RDERS.ADJPRICEFLAG ) left outer join pilot.dbo.SHIPTO SHIPTO5 on ( SHIPTO5.TYPE = @P46 ) and ( SHIPTO5.IV = pilot.db:confused:RDERS.ORD ) left outer join pilot.dbo.NSCUST on ( pilot.dbo.NSCUST.TYPE = @P47 ) and ( pilot.dbo.NSCUST.IV = pilot.db:confused:RDERS.ORD ) left outer join pilot.db:confused:RDERSCHED on ( pilot.db:confused:RDERSCHED.ORD = pilot.db:confused:RDERS.ORD ) left outer join pilot.db:confused:RDERSA on ( pilot.db:confused:RDERSA.ORD = pilot.db:confused:RDERS.ORD ) left outer join pilot.dbo.TAXES on ( pilot.dbo.TAXES.TAX = coalesce( pilot.db:confused:RDERSA.TAX , 0 ) ) left outer join pilot.dbo.CPROFSTATS on ( pilot.dbo.CPROFSTATS.CPROFSTAT = pilot.dbo.CPROF.CPROFSTAT ) left outer join pilot.dbo.MODELS on ( pilot.dbo.MODELS.MODEL = coalesce( pilot.db:confused:RDERSA.MODEL , 0 ) ) left outer join system.dbo.USERS USERS2 on ( USERS2.T$USER = coalesce( pilot.db:confused:RDERSCHED.DOER2 , 0 ) ) left outer join pilot.dbo.CUSTOMERSA on ( pilot.dbo.CUSTOMERSA.CUST = pilot.dbo.CUSTOMERS.CUST ) left outer join system.dbo.USERS USERS3 on ( USERS3.T$USER = coalesce( pilot.db:confused:RDERSCHED.DOER3 , 0 ) ) left outer join pilot.dbo.CUSTCAMPAIGNS on ( pilot.dbo.CUSTCAMPAIGNS.CC = coalesce( pilot.db:confused:RDERSA.CC , 0 ) ) left outer join pilot.dbo.WAREHOUSES WAREHOUSES1 on ( WAREHOUSES1.WARHS = coalesce( pilot.db:confused:RDERSA.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.db:confused:RDERS.ORDNAME <> @P54 ) and ( pilot.db:confused:RDERS.TYPE <> @P55 ) and ( pilot.db:confused:RDERS.ORDNAME = @P56 )
    order by 3 desc , 4 desc
    ',N'@P1 varchar(1),@P2 char(1),@P3 char(1),@P4 char(1),@P5 char(1),@P6 char(1),@P7 char(1),@P8 varchar(1),@P9 int,@P10 int,@P11 int,@P12 int,@P13 char(1),@P14 char(1),@P15 char(1),@P16 int,@P17 varchar(1),@P18 varchar(1),@P19 varchar(1),@P20 varchar(1),@P21 int,@P22 char(1),@P23 char(1),@P24 char(1),@P25 char(1),@P26 char(1),@P27 varchar(1),@P28 char(1),@P29 char(1),@P30 varchar(1),@P31 varchar(1),@P32 varchar(1),@P33 char(1),@P34 int,@P35 int,@P36 varchar(1),@P37 varchar(1),@P38 int,@P39 varchar(1),@P40 char(1),@P41 char(1),@P42 char(1),@P43 int,@P44 int,@P45 int,@P46 char(1),@P47 char(1),@P48 char(1),@P49 char(1),@P50 char(1),@P51 char(1),@P52 char(1),@P53 char(1),@P54 varchar(1),@P55 char(1),@P56 varchar(10)','','C','Y',' ','C','Y',' ','',0,0,0,0,'Y',' ','Y',0,'','','','',0,'O','O',' ','O','O','','Y',' ','','','',' ',1,0,'','',0,'',' ',' ',' ',1,7,1155,'O','O','Y','Y','Y','Y','Y','Y','','X','A8HT015553'
  2. madhuottapalam New Member

    If this query executes in 7 Min hopefully you are lucky. I counted the joins it is 50+ . And you have almost all the functions SQL offers in Select /Join/Where. I dont think even if you have index the optimizer is going to use that. I wonder how this perform as the data grows.
    Madhu

Share This Page