Performance issues

[Raul2005]

The folloing query return the query I am looking for however the performance is not too goog. Could some one provide some technical advise how to improve performance.

select f. s_name_1,
g.company_name,
g. s_name_1,
k.full_name,
j.p_name,
e.edit_date,
e.status,
l.login,
a.operationDate,
a.value,
h.login

from J_history A,
J_pages B,
J_items C,
systema_tables D,
PCCDES E,
company F,
company G,
P_users H,
region I,
product J,
contact K,
P_users L

where
A.J_pages_id = B.J_pages_id
AND B.J_pages_id = C.J_pages_id
AND A.field = 'TERRA'
AND NOT A.value = 'ACCESS'
AND C.ugly_table = D.tables_id
AND D.table_name = 'PCCDES'
AND C.reference_record = E.PCCDES_id
AND E.dealer_id = F.company_id
AND E.client_id = G.company_id
AND A.rn_create_user = H.users_id
AND I.region_id = F.region_id
AND I.region_name = 'LANDIA'
AND J.product_id = E.product_id
AND E.contact_id = K.contact_id
AND E.edit_user = I.users_id

 

[vongrunt]

Here are your relationships ATM:

A -> B -> C-> D
	   -> E ------> I
	   -> E -> F -> I
	      E -> G
	      E -> J
	      E -> K
A -> H

Five (5) level of joins, all inner ones. ** gulp! **

In table D (systema_tables), how many rows with table_name='PCCDES' you have? Or more generally said, is D.table_name (supposed to be) unique?

Same for table I (region). Is there only one region named 'LANDIA' - and is region_name (supposed to be) unique?

Also: is there any index on column A.field? On foreign (joined) columns involved?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Raul2005]

AND D.table_name = 'PCCDES' yes there is just one table with that name. we have 2 regions.


J_history A,
J_pages B,
J_items C,


B AND HAS FOREING KEY IN A

 

[Raul2005]

D.table_name = 'PCCDES' yes there is just one table with the name PCCDES so it is unique.
I table has 2 regions.


J_history A,
J_pages B,
J_items C,


B AND C HAS FOREING KEY IN A

Thanks

 

[vongrunt]

OK then... Three things to notice:

#1) Table L (P_users) is not joined on anything - this results with cross join.

#2) Tables D and I are not used in SELECT list. Their purpose is obviously filtering via inner join. Since columns I was asking about are (supposed to be) unique, this means used values from D (tables_id) and I (edit_user, region_id) are constant during query's lifetime. Pull 'em out into variables with two small "preparation" queries and then modify query to work without tables D and I.

#3) joins A -> B and B -> C are identical (on pages_id columns). So you can join both on A table (A -> B and A -> C).

After #2 and #3 join schema becomes:

A -> B 
  -> C -> E
       -> E -> F
          E -> G
          E -> J
          E -> K
A -> H

Again: you decide what to do with table L.

Here are proposed changes (code in bold, all variables assumed int):

[b]declare @tables_id int
select @tables_id=D.tables_id
from systema_tables D
where D.table_name = 'PCCDES' 

declare @edit_user int, @region_id int
select @edit_user=I.users_id, @region_id=I.region_id 
from region I
where I.region_name = 'LANDIA'[/b]

select f. s_name_1,
	...
from 	<all tables except D and I>
where
      A.J_pages_id = B.J_pages_id
      AND [b]A[/b].J_pages_id = C.J_pages_id
      AND A.field = 'TERRA'
      AND NOT A.value = 'ACCESS'
      AND C.ugly_table = [b]@tables_id[/b]
      AND C.reference_record = E.PCCDES_id
      AND E.dealer_id = F.company_id
      AND E.client_id = G.company_id
      AND A.rn_create_user = H.users_id
      AND F.region_id= [b]@region_id[/b]
      AND J.product_id = E.product_id
      AND E.contact_id = K.contact_id
      AND E.edit_user = [b]@users_id[/b]

If that doesn't help enough then we'll try index thing - first index candidates are columns A.field and C.ugly_table.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)