List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:September 6 1999 4:05pm
Subject:Re: big (access: < 1 min, mysql > 2 h) performance problem..
View as plain text  
Bernhard Thoni wrote:
> first of all: thanx for helping

It's best to keep the list in the Cc: of the mails - others will know
more than me about many things!

> ok: iif seems to be a special kind of if (i know this from visual
> basic). here comes the statement i have to port that it works under
> linux with mysql client:

OK.  I think IIF is a red herring - it's nothing to do with JOINs.  It
seems to have similar semantics to the mysql function IF(), so I expect
you can replace it with them.

> dbo_Kunden.K_Name1, dbo_Kunden.K_Name2,
> Calls.User AS Ausdr1, dbo_AuftragPos.AuP_M_Id,
> Sum([acct_session_time]/60) AS OnlineZeit,
> Sum([acct_input_octets]*0.0009765625*0.0009765625) AS Input,
> Sum([acct_output_octets]*0.0009765625*0.0009765625) AS Output,
> [Input]+[Output] AS Transfer,
> IIf([AuP_M_Id] Like 0 Or [AuP_M_Id] Like
> 2,[OnlineZeit]-180,IIf([AuP_M_Id] Like 3,[OnlineZeit]-1200)) AS
> Zeitueberschreitung,
> IIf([AuP_M_Id] Like 0 And
> [Zeitueberschreitung]>0,[Zeitueberschreitung]*0.07,IIf([Zeitueberschreitung]>0,[Zeitueberschreitung]*0.0812))
> AS Kosten_Zeit,
> IIf(([AuP_M_Id] Like 2 Or [AuP_M_Id] Like 3 Or [AuP_M_Id] Like 4) And
> ([Transfer]-200)>0,[Transfer]-200, IIf(([AuP_M_Id] Like 5) And
> ([Transfer]-1024)>0,[Transfer]-1024)) AS TransUeber,
> IIf([TransUeber]>0,[TransUeber]*0.928) AS KostenTransfer,
> Nz([Kosten_Zeit])+Nz([KostenTransfer]) AS Summe_DM

This is the JOINs bit here.

This bit should work absolutely fine.

> FROM Calls INNER JOIN ((dbo_Kunden INNER JOIN dbo_Auftrag ON
> dbo_Kunden.K_Id = dbo_Auftrag.Au_K_Id)
> INNER JOIN dbo_AuftragPos ON dbo_Auftrag.Au_Id = dbo_AuftragPos.AuP_Id)
> ON Calls.User = dbo_Kunden.K_MATCH
> WHERE (((dbo_Auftrag.Au_StartDatum)<=[Calls]![Start]) AND
> ((dbo_Auftrag.Au_StopDatum)>=[Calls]![Stop]))
> GROUP BY dbo_Kunden.K_Name1, dbo_Kunden.K_Name2, Calls.User,
> dbo_AuftragPos.AuP_M_Id
> HAVING (((dbo_AuftragPos.AuP_M_Id)=0 Or (dbo_AuftragPos.AuP_M_Id)=2 Or
> (dbo_AuftragPos.AuP_M_Id)=3 Or (dbo_AuftragPos.AuP_M_Id)=4 Or
> (dbo_AuftragPos.AuP_M_Id)=5))
> ORDER BY dbo_AuftragPos.AuP_M_Id, dbo_Kunden.K_Name1
> ;

> i am not sure how to do the iif block in mysql-environment (i read that
> there is no "select .. from ... where (select ...)";

There is none, you are correct.  However, I wouldn't have thought you
needed one - those are row-expressions (scalar expressions evaluated in
the context of each row) not relational expressions, as far as I can

> what do you think, is it normal to take 2 hours for such a statement ?
> we have about 80000 lines in table Calls, in the others very few

Definitely not.  You probably don't have enough indexes in place.

The mysql team will want to see the output of EXPLAIN SELECT ... so they
can tell you why the indexing isn't working.

big (access: < 1 min, mysql > 2 h) performance problem..Bernhard Thoni6 Sep
  • Re: big (access: < 1 min, mysql > 2 h) performance problem..Jules Bean6 Sep
  • Re: big (access: < 1 min, mysql > 2 h) performance problem..Jules Bean6 Sep
  • Re: big (access: < 1 min, mysql > 2 h) performance problem..Christian Mack10 Sep