Left Join select stmt Too slow performance HELP!!!

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 2000 Forums

SQL Server Development (2000)

Left Join select stmt Too slow performance HELP!!!

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-29 : 09:18:16

Hello,
I am trying to modify the Access query into sql view, but it return about 10 records for 24 seconds. Can you please take a look at my view below to see how I need to modify this to make it faster?
I use LEFT JOINs for a few VIEWs and Tables:

SELECT Sum.Cont_id,
RDate.EDate,
Sum_inc.To_Inc AS IncurredTotal,
Sum_Base.Exp,
Sum_Fore.Sum_For_Exp,
Sum_Base.SumOfExp AS Plan,
Sum_Act.SumOfTotal AS Act,
Sum_C_Val.ToCont,
Sum_C_Val.Percent
FROM (((Sum_Base RIGHT JOIN ((Sum LEFT JOIN RDate ON Sum.RDate_ID = RDate.RDate_ID)
LEFT JOIN Sum_Act ON ( Sum.Cont_id = Sum_Act.Cont_id and
Sum.RDate_ID = Sum_Act.RDate_ID))
ON (Sum_Base.Plan_Cont_id = Sum.Cont_id and
Sum_Base.RDate_ID = Sum.RDate_ID ))
LEFT JOIN Sum_Fore ON (Sum.Cont_id = Sum_Fore.Plan_Cont_id and
Sum.RDate_ID = Sum_Fore.RDate_ID))
LEFT JOIN Sum_C_Val ON (Sum.Cont_id = Sum_C_Val.Cont_id and
Sum.RDate_ID = Sum_C_Val.RDate_ID))
LEFT JOIN Sum_inc ON (Sum.Cont_id = Sum_inc.Cont_id and
Sum.RDate_ID = Sum_inc.RDate_ID)

Your help is appricated.
Thanks,
Jenny.

There is no stupid question.
www.single123.com

JasonGoff
Posting Yak Master

158 Posts

Posted - 2005-12-29 : 09:49:14

Jenny,

Can you post the table structures and some sample data so that we can understand your query in context.

Thanks

Jason

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-29 : 10:16:46

Here is two rows of data return:
55 2004-05-31 00:00:00.000 28257.995658 85442.2900
55 2004-05-31 00:00:00.000 67257.995658 35442.2900

Here is 2 related views, the sum_average view is the one TG(on this forum) show me, but it runs fast. Only the one I posted runs too slow:

/* sum_average */
SELECT Project.Cont_id, ref_date.ref_date_id,
inc.inc_from,inc.inc_to,
(IsNull(inc.la_money_inc,0)+
IsNull(inc.tra_money_inc,0)+
IsNull(inc.ODC_s_inc,0))/(nullif(datediff(day,inc.inc_from,inc.inc_to),0) + 1) as average,
(((datediff (day, IsNull(inc.inc_from,0),IsNull(inc.inc_to,0))-
abs(datediff (day, IsNull(inc.inc_from,0), IsNull(ref_date.ref_beg_date,0))))+
(datediff (day, IsNull(ref_date.ref_beg_date,0), IsNull(ref_date.ref_end_date,0))-
abs(datediff (day, IsNull(inc.inc_to,0), IsNull(ref_date.ref_end_date,0)))))/2)+1 AS tdays

/* Sum_inc */
SELECT sum_ave.cont_id,
sum_ave.ref_date_id,
sum(average*tdays) AS to_inc
FROM sum_ave
GROUP BY sum_ave.cont_id,
sum_ave.ref_date_id

Kristen
Test

22859 Posts

Posted - 2005-12-29 : 14:01:23

Have you got indexes, or Primary keys, on all the columns involved in the JOINs?

Without appropriate indexes JOINs, particularly on large tables, can be pretty slow.

I hate all that NESTING of JOINs that Access does with the syntax it generates ...

Kristen

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-30 : 09:15:57

yes, I did.
So, there is no way to make it faster.
Thanks,
Jenny.

There is no stupid question.
www.single123.com

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-30 : 09:30:22

>>So, there is no way to make it faster.

Are you saying that if Dr. Sql M. Server himself and a team of his brightest people all got together and dedicated themselves to improving the performance of your convoluted nested select statement on views of views, they wouldn't even be able eek another milisecond off the execution time?!?

If Google had that attitude, we'd all still think "google" was just a euphemism for some self gratifying sexual act.

EDIT:
Ok, now I feel guilty for wise-cracking without offering any help. But I honestly don't know how to read the FROM clause above. Can anyone translate that into a non-nested series of JOINs for me?
Be One with the Optimizer
TG

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-12-30 : 11:34:56

quote:
Can anyone translate that into a non-nested series of JOINs for me?

Yeah, you just work from the middle out, flipping any RIGHT joins into LEFT joins to avoid ambiguities. As if by magic, it turns out to simply be:


SELECT Sum.Cont_id, RDate.EDate, Sum_inc.To_Inc AS IncurredTotal, Sum_Base.Exp,
  Sum_Fore.Sum_For_Exp, Sum_Base.SumOfExp AS Plan, Sum_Act.SumOfTotal AS Act, 
  Sum_C_Val.ToCont, Sum_C_Val.Percent
FROM Sum
LEFT JOIN RDate
  ON Sum.RDate_ID = RDate.RDate_ID
LEFT JOIN Sum_Act
  ON Sum.Cont_id = Sum_Act.Cont_id and Sum.RDate_ID = Sum_Act.RDate_ID
LEFT JOIN Sum_Base
  ON Sum.Cont_id = Sum_Base.Plan_Cont_id and Sum.RDate_ID = Sum_Base.RDate_ID
LEFT JOIN Sum_Fore
  ON Sum.Cont_id = Sum_Fore.Plan_Cont_id and Sum.RDate_ID = Sum_Fore.RDate_ID
LEFT JOIN Sum_C_Val
  ON Sum.Cont_id = Sum_C_Val.Cont_id and Sum.RDate_ID = Sum_C_Val.RDate_ID
LEFT JOIN Sum_inc
  ON Sum.Cont_id = Sum_inc.Cont_id and Sum.RDate_ID = Sum_inc.RDate_ID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-30 : 12:45:29

Thanks for saving me the work, Arnold. One of these days I am going to write a "RIGHT OUTER JOIN" removal parser that does this for any SELECT, which also removes those silly Access "nested joins".

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-12-30 : 12:53:13

I'm sure some criteria might help speed things up ... how many rows does this return? Are each of those "sum_" objects Views? You need to troubleshoot and optimize each of those first.

Kristen
Test

22859 Posts

Posted - 2005-12-30 : 13:54:14

Can you post a query plan pls - that might shed some light on how hard the query has to work, and whether its likely it can be speeded up.

Output from the STATICSTICS thingie would help too - too see how many logical SCANs and READs are involved

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources