11 replies to this topic

[bleastan]

I got the following query atm:

SELECT v.datum,  ROUND((v.temp + 0.8 * g.temp + 4 * eg.temp + 2 * eeg.temp) / 2.4,1) AS uitkomst

		FROM (

		SELECT datum, (MAX(t_s/10 + MIN(t_s)/10) / 2 AS temp

FROM tbl_av GROUP BY datum )

		AS v

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp

FROM tbl_av GROUP BY datum ) AS g on g.datum = (v.datum - INTERVAL 1 DAY)

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp

FROM tbl_av GROUP BY datum ) AS eg on eg.datum = (v.datum - INTERVAL 2 DAY)

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp

FROM tbl_av GROUP BY datum ) AS eeg on eeg.datum = (v.datum - INTERVAL 3 DAY)

		WHERE v.datum

         BETWEEN 19970101 AND 19980101;

now the problem lies in the fact that it takes a long time ot execute (in this example its only between 1997-01-01 AND 1998-01-01) but it can be for more years. Now i think the solution to improving this is to add a WHERE clause so it doesnt get ALL the table data all the time but just the one i need so i tried doing this:

SELECT v.datum,  ROUND((v.temp + 0.8 * g.temp + 4 * eg.temp + 2 * eeg.temp) / 2.4,1) AS uitkomst

		FROM (

		SELECT datum, (MAX(t_s/10 + MIN(t_s)/10) / 2 AS temp

FROM tbl_av GROUP BY datum )

		AS v

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 1 DAY)

FROM tbl_av GROUP BY datum ) AS g on g.datum = (v.datum - INTERVAL 1 DAY)

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 2 DAY)

FROM tbl_av GROUP BY datum ) AS eg on eg.datum = (v.datum - INTERVAL 2 DAY)

		JOIN (

		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 3 DAY)

FROM tbl_av GROUP BY datum ) AS eeg on eeg.datum = (v.datum - INTERVAL 3 DAY)

		WHERE v.datum

         BETWEEN 19970101 AND 19980101

However in the WHERE clause it cant find the column v.datum (datum = date btw.. its just that its in dutch :P).

So now my question is there anyone that can help me improve the query in terms of performance? Thx in advance.

[wim DC]

Any chance that this does something?

SELECT 

	v.datum,  

	ROUND(((MAX(v.t_s/10 + MIN(v.t_s)/10) / 2 + 0.8 * (MAX(g.t_s)/10 + MIN(g.t_s)/10) / 2 + 4 * (MAX(eg.t_s)/10 + MIN(eg.t_s)/10) / 2 + 2 * (MAX(eeg.t_s)/10 + MIN(eeg.t_s)/10) / 2) / 2.4,1) AS uitkomst

FROM tbl_av v, tbl_av g, tbl_av eg, tbl_av eeg

WHERE g.datum = (v.datum - INTERVAL 1 DAY) AND 

	  eg.datum = (v.datum - INTERVAL 2 DAY) AND

	  eeg.datum = (v.datum - INTERVAL 3 DAY)			

WHERE v.datum BETWEEN 19970101 AND 19980101

GROUP BY v.datum

[bleastan]

Ill give that a go when i get back to the computer with the DB on it :)

[bleastan]

I just tried it out but unfortunately i get this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tbl_av v, tbl_av g, tbl_av eg, tbl_av e' at line 4

[wim DC]

Would it be possible to put the create/insert script of the tbl_av table? or does it contain secret info? ;)
If you use mysql workbench:

• at the start screen, click manage import export to the right, and select the appropriate server
  
• Click the 'data dump' tab
  
• Click the correct database schema
  
• choose export to self-contained file
  
• start export
  
• upload or paste the content of the file here

[bleastan]

oxano said:

Would it be possible to put the create/insert script of the tbl_av table? or does it contain secret info? ;)
If you use mysql workbench:

• at the start screen, click manage import export to the right, and select the appropriate server
  
• Click the 'data dump' tab
  
• Click the correct database schema
  
• choose export to self-contained file
  
• start export
  
• upload or paste the content of the file here

I assume you mean this (it comes from phpmyadmin)?

CREATE TABLE IF NOT EXISTS `tbl_av` (
`datum` date NOT NULL,
`uur` time NOT NULL,
`t_s` int(4) DEFAULT NULL,
`vers` int(4) DEFAULT NULL,
`versad` int(2) DEFAULT NULL,
PRIMARY KEY (`datum`,`uur`)
)

[wim DC]

Attempt #2:
This does not give syntax errors with the table from your create script. I'm just hoping it contains the correct data upon executing...

SELECT v.datum, 

 ROUND(

  (

   (

    (MAX(v.t_s)/10 + MIN(v.t_s)/10) / 2 + 0.8 * (MAX(g.t_s)/10 + MIN(g.t_s)/10) / 2 + 4 * (MAX(eg.t_s)/10 + MIN(eg.t_s)/10) / 2 + 2 * (MAX(eeg.t_s)/10 + MIN(eeg.t_s)/10) / 2

   ) / 2.4

  ),1

) AS uitkomst

FROM tbl_av v, tbl_av g, tbl_av eg, tbl_av eeg

WHERE v.datum BETWEEN 19970101 AND 19980101 AND

      g.datum = (v.datum - INTERVAL 1 DAY) AND 

	    eg.datum = (v.datum - INTERVAL 2 DAY) AND

	    eeg.datum = (v.datum - INTERVAL 3 DAY) 


Group by v.datum

    

[bleastan]

oxano said:

Attempt #2:
This does not give syntax errors with the table from your create script. I'm just hoping it contains the correct data upon executing...

SELECT v.datum, 
 ROUND(
  (
   (
    (MAX(v.t_s)/10 + MIN(v.t_s)/10) / 2 + 0.8 * (MAX(g.t_s)/10 + MIN(g.t_s)/10) / 2 + 4 * (MAX(eg.t_s)/10 + MIN(eg.t_s)/10) / 2 + 2 * (MAX(eeg.t_s)/10 + MIN(eeg.t_s)/10) / 2
   ) / 2.4
  ),1
) AS uitkomst
FROM tbl_av v, tbl_av g, tbl_av eg, tbl_av eeg
WHERE v.datum BETWEEN 19970101 AND 19980101 AND
      g.datum = (v.datum - INTERVAL 1 DAY) AND 
	    eg.datum = (v.datum - INTERVAL 2 DAY) AND
	    eeg.datum = (v.datum - INTERVAL 3 DAY) 

Group by v.datum
    

Ive just tried it out but my entire Database crashes on execution. Tried several restarts with the same results. And i think its because it uses the 4 seperate tables.. (considering tbl_av can contain many many rows).

So this one doesnt really improve the performance :P. Mayb u have any other suggestions?

ATM im trying to build on this idea:

SELECT v.datum,  ROUND((v.temp + 0.8 * g.temp + 4 * eg.temp + 2 * eeg.temp) / 2.4,1) AS uitkomst
		FROM (
		SELECT datum, (MAX(t_s/10 + MIN(t_s)/10) / 2 AS temp
FROM tbl_av GROUP BY datum )
		AS v
		JOIN (
		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 1 DAY)
FROM tbl_av GROUP BY datum ) AS g on g.datum = (v.datum - INTERVAL 1 DAY)
		JOIN (
		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 2 DAY)
FROM tbl_av GROUP BY datum ) AS eg on eg.datum = (v.datum - INTERVAL 2 DAY)
		JOIN (
		SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 3 DAY)
FROM tbl_av GROUP BY datum ) AS eeg on eeg.datum = (v.datum - INTERVAL 3 DAY)
		WHERE v.datum
         BETWEEN 19970101 AND 19980101

I just need to find a solution for these parts:
SELECT datum, (MAX(t_s)/10 + MIN(t_s)/10) / 2 AS temp WHERE datum = (v.datum - INTERVAL 2 DAY)
considering it cant find v.datum

However ill take any suggestion you have, thx so far already though :)

[wim DC]

Do i get it right if
v.datum = 11/11/2010
And : g.datum = (v.datum - INTERVAL 1 DAY)
then g.datum must be 11/11/2010 OR 10/11/2010
And : eg.datum = (v.datum - INTERVAL 2 DAY)
then eg.datum must be 11/11/2010 OR 10/11/2010 OR 09/11/2010
?
Or is it simply
g.datum must be 10/11/2010
eg.datum must be 09/11/2010


Either way, i inserted 5k records into the table and it could only finish the query with 2 tables, 3 and 4 took too long for my liking and i interupted it.
Then i replaced the query by:

select s.datum, 

(

    (select round(      (MAX(v.t_s) + MIN(v.t_s))/48,1) from tbl_av v WHERE v.datum=s.datum group by v.datum)

    + 

    (select round(0.8 * (MAX(g.t_s) + MIN(g.t_s))/48,1) from tbl_av g WHERE g.datum=(s.datum - interval 1 DAY)  group by g.datum)    

    + 

    (select round(4 * (MAX(eg.t_s) + MIN(eg.t_s))/48,1) from tbl_av eg WHERE eg.datum=(s.datum - interval 2 DAY) group by eg.datum)

    + 

    (select round(2 * (MAX(eeg.t_s) + MIN(eeg.t_s))/48,1) from tbl_av eeg WHERE eeg.datum=(s.datum - interval 3 DAY) group by eeg.datum)

)as uitkomst

from tbl_av s

WHERE s.datum BETWEEN 20100101 AND 20110101

GROUP BY s.datum

which takes 0,015seconds... but that's surprisingly quick...
(you need to change the between values in the where-clause)
What're the results like at your side?

[bleastan]

Thanks for reply. Ill test it out when i get back to the computer with the DB on it..which is unfortunately monday. Anyways ill update the post on monday so i hope the topic doesnt go to waste. Thanks for help so far.

[bleastan]

oxano said:

Do i get it right if
v.datum = 11/11/2010
And : g.datum = (v.datum - INTERVAL 1 DAY)
then g.datum must be 11/11/2010 OR 10/11/2010
And : eg.datum = (v.datum - INTERVAL 2 DAY)
then eg.datum must be 11/11/2010 OR 10/11/2010 OR 09/11/2010
?
Or is it simply
g.datum must be 10/11/2010
eg.datum must be 09/11/2010


Either way, i inserted 5k records into the table and it could only finish the query with 2 tables, 3 and 4 took too long for my liking and i interupted it.
Then i replaced the query by:

select s.datum, 
(
    (select round(      (MAX(v.t_s) + MIN(v.t_s))/48,1) from tbl_av v WHERE v.datum=s.datum group by v.datum)
    + 
    (select round(0.8 * (MAX(g.t_s) + MIN(g.t_s))/48,1) from tbl_av g WHERE g.datum=(s.datum - interval 1 DAY)  group by g.datum)    
    + 
    (select round(4 * (MAX(eg.t_s) + MIN(eg.t_s))/48,1) from tbl_av eg WHERE eg.datum=(s.datum - interval 2 DAY) group by eg.datum)
    + 
    (select round(2 * (MAX(eeg.t_s) + MIN(eeg.t_s))/48,1) from tbl_av eeg WHERE eeg.datum=(s.datum - interval 3 DAY) group by eeg.datum)
)as uitkomst
from tbl_av s
WHERE s.datum BETWEEN 20100101 AND 20110101
GROUP BY s.datum

which takes 0,015seconds... but that's surprisingly quick...
(you need to change the between values in the where-clause)
What're the results like at your side?

Thanks alot its working perfectly. Just tried running both queries (my old one) and yours
yours : 2 secs
mine: 49 secs
thats.. alot of improvement :P

Thanks alot. Id buy you a belgium wafel if i were close but unfortunately im not so i guess you have just have to do it with my gratitude. Thanks alot :)

[wim DC]

Hehe, You're welcome :)