The fields are all Different, had to change the names because of
corporate policy.
My Existing Query (without cursors) runs fine and is very similar to
what you've given below,
Was just expoerimenting with cursors to see if it made any performance
difference.
Thanks for the detailed response though.
But why would a stored Procedure crash MySQL when executed??
I tried a very simple query instead of the below given query, and I
still had the immediate crash when calling the procedure.
Could it be a problem with my MySQL Installation or something not set
right? Or is it a bug somewhere?
Regards,
Amit Wadhwa
-----Original Message-----
From: jbonnett@stripped [mailto:jbonnett@stripped]
Sent: Wednesday, August 11, 2004 5:28 AM
To: Wadhwa, Amit; win32@stripped
Subject: RE: Calling a Stored Procedure crashing MySQL!!
I have tried to unravell what you are trying to do and it seems to me
you may be able to achieve what you want without any cursors. As far as
I can tell what you are trying to do can be rewritten something like
this
SELECT <?>
FROM t1 as x, t1 as y
WHERE x.f1 = y.f1
AND y.f2 > x.f2
AND y.f3 <= x.f3
AND x.f6 NOT IN ('CNL')
AND x.f7 <> '0'
AND x.f8 <> 'null'
AND y.f4 NOT IN ('CNL')
AND y.f5 IS NOT NULL
AND y.f6 <> 'null'
What you put at <?> will depend on what you want to do at "//do
something here" and which fields you need. You could perhaps make a
cursor out of the above query to do whatever you want.
Also I think f4 NOT IN ('CNL') is the same as f4 <> 'CNL' which probably
runs faster.
I am also suspicious that your table design may not be very good. It
looks to me that you have two different things stored in fields of the
one table.
Life may be a lot simpler if there were two tables. While I don't
understand what you are doing, when I see things like f4 NOT IN ('CNL')
and f6 NOT IN
('CNL') plus f6 <> 'null' and f8 <> 'null' I get the feeling that f4, f6
and
f8 may be storing multiple instances of the same sort of data in the one
table. This sort of thing violates first normal form. You will find SQL
works much better if you design your tables to at least 3rd normal form.
Anyway, there are some thoughts.
John Bonnett
-----Original Message-----
From: Amit_Wadhwa@stripped [mailto:Amit_Wadhwa@stripped]
Sent: Wednesday, 11 August 2004 4:53 AM
To: win32@stripped
Subject: Calling a Stored Procedure crashing MySQL!!
Hi All,
Im a Newbie with Cursors,
What I basically need to do is:
Execute one SQL Statement:
Loop
For every Record, execute a query based on results of the first
query
Loop
Check a Condition based on these results.
If (condition)
Execute Another Query
Else
Execute Another Query
End Loop
End Loop
Currently am using JDBC/Java Program to do the above in 10 Minutes for
over
400,000 Records. But am experimenting with Cursors to see if it can be
done
any faster.
The Procedure is given below.
It Stores Fine. But when I call it, my MySqld-NT crashes
immediately.(...has
caused an error and needs to close)
Any Help is greatly Appreciated!
Regards,
Amit Wadhwa
CREATE PROCEDURE calculatehits()
BEGIN
DECLARE cur1 CURSOR FOR SELECT f1,f2,f3,f4 FROM t1 WHERE f5 IS NOT NULL
AND
f6 NOT IN ('CNL') AND f7 <> '0' AND f8<> 'null' ; DECLARE a CHAR(16);
DECLARE b,c DATETIME; DECLARE d int(16); DECLARE e int(16); OPEN cur1;
REPEAT FETCH cur1 INTO a, b,c,d; SELECT f9 INTO @e FROM t1 WHERE f1= @a
and
f2 > @b And f3 <= @c AND f4 NOT IN ('CNL') AND f5 IS NOT NULL AND f6 <>
'null'; IF d=e THEN //do something here END IF; UNTIL done END REPEAT;
CLOSE
cur1;
END
