maybe u can help with this query

Question

maybe u can help with this query

Mad-Dog

SSCertifiable

Points: 6117
More actions
June 23, 2008 at 2:46 pm

#189481

Hi,
I've a program that run those kind of very long query(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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

This:

...
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 ...

is just sad! ... 62 tables/views ?

* Noel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I'm not sure if there is much more that could be done to improve your query. As I was trying to reformat it into a more readable form I saw inner joins where the ON clause was 1 = 1, coalesce functions in the where clause (that could easily cause table scans). Unfortunately, after a bit, I just gave up trying to reformat the code.

My first suggestion from what I saw, make better use of aliases in your FROM clause for your tables and change the four part naming in your select list to two part; tablealais.columnname instead of the databasename.schema.table.columnname you are currently using.

Second, I'd rewrite your query outside of a dynamic string and debug it that way. Reformat the code using proper indenting of the code so that is easier to tell what goes with what, especially with nested conditions in the WHERE clause.

Also, it would help if you also provided information regarding the tables involved, tble DDL, indexes, etc. Also helpful would be some sample data, in the form of insert statements so people trying to help can cut, paste, and run without having to format it first, and what the expected output is based on the sample data.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

noeld (6/23/2008)
This:
...
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 ...
is just sad! ... 62 tables/views ?

And if you look carefully you will find a couple or so that are in there more than once.

😎

stephen.christie SSC Eights! Points: 895 More actions · Answer 4

WOW, This code as i see someone else complain, way to complicated to try and reformat. But that aside, few issues, 1=1 on an inner join, well actually in any Statement is a NO NO. Then i see you are using Functions and case statements in the where clause, this will slow down your query. Rather use Functions on the other side of the = sign.

You also have the following in the WHERE clause

and ( pilot.dbo.ORDERS.CUST = pilot.dbo.CUSTOMERS.CUST )

and ( pilot.dbo.ORDERS.PROF = pilot.dbo.CPROF.PROF )

This should be used in the ON clause.

Next problem

( pilot.dbo.NSCUST.TYPE = @P47 )

U are using this in your ON clauses. Rather move this down to the Where clause and use the Key joins in the ON.

Without table layout and indexes, views ect explained more it is hard to understand a query like this.

Hope this helps

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 5

Only 62 tables. We had one that was 85 and we got it to run in under 2 seconds (of course, it took 12 minutes to recompile).

But seriously, everyone else has started to lay out where your issues lay. I'd strongly suggest you get an execution plan and start working through eliminating table/cluster scans and other high cost operations. The execution plan is going to be fairly messy. This might help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

noeld SSC Guru Points: 96590 More actions · Answer 6

I have seen these things in "generated" queries and they require pretty much a serious re-write or simply just let it happen at "off" hours. If this is the case re-writing the query (and verifying index usage) following the above suggestions is probably the only way this "thing" could work.

* Noel