List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 10 1999 5:02pm
Subject:Re: big (access: < 1 min, mysql > 2 h) performance problem..
View as plain text  
Jules Bean wrote:
> 
> 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.
> 
> >
> > SELECT
> > 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
> see.
> 
> > 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.
> 
> Jules

Hi Jules

AFAIK you can't reuse result column labels in other result columns or in the WHERE part.
So you have to replace these with the original definition.
As an exaple I did this for Input and Output in Transfer.

I don't know what Nz does :(

You should use something like:
SELECT
	knd.K_Name1
	, knd.K_Name2
	, c.User AS Ausdr1
	, pos.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
	, SUM( [acct_input_octets] * 0.0009765625 * 0.0009765625 )
		+ SUM( [acct_output_octets] * 0.0009765625 * 0.0009765625 ) AS Transfer
	, IF( pos.AuP_M_Id = 0 OR pos.AuP_M_Id = 2
		, [OnlineZeit]-180
		, IF( pos.AuP_M_Id = 3
			,[OnlineZeit]-1200
			, NULL
			)
		) AS Zeitueberschreitung
	, IF( pos.AuP_M_Id = 0 AND [Zeitueberschreitung]>0
		, [Zeitueberschreitung]*0.07
		, IF( [Zeitueberschreitung]>0
			, [Zeitueberschreitung]*0.0812
			, NULL
			)
		) AS Kosten_Zeit
	, IF( (pos.AuP_M_Id = 2 OR pos.AuP_M_Id = 3 OR pos.AuP_M_Id = 4)
		AND ([Transfer] - 200)>0
		, [Transfer]-200
		, IF( pos.AuP_M_Id = 5 And ([Transfer]-1024) > 0
			, [Transfer]-1024
			, NULL
			)
		) AS TransUeber
	, IF( [TransUeber] > 0, [TransUeber] * 0.928, NULL) AS KostenTransfer
	, Nz([Kosten_Zeit]) + Nz([KostenTransfer]) AS Summe_DM 
FROM 
	Calls AS c
	, dbo_Kunden AS knd
	, dbo_Auftrag AS auf
	, dbo_AuftragPos AS pos
WHERE 
	knd.K_Id = auf.Au_K_Id
	AND auf.Au_Id = pos.AuP_Id
	AND c.User = knd.K_MATCH
	AND auf.Au_StartDatum <= c.Start
	AND auf.Au_StopDatum >= c.Stop
	AND ( pos.AuP_M_Id = 0 
		OR pos.AuP_M_Id = 2 
		OR pos.AuP_M_Id = 3 
		OR pos.AuP_M_Id = 4 
		OR pos.AuP_M_Id = 5 
		)
GROUP BY 
	knd.K_Name1
	, knd.K_Name2
	, c.User
	, pos.AuP_M_Id
ORDER BY 
	pos.AuP_M_Id
	, knd.K_Name1
	;

With this query you need the following keys (if possible):
Calls     ->      (User, Start, Stop)
dbo_Kunden   ->   (K_ID, K_MATCH)
dbo_Auftrag   ->  (Au_K_Id, Au_Id, Au_StartDatum, Au_StopDatum)
dbo_AuftragPos -> (AuP_M_Id)

Tschau
Christian

Thread
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