maybe u can help with this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

maybe u can help with this query

Author

Topic

avipenina
Starting Member

44 Posts

Posted - 2008-06-24 : 09:27:24

Hi,

I've a program that run those kind of very long query(couldn't attach TXT file).
my problem with this query is that i takes about 35 sec to finish and while it's running it take 25% of CPU.
i run this query in the DETA and set his recommendations but still the same result 35 sec duration and CPU 25% until it's finish.

what can i do to reduce the use of CPU in that query?

my server hardware is Windows 2003 ENT 64BIT,2 XEON 3.0 GHZ,6GB MEM,SQL Server DEV 64BIT.
memory setup - lock pages in memory set,max server memory 4GB.
this is a dedicated SQL Server

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.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
from pilot.dbo.CUSTOMERS 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 1 = 1 inner join pilot.dbo.XVR_ORDERS on 1 = 1 inner join system.dbo.USERSB USERSB8 on ( USERSB8.T$USER = @P45 ) inner join pilot.dbo.CPROF on 1 = 1 inner join pilot.dbo.ORDERS on ( pilot.dbo.ORDERS.ORD = pilot.dbo.XVR_ORDERS.ORD ) inner join pilot.dbo.ORDSTATUS on ( pilot.dbo.ORDSTATUS.ORDSTATUS = pilot.dbo.ORDERS.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.dbo.ORDERS.PROJ ) inner join pilot.dbo.HOWHEARD HOWHEARD6 on ( HOWHEARD6.HOWHEARD = pilot.dbo.XVR_ORDERS.HOWHEARD ) inner join pilot.dbo.XVR_SETTLEMENT on 1 = 1 inner join pilot.dbo.DEAL on ( pilot.dbo.DEAL.DEAL = pilot.dbo.ORDERS.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.dbo.ORDERS.CURRENCY ) inner join pilot.dbo.PAY on ( pilot.dbo.PAY.PAY = pilot.dbo.ORDERS.PAY ) inner join pilot.dbo.CURRENCIES CURRENCIES1 on ( CURRENCIES1.CURRENCY = pilot.dbo.ORDERS.LCURRENCY ) inner join system.dbo.USERS on ( system.dbo.USERS.T$USER = pilot.dbo.ORDERS.DOER ) inner join pilot.dbo.AGENTS on ( pilot.dbo.AGENTS.AGENT = pilot.dbo.ORDERS.AGENT ) inner join pilot.dbo.XVR_ORDERSB on ( pilot.dbo.XVR_ORDERSB.ORD = pilot.dbo.ORDERS.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.dbo.ORDERS.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.dbo.ORDERS.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.dbo.ORDERS ORDERS9 on ( ORDERS9.ORD = pilot.dbo.XVR_ORDERS.LASTORD ) inner join pilot.dbo.CPROFTYPES on ( pilot.dbo.CPROFTYPES.CPROFTYPE = pilot.dbo.ORDERS.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.dbo.ORDERS.PHONE ) inner join pilot.dbo.DESTCODES on ( pilot.dbo.DESTCODES.DESTCODE = pilot.dbo.ORDERS.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.dbo.ORDERS.WARHS ) inner join pilot.dbo.XVR_CONTRACT on ( pilot.dbo.XVR_CONTRACT.CONTRACT = pilot.dbo.ORDERS.XVR_CONTRACT ) inner join pilot.dbo.ADJPRICES on ( pilot.dbo.ADJPRICES.ADJPRICEFLAG = pilot.dbo.ORDERS.ADJPRICEFLAG ) left outer join pilot.dbo.SHIPTO SHIPTO5 on ( SHIPTO5.TYPE = @P46 ) and ( SHIPTO5.IV = pilot.dbo.ORDERS.ORD ) left outer join pilot.dbo.NSCUST on ( pilot.dbo.NSCUST.TYPE = @P47 ) and ( pilot.dbo.NSCUST.IV = pilot.dbo.ORDERS.ORD ) left outer join pilot.dbo.ORDERSCHED on ( pilot.dbo.ORDERSCHED.ORD = pilot.dbo.ORDERS.ORD ) left outer join pilot.dbo.ORDERSA on ( pilot.dbo.ORDERSA.ORD = pilot.dbo.ORDERS.ORD ) left outer join pilot.dbo.TAXES on ( pilot.dbo.TAXES.TAX = coalesce( pilot.dbo.ORDERSA.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.dbo.ORDERSA.MODEL , 0 ) ) left outer join system.dbo.USERS USERS2 on ( USERS2.T$USER = coalesce( pilot.dbo.ORDERSCHED.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.dbo.ORDERSCHED.DOER3 , 0 ) ) left outer join pilot.dbo.CUSTCAMPAIGNS on ( pilot.dbo.CUSTCAMPAIGNS.CC = coalesce( pilot.dbo.ORDERSA.CC , 0 ) ) left outer join pilot.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 ( case when ( ( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) = rtrim(ltrim(reverse( @P50 ))) ) ) then ( pilot.dbo.CUSTOMERS.CUSTDES ) else ( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) ) end = rtrim(ltrim(reverse( @P51 ))) ) and ( coalesce( SHIPTO5.XVR_SETTLEMENT , 0 ) = pilot.dbo.XVR_SETTLEMENT.SETTLEMENT ) and ( ( XVR_USERSB8.PROFFLAG = @P52 ) or ( pilot.dbo.XVR_ACTFIELDS.PROFFLAG <> @P53 ) ) and ( ( XVR_USERSB8.XVR_SALLIMITFLAG = @P54 ) or ( pilot.dbo.CUSTOMERS.XVR_SALLIMITFLAG <> @P55 ) ) and ( pilot.dbo.ORDERS.ORDNAME <> @P56 ) and ( pilot.dbo.ORDERS.TYPE <> @P57 ) and ( pilot.dbo.ORDERS.BRANCH = pilot.dbo.BRANCHES.BRANCH ) and ( pilot.dbo.ORDERS.CUST = pilot.dbo.CUSTOMERS.CUST ) and ( pilot.dbo.ORDERS.PROF = pilot.dbo.CPROF.PROF ) and ( pilot.dbo.CUSTOMERS.CUSTNAME = @P58 )
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 varchar(1),@P51 varchar(11),@P52 char(1),@P53 char(1),@P54 char(1),@P55 char(1),@P56 varchar(1),@P57 char(1),@P58 varchar(7)','','C','Y',' ','C','Y',' ','',0,0,0,0,'Y',' ','Y',0,'','','','',0,'O','O',' ','O','O','','Y',' ','','','',' ',1,0,'','',0,'',' ',' ',' ',1,7,1127,'O','O','Y','Y','','?????? ????','Y','Y','Y','Y','','X','1001431'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-24 : 10:18:45

