June 17, 2005 at 7:52 am
Hello,
I have 2 scheduled ( every 5min) nested stored procedures that extract data from a flat file and execute a sequence of other 11 nested stored procedures in order to insert or update a particular record in my database ( 6GB).
Although the process works fine and usually a 1000 of files(60K each) get processed quickly, my concerns are about the memory overhead it implies. The server has 1.5GB of Ram and every Week i must restart my server as this importation process blocks the access to the server. this off course, either for the file procesing procedures but also for the web and application clients connected on this server.
My question is :
Would it be useful to change this importation process from a scheduled SPs to scheduled dts job that process the files and insert/update the data ? What would be the implication on the performance of the server, in term of memory , processors, process speed ?
Is there a better way to achieve this importation problem a part from adding more RAM?
My main concerns is to avoid this restart problem.
Thanx a lot !
Y.K
June 17, 2005 at 8:39 am
The real question is why do you have to reboot the server? What is the bottleneck (cpu/disk/ram).
When are you doing this importation (off hours when the site is not busy are at 5 pm when everyone's logged in)?
How much data is imported at each execution?
Is there a lot of indexes in the tabs you insert to?
June 17, 2005 at 8:49 am
well it seems to me that the reboot is necessary for reinitiating everything.The botteleneck is mainly the free ram usage.
it seems also that the dynamic memory management of the sql server doesn't really work nor free memory.
The job runs 24/7 every 5 min ( I know it's heavy...) but the structure of the process needs it. A very old dos based application delivering text file to the sql server and there is no other way that this dos based application could removed or upgraded?
Concerning the number of data imported it is more or less 500 files, of 60KB each every 5 min.
There is not alot of indexes, only 2.
June 17, 2005 at 8:56 am
One thing you must know about sqlserver managing ram is that it'll take all you give to him for faster data access. If you have a 1 gig db and give the server 1.5 gig of ram to work with, you can make sure that 100% of the db will be in memory (assuming you query all the tables).
Also unless the you have a damn good reason, don't reboot the server.
The list goes as follow : service pack on windows or the server (reboot even if not required. This is to be 100% sure that it's still working. You don't want a surprise in 2 months wondering what the hell happened to the server and having to track back to the service pack(s)).
Having to change hardware on the server.
That's about it. There are surely special circumstances but you haven't met one yet.
What makes you think that the server doesn't have enough memory?
June 17, 2005 at 9:11 am
The imported files contains a list of "Key=Value". Before inserting the different values in the appropriate tables , they are all stored in local variables, manipulated ( cleaned and converted to the specific datatypes) as a first step.
The second step is for inputing the data in the different tables ( 11 different one) using a stored procedure for each table. This happen only if all variables have been completely loaded ( no NULL values).
I have traced the execution of this TSQL script and everytime the bottleneck occurs it's just before the second step to occur. A print out of the values shows null values instead of the field value.
June 17, 2005 at 9:12 am
Is the error coming from the files, or from the import?
June 17, 2005 at 9:15 am
To me it's from the import , all the files are correctly formed and not sent unless they meet the requirement on data coherence with the sql server database.
June 17, 2005 at 9:17 am
You'll have to debug the procedure... I can't help you on that part. You'll have to figure out what's hapenning.
June 17, 2005 at 9:20 am
Thanx anyway .
June 17, 2005 at 9:23 am
How are importing the files anyways?
June 17, 2005 at 9:40 am
They are imported through with the help of this SP
--declaration of local variable
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'OpenTextFile', @objFile out, @XFileName, 1
exec sp_OAMethod @objFile, 'AtEndOfStream', @blnEndOfFile out
while @blnEndOfFile=0 begin
-- read every line
-- clean data
-- convert to UNIcode
-- assign to the dedicated local variable
-- loop
-- If ok insert into the database
June 17, 2005 at 9:54 am
That's you bottlenck. Change this task to a bulk insert using dts.
You could import all the files, then start working on validating/splitting.
Then you'd just have 11 insert queries to make instead of 60K ??.
You could get extrodinary improvement from that.
June 18, 2005 at 5:43 am
What is the bottelneck , the fact to import the whole file in memory or to use "sp_oa" suite procedure?
of what i have understood from your post , is it to import all the files into a temp table using the bcp utility, then clean , convert the different data to its original datatype and finally insert it to the appropriate tables ?
June 18, 2005 at 12:35 pm
Yes, sp_oa are notoriously slow. They cause a lot of overhead (in memory/cpu mostly). If you could batch import the files, then in 1 select statement, validate/split, then you'd only have 11 statements to insert the data into the tables. If I'm correct this baby's gonna run at least 10 times faster.
June 19, 2005 at 9:34 am
Thank you , i will try that and get back to you !
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply