Help with big stored procedure
-
i am very new to SQL, and my first project has been a pretty big undertaking... i am grabbing a set of textfiles from a webserver, parsing them, and doing a number of calculations, etc on them, and storing the result in SQL, this is the main load on SQL... other loads are nearly irrelevant and are always simple selects with few joins. i am having the problem that the CPU is getting toasted, and i know there is plenty of room for optimization in my (lengthy) procedure, which is being supplied to sql pretty much with no overhead (the parsing program uses very little cpu power and during an update always has a queue ready) so if you see anything at a glance that can speed it up please help! (i have guessed that having no extra indexes for this database is probably for the best?) Method Caller (C#) from web downloader
Method (C#) i have a class that manages all the sql operations for the program
SQL Stored Procedure (SQL) huge thing that is CPU bound... any hints are more than appreciated... this is for a project i intend to be release as code for anyone to use... but first i need to get it running at a decent speed... if i did all the processing in .net and dumped it into a txt, and BCP'd it in, would it be faster? i would still need many of the database operations there. -Luke -
i am very new to SQL, and my first project has been a pretty big undertaking... i am grabbing a set of textfiles from a webserver, parsing them, and doing a number of calculations, etc on them, and storing the result in SQL, this is the main load on SQL... other loads are nearly irrelevant and are always simple selects with few joins. i am having the problem that the CPU is getting toasted, and i know there is plenty of room for optimization in my (lengthy) procedure, which is being supplied to sql pretty much with no overhead (the parsing program uses very little cpu power and during an update always has a queue ready) so if you see anything at a glance that can speed it up please help! (i have guessed that having no extra indexes for this database is probably for the best?) Method Caller (C#) from web downloader
Method (C#) i have a class that manages all the sql operations for the program
SQL Stored Procedure (SQL) huge thing that is CPU bound... any hints are more than appreciated... this is for a project i intend to be release as code for anyone to use... but first i need to get it running at a decent speed... if i did all the processing in .net and dumped it into a txt, and BCP'd it in, would it be faster? i would still need many of the database operations there. -LukeOne thing you may want to try is using a dataset and dataadapter. Read your files and fill in the dataset, then call the DataAdapter.Update rather doing the ExecuteNonQuery for every line read.
-
One thing you may want to try is using a dataset and dataadapter. Read your files and fill in the dataset, then call the DataAdapter.Update rather doing the ExecuteNonQuery for every line read.
any thoughts on the size of the data set i should use? a typical update would probably be around 15-40k new/modified entries in [user_history] perhaps i will try using a 1/4 of the system memory to start... thanks, wether or not its will be an improvement will be interesting, as i still need a lot of if-exists... i guess it depends what factor is causing the cpu to remain at high load... :) i also have the option of switching the server... from a single Athlon XP 2500+ to a dual 900Mhz Xeon... i think staying with this machine is for the best...