首页 | 新闻 | 交流 | 问吧 | 文档 | 手册 | 下载 | 博客

收藏此问题 发表新评论

存储过程老是超时

SQL code
CREATE PROCEDURE order_down_forjoin2 @result varchar(50) output, @orderno varchar(50), @out_trade_no varchar(50), @product_class varchar(50), @brand varchar(50), @product_no varchar(30), @product_size varchar(50), @color varchar(100), @color2 varchar(100), @number int, @price money, @area varchar(50), @city varchar(50), @county varchar(50), @address varchar(200), @zip varchar(20), @username varchar(50), @phone varchar(50), @mobile varchar(50), @email varchar(100), @joinno varchar(50), @isems int, @ems money, @send varchar(400), @memo text, @sendinfo1 varchar(50), @sendinfo1time datetime, @sendinfo varchar(50), /*库存的更新*/ @number55 int, @number2 int, @number3 int, @number4 int, @number5 int, /*库存记录的插入*/ @number222 int, @stock varchar(50), @operation_type varchar(50), @operation varchar(50), @memo_product_inout_record text, @users varchar(50), /*备用金的更新*/ @checks varchar(16), @lastprice money, @memo_protect_log text, @server varchar(50), @disprice_class varchar(50), @disprice money AS begin TRANSACTION declare @nror int set @nror=0 insert into tbl_order (orderno,out_trade_no,product_class,brand,product_no,product_size,color,color2,price,area,city,county,server,address,zip,username,phone,email,memo,joinno,isems,ems,send,mobile,disprice,disprice_class) values (@orderno,@out_trade_no,@product_class,@brand,@product_no,@product_size,@color,@color2,@price,@area,@city,@county,@server, @address,@zip,@username,@phone,@email,@memo,@joinno,@isems,@ems,@send,@mobile,@disprice,@disprice_class) set @nror=@nror+@@error update tbl_product_store set number=number-@number55,number2=number2-@number2,number3=number3-@number3,number4=number4-@number4,number5=number5-@number5 where product_no=@product_no and color=@color and product_size=@product_size set @nror=@nror+@@error insert into tbl_product_inout_record (product_no,color,product_size,number2,stock,users,operation_type,operation,memo) values (@product_no,@color,@product_size,0- @number,@stock,@users,@operation_type,@operation,@memo_product_inout_record) set @nror=@nror+@@error update tbl_protect set price=@lastprice ,checks=@checks where joinno=@joinno set @nror=@nror+@@error insert into tbl_protect_log (joinno,orderno,price,product_no,memo) values (@joinno,@orderno,0-@price,@product_no,@memo_protect_log) set @nror=@nror+@@error if @nror<>0 begin set @result='失败' rollback transaction end else begin set @result='成功' commit transaction end GO

C# code
public string order_down_forjoin(string product_no, string product_class, string brand, string product_size, string color, int number, double price, string area, string city, string county, string address, string zip, string username, string phone, string email, string joinno, string memo, string send, double ems, int isems, string color2, string mobile, string sendinfo1, DateTime sendinfo1time, string sendinfo, int number55, int number2, int number3,int number4, int number5,int number222, string stock, string operation_type, string operation,string users,string memo_product_inout_record, string check, string memo_protect_log, string orderno, string out_trade_no,double lastprice,string server,string disprice_class,double disprice) { con = new SqlConnection(shopcon); con.Open(); com = new SqlCommand("order_down_forjoin2", con); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add("@product_no", product_no); com.Parameters.Add("@product_class", product_class); com.Parameters.Add("@brand", brand); com.Parameters.Add("@orderno", orderno); com.Parameters.Add("@out_trade_no", out_trade_no); com.Parameters.Add("@product_size", product_size); com.Parameters.Add("@color", color); com.Parameters.Add("@color2", color2); com.Parameters.Add("@number", number); com.Parameters.Add("@price", price); com.Parameters.Add("@area", area); com.Parameters.Add("@city", city); com.Parameters.Add("@county", county); com.Parameters.Add("@address", address); com.Parameters.Add("@zip", zip); com.Parameters.Add("@username", username); com.Parameters.Add("@phone", phone); com.Parameters.Add("@mobile", phone); com.Parameters.Add("@email", email); com.Parameters.Add("@joinno", joinno); com.Parameters.Add("@isems", isems); com.Parameters.Add("@ems", ems); com.Parameters.Add("@send", send); com.Parameters.Add("@memo", memo); com.Parameters.Add("@sendinfo1", sendinfo1); com.Parameters.Add("@sendinfo1time", sendinfo1time); com.Parameters.Add("@sendinfo", sendinfo); com.Parameters.Add("@number55", number55); com.Parameters.Add("@number2", number2); com.Parameters.Add("@number3", number3); com.Parameters.Add("@number4", number4); com.Parameters.Add("@number5",number5); com.Parameters.Add("@number222", number222); com.Parameters.Add("@stock",stock); com.Parameters.Add("@operation_type",operation_type); com.Parameters.Add("@operation",operation); com.Parameters.Add("@users", users); com.Parameters.Add("memo_product_inout_record", memo_product_inout_record); com.Parameters.Add("@checks",check); com.Parameters.Add("@lastprice", lastprice ); com.Parameters.Add("@memo_protect_log", memo_protect_log); com.Parameters.Add("@server", server ); com.Parameters.Add("@disprice",disprice ); com.Parameters.Add("@disprice_class",disprice_class ); com.CommandTimeout = 1000; //com.Parameters.Add("@price2",price2); com.Parameters.Add(new SqlParameter("@result", SqlDbType.NVarChar, 30)); com.Parameters["@result"].Direction = ParameterDirection.Output; com.ExecuteNonQuery (); string result = com.Parameters["@result"].Value.ToString(); return result; } string strs = co.order_down_forjoin(product_no, product_class, brand, product_size, color, num, price, provice, city, county, addmore, zipcode, touser, litterphone, email, joinno, task, send, ems, isems, color2, mobile, sendinfo, dt, sendinfo, num3, num2, num2, num2, num2, num, "全国仓", "订单", "出库", users, "", newcheck, "订单", order_no, out_trad_no, lastprice, servers,disprice_class ,disprice ); if (strs == "成功") { int op = co.update_product_store_cut(product_no, color, product_size, num3, num2, num2, num2, num2); if (op > 0) { RegisterStartupScript("sdf", "<script>alert('操作成功,请留意订单发货信息!')</script>"); } } else if (strs == "失败") { RegisterStartupScript("sdf", "<script>alert('操作失败,请检查输入的字符!')</script>"); }

这句代码老是运行时超时,不知道哪里有问题,请各位大哥帮小弟一下
昵称: duanxifeng888  时间: 2010-08-07 14:20:54
SQL code
begin TRANSACTION declare @nror int set @nror=0 insert into tbl_order (orderno,out_trade_no,product_class,brand,product_no,product_size, color,color2,price,area,city,county,server,address,zip,username,phone,email,memo,joinno, isems,ems,send,mobile,disprice,disprice_class) values (@orderno,@out_trade_no,@product_class,@brand,@product_no,@product_size,@color,@color2, @price,@area,@city,@county,@server, @address,@zip,@username,@phone,@email,@memo,@joinno,@isems, @ems,@send,@mobile,@disprice,@disprice_class) IF @@error<>0 goto ErrM update tbl_product_store set number=number-@number55,number2=number2-@number2,number3=number3-@number3, number4=number4-@number4,number5=number5-@number5 where product_no=@product_no and color=@color and product_size=@product_size IF @@error<>0 goto ErrM insert into tbl_product_inout_record (product_no,color,product_size,number2,stock,users, operation_type,operation,memo) values (@product_no,@color,@product_size,0- @number,@stock,@users,@operation_type,@operation, @memo_product_inout_record) IF @@error<>0 goto ErrM update tbl_protect set price=@lastprice ,checks=@checks where joinno=@joinno IF @@error<>0 goto ErrM insert into tbl_protect_log (joinno,orderno,price,product_no,memo) values (@joinno,@orderno,0-@price,@product_no,@memo_protect_log) IF @@error<>0 goto ErrM set @result='成功' commit transaction Errm: begin set @result='失败' rollback transaction end GO
昵称: shutao917  时间: 2010-08-07 14:36:00
设置CommandTimeout
昵称: xys_777  时间: 2010-08-07 14:43:01
超时?那查查哪些语句执行成功了,哪些没有成功看看问题出在哪条语句上!优化一下。
昵称: hao1hao2hao3  时间: 2010-08-07 14:59:17