Wow ! that's one huge huge query.

How many rows does it return ?

FROM 	pilot.dbo.CUSTOMERS  
	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 1 = 1 
	INNER JOIN pilot.dbo.XVR_ORDERS  		ON 1 = 1 
	INNER JOIN system.dbo.USERSB USERSB8 	ON ( USERSB8.T$USER = @P45 ) 
	INNER JOIN pilot.dbo.CPROF  			ON 1 = 1 
	INNER JOIN pilot.dbo.ORDERS  			ON ( pilot.dbo.ORDERS.ORD = pilot.dbo.XVR_ORDERS.ORD ) 
	INNER JOIN pilot.dbo.ORDSTATUS  		ON ( pilot.dbo.ORDSTATUS.ORDSTATUS = pilot.dbo.ORDERS.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.dbo.ORDERS.PROJ ) 
	INNER JOIN pilot.dbo.HOWHEARD HOWHEARD6 	ON ( HOWHEARD6.HOWHEARD = pilot.dbo.XVR_ORDERS.HOWHEARD ) 
	INNER JOIN pilot.dbo.XVR_SETTLEMENT  	ON 1 = 1 
	INNER JOIN pilot.dbo.DEAL  			ON ( pilot.dbo.DEAL.DEAL = pilot.dbo.ORDERS.DEAL )

How many rows are there in table BRANCHES, XVR_ORDERS, CPROF, XVR_SETTLEMENT ? you are cross joining to those tables

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 10:24:13

No need for dynamic sql in the first place.

62 tables in the JOIN of which 12 are left joined.
Two function in two cases.
A case in the WHERE clause.
3 OR's in the WHERE clause.

