Hi Ger,



I have some process where I handle millions of records, for those process I had to build a stored procedure, using a BO to load 500k+ records which you would need to enumerate using bo.Getenumerable would be pretty slow.  On some cases I need to export some data to CSV and loading 1.5 million records into a datatable to be exported is just fine, but for you to process record by record could have a huge performance hit.  MS SQL server has several nice features that allows you to process millions of records in batch to do all sort of things and then you can just load that result into a BO to show your report.

I am not a SQL expert, for that I do use the services of a MS SQL MVP developer for the last 2 years with pretty good results in all the projects I have used him, so if you need some reference I will be more than happy to introduce you to him. 
To give you an example one of my process imports 800+ thousand records every week, do several calculations and matches against a lookup table which keeps growing with each imported batch, I have customers with more than 25 million records and the whole stored procedure process takes from 1 to 10 minutes to complete, when I was doing the same process via enumerating the records, the process could take from 30 minutes to 3 hours to complete, this MS SQL MVP make me a real believer of the power of MS SQL Server engine.

Edhy Rijo

Hi Michel. Thanks for replying.

Had a quick look at this product and it seems to allow you to use Sql select type syntax for querying a Dataset. I could not determine if this product will allow you to Select from a Dataset table and JOIN a Database Table (that doies not exist in the dataset). I have left a question on their site. If it does this (i.e. can join say a Dataset Table to a Databse Table) it would be a grat help

Hi Ger,

I don't understand how you could join anything to a database table, if the code is not in a database object.

if you code where you have the dataset table, then you are out of the database, and the database tables are reachable only through a dataset. If you code where you have the table, then you are inside the database, and you need to have received the dataset as a table parameter, or a temp table to have be filled with the rows of the dataset.

Hi Edhy. Many thanks for very informative reply.

After reading it and considering it, I will difinitely have a look at using Stored Procedures now , particulalrly for any processing which involves large numbers  of rows. I agree with you that this should definitely be more efficient, and it should also future proof development to some extent in that the 'processing' will be kept in the datatbase, and away from the client side, in case the futore dictates changes to the Client Side, in an ever changing and rapidly evoving IT environment

Hi Michel.

After considering the very informative replies in this thread, I am definitiely going to look into Stored Procedures and User Functions as part of a solution to Heavy processing requirements.

You are welcome Ger!!!

Edhy Rijo