0

The system I maintain seems to slow down quite a bit every few days, and I assume it's from a bad query somewhere.

From what I can tell, I've narrowed the issue down to a page or two. Here's the query on the page that I think is causing the issue.

select a.s_purchase_order as order_id, a.order_type, a.nobackorder, a.order_note, a.note, a.rqst_dlvry_date, b.customer_name ,c.store_name,(c.store_name + ',' + isnull(c.address1 + ',', ' ') +  isnull(c.city + ',', ' ') +  isnull(c.state_cd+ ',', ' ')  + isnull( c.zipcode, ' ')) as store_info, d.supplier_account
from VW_CustomerOrder a, Customer b, Store c, eligible_supplier d
where a.customer = c.customer
and a.store = c.store
and a.customer = b.customer
and c.customer *= d.customer
and c.store *= d.store
and a.supplier *= d.supplier
and a.purchase_order = @order_id
and a.customer = @customer_id
and a.store=@store_id
and a.supplier = @supplier_id

Is there something obvious there that would be very slow or cause the system to slow over time?

Cœur
  • 37,241
  • 25
  • 195
  • 267
fullOfQuestions
  • 453
  • 1
  • 11
  • 25
  • Do your joins in the From clause (like the answer below) not in the where clause, it improves readability. Also, what DBMS are you using? MySQL? SQL Server? other? – Ghost Aug 02 '12 at 17:23
  • @Yaroslav Don't impose your own syntax preferences on others. Your uppercase SQL keywords are no more correct than lowercase keywords, and they look (in my opinion) hideous. – user229044 Aug 02 '12 at 18:53
  • Thanks guys, this is a good idea, but I'm getting an error now. Thanks for all your help – fullOfQuestions Aug 02 '12 at 19:40
  • Sir yes sir! ...the hideuos, in my opinion, was unnecessary http://blog.stackoverflow.com/2012/07/kicking-off-the-summer-of-love/ For me is easier to differentiate keywords from columns, tables, etc, I'm aware of the colours, but in my opinion is more readable...but yes, I should not impose my preference... nevertheless: http://stackoverflow.com/questions/608196/why-should-i-capitalize-my-sql-keywords http://stackoverflow.com/questions/292026/is-there-a-good-reason-to-use-upper-case-for-t-sql-keywords And last but not least, I think that at least the identation should be kept – Yaroslav Aug 02 '12 at 20:21

1 Answers1

1

what about do some inner joins to solve this, check your base and see index and foreign keys for those table, this always is helpful in querys and performance 

select 
  a.columun_a, b.column_a from table_a a 
inner join table_b b on a.id = b.id
where 
  b.column_b = "some value"
Shirow
  • 70
  • 9
  • This seems like a great idea, but when I add them I get this error: `1016, Level 15, State 3, Procedure sp_s_getCustOrderSummary, Line 12 Outer join operators cannot be specified in a query containing joined tables.` – fullOfQuestions Aug 02 '12 at 19:38
  • I changed it to `from VW_CustomerOrder a inner join Customer b on a.customer = b.customer inner join Store c on a.customer = c.customer inner join eligible_supplier d on a.customer = d.customer` – fullOfQuestions Aug 02 '12 at 19:39