select		pilot.dbo.CUSTOMERS.CUSTNAME,
		case
			when coalesce(pilot.dbo.NSCUST.CUSTDES, '') = reverse(rtrim(ltrim(@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,
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.QPRICE. 0.0)),
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.XVR_ESTQPRICE, 0.0)),
		case
			when pilot.dbo.XVR_ORDERS.GLOBAL = @P8 then convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.QPRICE, 0.0))
			else convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.XVR_ESTQPRICE, 0.0))
		end,
		convert(decimal(11,2), coalesce(pilot.dbo.ORDERS.T$PERCENT, 0.0)),
		convert(decimal(19,2), coalescE(pilot.dbo.ORDERS.DISPRICE, 0.0)),
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.VAT, 0.0)),
		coalesce(pilot.dbo.TAXES.TAXCODE, '' ),
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.TOTPRICE, 0.0)),
		pilot.dbo.CURRENCIES.CODE,
		convert(decimal(19,2), coalesce(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,
		convert(decimal(9,2), coalesce(pilot.dbo.ORDERS.XVR_TRSREQUANT, 0.0)),
		convert(decimal(9,2), coalesce(pilot.dbo.ORDERS.XVR_TRSTQUANT, 0.0)),
		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,
		convert(decimal(6,2), coalesce(pilot.dbo.XVR_ORDERS.ASSIGNPERCENT, 0.0)),
		convert(decimal(13,2), coalesce(pilot.dbo.XVR_ORDERSB.FIXEDPRICE, 0.0)),
		convert(decimal(9,2), coalesce(pilot.dbo.XVR_ORDERS.HOURWAGE, 0.0)),
		pilot.dbo.XVR_ORDERSB.GLOBALFLAG,
		convert(decimal(12,2), coalesce(pilot.dbo.XVR_ORDERS.GLOBALWAGE, 0.0)),
		case
			when convert(decimal(6,2), pilot.dbo.XVR_ORDERS.ASSIGNPERCENT) <> 0.0 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,
		convert(decimal(9,2), coalesce(pilot.dbo.XVR_ORDERS.MONTHTOT, 0.0)),
		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,
		convert(decimal(11,2), coalesce(pilot.dbo.XVR_ORDERS.FRIDAYTOT, 0.0)),
		convert(decimal(8,2), coalesce(pilot.dbo.XVR_ORDERS.NORMALHOURADD, 0.0)),
		pilot.dbo.XVR_ORDERS.PWEEKSHIFTNUM,
		pilot.dbo.XVR_ORDERS.RIDEADD,
		convert(decimal(11,2), coalesce(pilot.dbo.XVR_ORDERS.SPLITADD, 0.0)),
		pilot.dbo.XVR_ORDERS.HOURSUMMARY,
		convert(decimal(9,2), coalesce(pilot.dbo.XVR_ORDERS.COSTCOEFF, 0.0)),
		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,
		convert(decimal(27,9), coalesce(pilot.dbo.ORDERS.LEXCHANGE, 0.0)),
		coalesce(pilot.dbo.MODELS.MODELNAME, ''),
		coalesce(pilot.dbo.ORDERSA.QUANT, 0),
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.QPROFIT, 0.0)),
		100.0 * convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.QPROFIT, 0.0)) /	case
													when case
														when @P9 = @P10 then convert(decimal(19,2), coalesce(pilot.dbo.ORDERSA.TOTPURCHASEPRICE, 0.0))
														else convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.DISPRICE, 0.0))
													end = 0.0 then 1
													else case
														when @P11 = @P12 then convert(decimal(19,2), coalesce(pilot.dbo.ORDERSA.TOTPURCHASEPRICE, 0.0))
														else convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.DISPRICE, 0.0))
													end
												end,
		CURRENCIES1.CODE,
		convert(decimal(27,9), coalesce(pilot.dbo.ORDERS.LEXCHANGE * CURRENCIES1.EXCHQUANT, 0.0)),
		convert(decimal(19,2), coalesce(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,
		convert(decimal(19,2), coalesce(pilot.dbo.ORDERS.ADVBAL, 0.0)),
		convert(decimal(9,2), coalesce(pilot.dbo.ORDERS.ADVPERCENT, 0.0)),
		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, ''),
		convert(decimal(27,9), coalesce(pilot.dbo.CURRENCIES.EXCHANGE, 0.0)),
		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, 
		ilot.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,
		convert(decimal(11,2), coalesce(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
from		pilot.dbo.CUSTOMERS
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 1 = 1
inner join	pilot.dbo.XVR_ORDERS on 1 = 1
inner join	system.dbo.USERSB AS USERSB8 on USERSB8.T$USER = @P45
inner join	pilot.dbo.CPROF on 1 = 1
inner join	pilot.dbo.ORDERS on pilot.dbo.ORDERS.ORD = pilot.dbo.XVR_ORDERS.ORD
inner join	pilot.dbo.ORDSTATUS on pilot.dbo.ORDSTATUS.ORDSTATUS = pilot.dbo.ORDERS.ORDSTATUS
inner join	system.dbo.XVR_USERSB AS XVR_USERSB8 on XVR_USERSB8.USERB = USERSB8.USERB
inner join	pilot.dbo.DOCUMENTS on pilot.dbo.DOCUMENTS.DOC = pilot.dbo.ORDERS.PROJ
inner join	pilot.dbo.HOWHEARD as HOWHEARD6 on HOWHEARD6.HOWHEARD = pilot.dbo.XVR_ORDERS.HOWHEARD
inner join	pilot.dbo.XVR_SETTLEMENT on 1 = 1
inner join	pilot.dbo.DEAL on pilot.dbo.DEAL.DEAL = pilot.dbo.ORDERS.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.dbo.ORDERS.CURRENCY
inner join	pilot.dbo.PAY on pilot.dbo.PAY.PAY = pilot.dbo.ORDERS.PAY
inner join	pilot.dbo.CURRENCIES as CURRENCIES1 on CURRENCIES1.CURRENCY = pilot.dbo.ORDERS.LCURRENCY
inner join	system.dbo.USERS on system.dbo.USERS.T$USER = pilot.dbo.ORDERS.DOER
inner join	pilot.dbo.AGENTS on pilot.dbo.AGENTS.AGENT = pilot.dbo.ORDERS.AGENT
inner join	pilot.dbo.XVR_ORDERSB on pilot.dbo.XVR_ORDERSB.ORD = pilot.dbo.ORDERS.ORD
inner join	system.dbo.USERS as 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 as 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 as USERSB5 on USERSB5.USERB = pilot.dbo.XVR_ORDERS.RECORDERA
inner join	pilot.dbo.XVR_EVENTSTYPES as XVR_EVENTSTYPES1 on XVR_EVENTSTYPES1.EVENTTYPE = pilot.dbo.XVR_ORDERS.EVENTTYPE
inner join	pilot.dbo.PRICELIST on pilot.dbo.PRICELIST.PLIST = pilot.dbo.ORDERS.PLIST
inner join	pilot.dbo.SHIPTYPES as SHIPTYPES6 on SHIPTYPES6.SHIPTYPE = pilot.dbo.XVR_ORDERS.HEVERSHIPTYPE
inner join	pilot.dbo.SHIPTYPES on pilot.dbo.SHIPTYPES.SHIPTYPE = pilot.dbo.ORDERS.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 as PART5 on PART5.PART = pilot.dbo.XVR_ORDERS.PART
inner join	pilot.dbo.ZONES as ZONES8 on ZONES8.ZONE = pilot.dbo.XVR_ORDERS.ZONE
inner join	pilot.dbo.JOBTYPES as JOBTYPES6 on JOBTYPES6.JOBT = pilot.dbo.XVR_ORDERS.JOBT
inner join	pilot.dbo.ORDERS as ORDERS9 on ORDERS9.ORD = pilot.dbo.XVR_ORDERS.LASTORD
inner join	pilot.dbo.CPROFTYPES on pilot.dbo.CPROFTYPES.CPROFTYPE = pilot.dbo.ORDERS.ORDTYPE
inner join	system.dbo.USERS as USERS5 on USERS5.T$USER = pilot.dbo.XVR_ORDERS.CREATEUSER
inner join	pilot.dbo.CUSTOMERS as CUSTOMERS1 on CUSTOMERS1.CUST = pilot.dbo.CUSTOMERS.PAYCUST
inner join	pilot.dbo.PHONEBOOK on pilot.dbo.PHONEBOOK.PHONE = pilot.dbo.ORDERS.PHONE
inner join	pilot.dbo.DESTCODES on pilot.dbo.DESTCODES.DESTCODE = pilot.dbo.ORDERS.DESTCODE
inner join	pilot.dbo.ZONES as ZONES7 on ZONES7.ZONE = pilot.dbo.XVR_ORDERS.WORKZONE
inner join	pilot.dbo.XVR_SETTLEMENT as XVR_SETTLEMENT8 on XVR_SETTLEMENT8.SETTLEMENT = pilot.dbo.XVR_ORDERS.WORKSETTLEMENT
inner join	pilot.dbo.DESTCODES as DESTCODES5 on DESTCODES5.DESTCODE = pilot.dbo.XVR_ORDERS.WORKSITE
inner join	pilot.dbo.WAREHOUSES on pilot.dbo.WAREHOUSES.WARHS = pilot.dbo.ORDERS.WARHS
inner join	pilot.dbo.XVR_CONTRACT on pilot.dbo.XVR_CONTRACT.CONTRACT = pilot.dbo.ORDERS.XVR_CONTRACT
inner join	pilot.dbo.ADJPRICES on pilot.dbo.ADJPRICES.ADJPRICEFLAG = pilot.dbo.ORDERS.ADJPRICEFLAG
left join	pilot.dbo.SHIPTO as SHIPTO5 on SHIPTO5.TYPE = @P46
			and SHIPTO5.IV = pilot.dbo.ORDERS.ORD
left join	pilot.dbo.NSCUST on pilot.dbo.NSCUST.TYPE = @P47
			and pilot.dbo.NSCUST.IV = pilot.dbo.ORDERS.ORD
left join	pilot.dbo.ORDERSCHED on pilot.dbo.ORDERSCHED.ORD = pilot.dbo.ORDERS.ORD
left join	pilot.dbo.ORDERSA on pilot.dbo.ORDERSA.ORD = pilot.dbo.ORDERS.ORD
left join	pilot.dbo.TAXES on pilot.dbo.TAXES.TAX = coalesce(pilot.dbo.ORDERSA.TAX, 0)
left join	pilot.dbo.CPROFSTATS on pilot.dbo.CPROFSTATS.CPROFSTAT = pilot.dbo.CPROF.CPROFSTAT
left join	pilot.dbo.MODELS on pilot.dbo.MODELS.MODEL = coalesce(pilot.dbo.ORDERSA.MODEL, 0)
left join	system.dbo.USERS as USERS2 on USERS2.T$USER = coalesce(pilot.dbo.ORDERSCHED.DOER2, 0)
left join	pilot.dbo.CUSTOMERSA on pilot.dbo.CUSTOMERSA.CUST = pilot.dbo.CUSTOMERS.CUST
left join	system.dbo.USERS as USERS3 on USERS3.T$USER = coalesce(pilot.dbo.ORDERSCHED.DOER3, 0)
left join	pilot.dbo.CUSTCAMPAIGNS on pilot.dbo.CUSTCAMPAIGNS.CC = coalesce(pilot.dbo.ORDERSA.CC, 0)
left join	pilot.dbo.WAREHOUSES as 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 case
			when coalesce(pilot.dbo.NSCUST.CUSTDES, '') = reverse(rtrim(ltrim(@P50))) then pilot.dbo.CUSTOMERS.CUSTDES
			else coalesce(pilot.dbo.NSCUST.CUSTDES, '')
		end = rtrim(ltrim(reverse(@P51))) 
		and coalesce( SHIPTO5.XVR_SETTLEMENT, 0) = pilot.dbo.XVR_SETTLEMENT.SETTLEMENT
		and (XVR_USERSB8.PROFFLAG = @P52 or pilot.dbo.XVR_ACTFIELDS.PROFFLAG <> @P53) 
		and (XVR_USERSB8.XVR_SALLIMITFLAG = @P54 or pilot.dbo.CUSTOMERS.XVR_SALLIMITFLAG <> @P55)
		and pilot.dbo.ORDERS.ORDNAME <> @P56
		and pilot.dbo.ORDERS.TYPE <> @P57
		and pilot.dbo.ORDERS.BRANCH = pilot.dbo.BRANCHES.BRANCH
		and pilot.dbo.ORDERS.CUST = pilot.dbo.CUSTOMERS.CUST
		and pilot.dbo.ORDERS.PROF = pilot.dbo.CPROF.PROF
		and pilot.dbo.CUSTOMERS.CUSTNAME = @P58
order by	pilot.dbo.ORDERS.CURDATE desc,
		pilot.dbo.ORDERS.ORDNAME desc

E 12°55'05.25"
N 56°04'39.16"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-24 : 11:45:08

Peso! are you human???

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 13:02:32

quote:
Originally posted by jezemine

Peso! are you human???

elsasoft.org

Extraordinary

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-24 : 13:09:13

I'm surprised it completes in 35 seconds. I would have thought this would be measured in minutes.

I'm gonna remove the code tags from the first post since it's not formatted anyway, might as well just wrap the text so we don't have to scroll to the right.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

avipenina
Starting Member

44 Posts

Posted - 2008-06-24 : 14:54:34

i'm sry for the mistake but this query run for almost 30MIN with CPU 30% until it's finish.
when i try to run the query that Peso fix i get an error
Msg 156, Level 15, State 1, Line 97
Incorrect syntax near the keyword 'convert'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-24 : 16:12:31

I haven't fixed anything. I have just written the query for easier reading.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 19:16:38