October 6, 2012 at 1:08 am
Hi,
I am using SSIS Package to import more than 2000 files in my table which has below structure and data.
CodeItemNoPrice
1204589100
169744500
2204589200
3204589800
269744600
1981631000
Now, the process consuming 2 hours to import the file which is too time consuming. I have used one Flat File source from where i am picking files and mapped to OLEDB Command which executes Stored Procedure.
IF EXISTS (SELECT * fROM dbo.[MRP Code]
WHERE [Item] = @Item AND [MRP]NOT IN (@MRP))
BEGIN
SELECT * fROM dbo.[MRP Code]
WHERE [Item] = @Item AND [MRP] = @MRP
SET @Check =@@ROWCOUNT
IF @Check = 0
BEGIN
SET @Count = (SELECT MAX([Code]) FROM dbo.[MRP Code]
WHERE [Item] = @Item )
SET @Count = @Count +1
IF @Count > 1 AND @MRP NOT LIKE '0%' BEGIN
INSERT INTO dbo.[MRP Code]
([Code],[Item],[MRP],[Creation Date])
VALUES (@Count,@Item,@MRP,@Date)
END
END
END
Now, if Item is new then normal insertion happen but if item exists and Price is new then It checks from MAX code and increment with 1 and the insert.
I think this is taking lots of time.
Please suggest if anybody have better opinion.
Thanks
October 6, 2012 at 1:25 am
please reply!!!
Its really urgent..
October 6, 2012 at 3:55 am
Wow, you waited a whole 17 minutes before bumping your own thread 🙂 We're volunteers here. If you're under a serious deadline and need immediate assistance consider hiring someone with more experience than yourself to assist you.
Onto your issue...
This is not a SSIS problem or even a SQL Server problem, this is a design problem. It appears you are doing things row-by-row. It would be much more efficient for you to load the entire file data into a staging table and then write some set-based SQL code to apply all necessary updates to your table at one time instead of operating on the file one row at a time.
If you must stick with the way you're doing things then you might be able to improve performance by adding some indexes to your tables to improve lookup speeds or by tightening up some of your code to be more efficient but without seeing all the stored procedure code, the DDL for all the tables involved and some sample data it will be impossible to say more.
One thing you might be able to tighten up in the code you showed. Change this:
SELECT *
FROM dbo.[MRP Code]
WHERE [Item] = @Item
AND [MRP] = @MRP
SET @Check = @@ROWCOUNT
to this:
SELECT @Check = COUNT(*)
FROM dbo.[MRP Code]
WHERE [Item] = @Item
AND [MRP] = @MRP
As an aside, if you surround your code with [code="sql"][/code] it will appear highlighted on this forum and be easier to read. You'll get more chances of receiving help if your posts are easy to read.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply