Stored Procedure and ADO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure and ADO

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2007-03-05 : 13:59:47

I am a little bit surprised how slow stored procedure works.
I had regular INSERT TO query which I sent with ADO CommandText to transfer data on sql server 2000 from table to table and
I was thinking will be faster if I create procedure and transfer data from table to table by ADO and stored procedure.

It working much slower with stored procedure. I measured ...4 seconds with query, 15 seconds with stored procedure ..

Is there something else I need to add to stored procedure ....

Here is my procedure ...

CREATE PROCEDURE Prodaja_Rijec_04
@date_from datetime,
@date_to datetime,
@trazilica nvarchar(30),
@ReciNum integer
AS
SET NOCOUNT ON
DECLARE @datum_od datetime
DECLARE @datum_do datetime
DECLARE @srchalica nvarchar(30)
DECLARE @recko integer
SET @datum_od = convert(varchar,@date_from,101)
SET @datum_do = convert(varchar,@date_to,101)
SET @srchalica = '%'+ @trazilica + '%'
SET @recko = @ReciNum
INSERT INTO arhPOSInvoiceItems_Sales ( [Description], [Item Number], Quantity, [UPC Code], [Quantity In Stock], [Entry Date],RepID )
SELECT arhPOSInvoiceItems_2004.[Description], arhPOSInvoiceItems_2004.[Item Number],
Sum(arhPOSInvoiceItems_2004.Quantity) AS [SumOfQuantity], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri, @recko
FROM arhPOSInvoiceItems_2004
INNER JOIN Inventory
ON arhPOSInvoiceItems_2004.[Item Number] = Inventory.[Item Number]
WHERE arhPOSInvoiceItems_2004.[Ship Date] Between @datum_od And @datum_do And arhPOSInvoiceItems_2004.[Description] Like @srchalica
GROUP BY arhPOSInvoiceItems_2004.[Description], arhPOSInvoiceItems_2004.[Item Number], Inventory.[UPC Code], Inventory.[Quantity in Stock], Inventory.PoljeTri
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources