Last post Feb 17, 2014 05:32 AM by vikineese
Feb 17, 2014 04:37 AM|RohitRaghavan|LINK
I am using Visual Studio 2010 SP1 and SQL Server 2008 R2.
I have situations where there are >= 1 million transaction records for clients in a database table and I am generating around 30+ reports on this data based on conditions (e.g. select customer of type M, select customer where sales is > 1000 etc) plus some
of the column values are calculated based on the row data and placing it in multiple tabs of excel sheet. The code currently running brings all this information from the database into a DataTable and achieves the data selection using "Select" method of the
datatable or in some situations by looping through the data and checking for the conditions and again performing calculations. I would like to standardize the process flow and am looking at the following option -
1. Generate the conditional data using stored procedure. <This is quite effective as it only generate the data that is required, however the no. of stored procedure required is large (30 and might increase) and it is a bit complicated to perform the caluclation
(requires creation of temp table or table variables etc).>
2. Use the data table select method approach at all place replacing the for loop. <This works however is a concern from a memory point of view, caused out of memory exception some times>
3. LINQ - Use LINQ for performing the conditional data selection and the calculation.
The application will be used by not more than 7 people in total and not more than 2 people at a time.
Please guide me with the ideal approach for this situation.
Thanks and Regards,
Feb 17, 2014 05:03 AM|er_abhi|LINK
Doing at database level looks best to me but again as you mentioned you will end with lot of procs.Handling in C# should be fine ,don't understand why you are getting memory exception . You should try executing your code using multithreading or you can also
use parallel keyword which will give you fast performance.
Feb 17, 2014 05:32 AM|vikineese|LINK
Based on my experience I would suggest the following,
1. Perform all your caculations at your stored proc level itself as doing it at the code level would cost you more time and increases complexity and decreases maintainability / change requests.
2. Have proper INDEXES on the tables (compositie indexes as well) wherever required so that fetching data should not be a problem.
3. Fine tune your stored procedures for any unwanted memory usage or execution. Wisely use CTE, Pivot etc rather that performing calcuations and pivoting at the front end.
4. Always have a NO LOCK on all your select as you are mainly involving in report generation.
5. See the execution plan of your stored procedure and see where it takes time.
6. For certain worst case scenarios you can try n use LINQ in the front end for few calculations.
Hope this helps.