谢谢大家的回复! 以下是我的过程 create PROCEDURE loadDataForStock AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
Declare @store VARCHAR(20) Declare @storeId Int Declare @sku VARCHAR(20) Declare @skuId Int Declare @qty Int Declare @stockSku VARCHAR(20) Declare @stockStore VARCHAR(20)
Declare cStock cursor For SELECT store, sku, qty FROM [TACCStock] OPEN cStock Fetch Next From cStock Into @store, @sku,@qty While @@Fetch_Status = 0 Begin SELECT @storeId = id FROM Tbl_Store WHERE store_no = @store
SELECT @skuId = id FROM Tbl_Sku WHERE sku_code = @sku SELECT @stockSku = [sku_id] , @stockStore = [store_id] FROM Tbl_Stock WHERE sku_id = @skuId AND store_id = @storeId AND qty <> @qty IF(@stockSku IS NOT NULL AND @stockStore IS NOT NULL) BEGIN Update [Tbl_Stock] SET qty = @qty WHERE sku_id = @stockSku AND store_id = @stockStore END --print @qty --pirnt @sku --print @stockStore Declare @existed int SELECT @existed=count(*) FROM [Tbl_Stock] WHERE sku_id = @skuId AND store_id = @storeId IF(@existed=0) BEGIN INSERT INTO [dbo].[Tbl_Stock] ([version] ,[qty] ,[sku_id] ,[store_id]) VALUES(0, @qty, @skuId, @storeId) END Fetch Next From cStock Into @store, @sku,@qty End -- End of Fetch Close cStock Deallocate cStock END GO 这是我的过程 ----------------------------------------------------------------------------------- Class.forName(driver); conn = DriverManager.getConnection(strUrl, "lab_user", "root"); CallableStatement proc = null; System.out.println("start:"+new Date()); proc = conn.prepareCall("{call loadDataForStock}");
问题知道了原因了,查了一下微软的手册http://technet.microsoft.com/en-us/library/ms176047.aspx这个手册上说: print 方法的功能是: Returns a user-defined message to the client.这里的client就是JDBC客户端. 也就是说, 我的3行print语句从SqlServer向你的JDBC客户端程序发送了 34万*3 个数据
以下是我的过程
create PROCEDURE loadDataForStock
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @store VARCHAR(20)
Declare @storeId Int
Declare @sku VARCHAR(20)
Declare @skuId Int
Declare @qty Int Declare @stockSku VARCHAR(20)
Declare @stockStore VARCHAR(20)
Declare cStock cursor For
SELECT store, sku, qty FROM [TACCStock] OPEN cStock
Fetch Next From cStock Into @store, @sku,@qty While @@Fetch_Status = 0 Begin SELECT @storeId = id FROM Tbl_Store WHERE store_no = @store
SELECT @skuId = id FROM Tbl_Sku WHERE sku_code = @sku SELECT @stockSku = [sku_id] , @stockStore = [store_id]
FROM Tbl_Stock WHERE sku_id = @skuId AND store_id = @storeId AND qty <> @qty IF(@stockSku IS NOT NULL AND @stockStore IS NOT NULL)
BEGIN
Update [Tbl_Stock] SET qty = @qty
WHERE sku_id = @stockSku AND store_id = @stockStore
END
--print @qty
--pirnt @sku
--print @stockStore
Declare @existed int SELECT @existed=count(*) FROM [Tbl_Stock] WHERE sku_id = @skuId AND store_id = @storeId IF(@existed=0)
BEGIN
INSERT INTO [dbo].[Tbl_Stock]
([version]
,[qty]
,[sku_id]
,[store_id])
VALUES(0, @qty, @skuId, @storeId)
END Fetch Next From cStock Into @store, @sku,@qty End -- End of Fetch Close cStock
Deallocate cStock
END
GO
这是我的过程
-----------------------------------------------------------------------------------
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "lab_user", "root");
CallableStatement proc = null;
System.out.println("start:"+new Date());
proc = conn.prepareCall("{call loadDataForStock}");
proc.execute();
这是java调用code
--------------------------------------------------------------------------------去掉这些print语句 当然可以执行通过,并且不会占用太多tomcat内存!
我想知道有这些print语句和没有为什么会占用tomcat如此多的内存,java调用procedure时JVM是怎么工作的?想去看看sqlserver对jdbc的实现源码了
java调用procedure时JVM是怎么工作的?这个最好去java板块问问哈,我对这个原理不懂
也就是说,把print的结果,都返回到jdbc的客户端,也就是都返回到了tomcat了是吧,然后tomcat通过控制台,再输出。