List:MySQL on Win32« Previous MessageNext Message »
From:<Amit_Wadhwa Date:August 11 2004 1:33pm
Subject:RE: Calling a Stored Procedure crashing MySQL!!
View as plain text  
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


Thread
Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
Re: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
RE: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!jbonnett11 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa11 Aug