Timeout expired after calling stored procedure

I keep getting this error after the calling the stored procedure on mysql database :

"MySql.Data.MySqlClient.MySqlException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.TimeoutException: Timeout in IO operation"

I know that the problem is related to the query it takes to long to execute so my question is this query it is good ? is there another way to optimize this stored procedure and make it return the data in relatively finite time:

CREATE DEFINER=`mysql`@`%` PROCEDURE `UpadtePriceToCalibre`(IN Dos int(6),    IN Var varchar(50), IN PRIX double(10,4) , IN Cal int(6))
BEGIN
  insert into xxpch 
select `NuméroDossier`,`NuméroLot`,`Variété`,`Client`,`NF`,`Calibre`,`PrixKg`,PRIX,`NetFacture`,(PRIX * PoidNet),NOW()         
from detaildossier
where NuméroDossier = Dos AND Variété LIKE CONCAT('%',Var,'%') AND Calibre= Cal;
update detaildossier
SET PrixKg = PRIX 
    , NetFacture = (PRIX * PoidNet) 
    , PrixColis = (( PoidNet / NombreColis) * PRIX)
WHERE NuméroDossier = Dos AND Variété LIKE CONCAT('%',Var,'%') AND Calibre= Cal;
update aff_e ,cpv_d
set aff_e.netcpv =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
    ,aff_e.mntvnt =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
where  aff_e.numdos = Dos   AND cpv_d.numlot = aff_e.numlot;
END

The first two queries in the stored procedure look OK to me. Just an insert and an updates.

The final update query might take a bit longer because its summing multiple rows on another table. Its hard to tell without knowing more about the database whether that should run fast or not.

edit: Come to think of it, perhaps there is something wrong with the final update. Instead of:

update aff_e ,cpv_d
  set aff_e.netcpv =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
    ,aff_e.mntvnt =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
where  aff_e.numdos = Dos   AND cpv_d.numlot = aff_e.numlot;

try:

update aff_e 
  set aff_e.netcpv =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
  ,aff_e.mntvnt =(select sum(cpv_d.prxtot) from cpv_d where cpv_d.numdos = Dos AND cpv_d.numlot = aff_e.numlot)
where  aff_e.numdos = Dos   AND cpv_d.numlot = aff_e.numlot;

I've got a feeling that adding cpv_d to the first line of the update statement might be causing problems, because it might cause lots of rows to get involved in the update that aren't needed.

Another idea: It could be that you've got some sort of locking or deadlock situation on your database and so your SP is timing out because of that. For example someone else running a big report could potentially (in certain conditions) lock parts of the tables you are trying to update and in situations like that, even fast stored procedures might time out.

As others have said, you could also just try increasing to timeout setting.

Welcome to ModOverflow!