Problem: 100% CPU utilization Application: Windows Application (.NET 2.0, C#, SQL SERVER 2005, Web Services). Purpose: To upload data from an excel sheet to the database Functionality: The excel sheet will have around 15,000 – 100,000 rows of data across 40 – 50 columns. The data from the excel sheet has to be imported into three Tables in the database which includes certain business logic. Constraints: We are not supposed to use database objects like Stored Procedures or DTS packages due to portability issues. The data upload process done by the application is explained below. 1. The excel data is populated into a Data Table using the excel manager. The data tables in the dataset is then populated from this excel data table and finally the table adapters update method is called to push the data from the dataset to the database. 2. For this import process we also need to check if the data in the excel sheet already exists in the database. For doing this validity we tried two different approaches. a. The existing data from the database for the particular item is populated in the data table. For example the "Data Table 1" data table is populated from the database initially. Then the process runs in a loop for each row in the excel data table and checks if the data is existing in the "Data Table 1" data table and adds the new row if the data doesn’t exist. After updating the "Data Table 1" data table the table adapters update method is called to push the data to the database. A similar process is carried out for updating the other two data tables also in the dataset. This process causes a 100% CPU utilization on the web service. b. The second approach was to eliminate the process of validating for existing records in the data tables. For example all the "Data Table 1" data from the excel data table was inserted into the "Data Table 1" data table. The table adapters update method was called and the validation for existing records was carried out in the database side. The command text for the insert command was updated appropriately like “IF NOT EXISTS (SELECT ….) INSERT INTO ….” This process caused a 100% CPU utilization on the database server. Is there any better approach that can minimize the CPU Utilization or any configuration settings that needs to be looked at. Any help will be appreciated. Thanks.

Maybe I'm missing something, but why don't you rely on your Primary Key to do its job, and just insert the data straight into the database? The biggest complication I see from this is a Primary Key Not Unique error, which is easily ignored.

Thanks Razor. Yes we do insert data to the database from the dataset by using the update method of the tableadapter in one of the approaches. We validate for uniqueness for a couple of columns other than the Primary Key which is an IDENTITY column in the database. This is the process which uses max cpu because of its intensity. Can this be handled in a different approach other than Datasets, or if this has something to do with the server configuration as well.

Well, I guess you could build your own index, and then refer to that during your uniqueness checks. I'm not sure how much this would save you, though, but it's bound to be faster than your brute force method. It'd depend on the amount of data you're checking against. A second option would be to split the uniqueness checks and the database INSERTs into two separate threads. Depending on how you implemented it, the program might become IO (database) bound.

The uniqueness check needs to be done for data of about 15000 to 100000 which is going to be slow. The second option you have mentioned about spliting the process into separate threads seems something we may want to try out. Thanks for the suggestions.