July 12, 2017 at 12:06 pm
hi
I have 13980 rows coming in excel files which containg employee status.
I need to load those in sql server table. everyday I need to see if any employee data is updated or new employee , based on that I need to updatemy table in sql.
right now I am putting everything in stg table and planning to use merge to load or update data.
but its taking lot of time ,please help
July 12, 2017 at 12:14 pm
coool_sweet - Wednesday, July 12, 2017 12:06 PMhi
I have 13980 rows coming in excel files which containg employee status.
I need to load those in sql server table. everyday I need to see if any employee data is updated or new employee , based on that I need to updatemy table in sql.
right now I am putting everything in stg table and planning to use merge to load or update data.
but its taking lot of time ,please help
13980 rows should load fast. Which part of your process is taking a lot of time?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 12, 2017 at 4:03 pm
i have derived column and data conversion in between.
will it be reason?
July 13, 2017 at 7:58 am
As Phil said, it should be very quick, even with the data conversion. Have you got any indexes on your staging table? Multiple indexes will slow your write speed down. Sometimes it speeds things up if you drop the indexes before the data load, except primary key and then recreate the indexes at the end of the data load.
Can you post the logic from your derived column?
Tim.
July 13, 2017 at 8:37 am
no there is no index in staging table
I have 32 columns in excel
July 13, 2017 at 8:46 am
here is my derived column
TOKEN([Copy of Employee Name (Last , MI)],",",2) + " " + TOKEN([Copy of Employee Name (Last, First )],",",1)
REPLACE(LTRIM(RTRIM([Copy of Supervisor Name ])),",","")
REPLACE([Copy of Job (Supervisor)],",","")
i am replacing this column , to remove ,
July 13, 2017 at 10:05 am
Nothing is leaping out at me from your code to cause a bottleneck.
If it is taking a long time to load the data into the staging table before any further data processing I would be looking at Memory Usage, CPU usage, available disk spaces, Is the database log file on a separate drive to the data file, is tempdb on it's own drive? Is there more than 1 tempdb data file? Is there several GB free disk space on the system volume? I had one machine that had 2GB free on the system volume which would be swallowed by SQL creating temporary files in the hidden folders during a data load process. Once we increased the free disk space on the system volume the problem went away.
July 13, 2017 at 11:38 am
Which part is slow? The loading of Excel into the staging table or merging the staging table into the live table?
You are using SSIS. How much memory does your server have that is free at the time the data import starts?
SSIS operations in separate memory space from SQL Server. What this means is that if you have SQL Server configured to use all of the memory and then you try to run an SSIS package, it will frequently need to read and write from the disk instead of from memory which will be drastically slower.
I would ensure that your SQL Server has a max memory value set that is reasonable and that your Server (physical or VM) has enough memory for SQL and SSIS.
We had a set of SSIS packages that run in less than 5 minutes most of the time. Every now and then we were seeing it run for 8 hours. Investigating the issue, we noticed that the server ran out of memory which is what caused this to happen. Reducing the max memory for SQL Instances that are low priority or that don't need as much memory as we had allocated for it. Since lowering Max Memory, the SSIS load has not run longer than 5 minutes.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply