We have a web application using WCF services hosted by a self-hosting Windows Service. For the moment we are using ODBC as our database connectivity and we have a question regarding CPU at the database level.
We are currently working on a large operation (importing hundreds of thousands of records) and we notice during this operation that the database (Oracle, SQL Server) begins using a large amount of CPU and Memory as well. We are less concerned about the memory growth but it is the CPU that we wonder if we are doing something incorrect to cause such a thrashing of the database. Let me explain the workflow:
- (Web Application) User selects data to import and passes this selection to our self-hosted WCF service. To increase download speeds we are sending data concurrently using a OneWay operation contract (chunks of 200 items).
- (Self-Hosted WCF service) Accepts the selected data from the Web Application, opens a database connection via ODBC, and begins processing the data for import.
During the import of the data we are keeping our transactions very small (only opening transactions at time of save and committing immediately).
Now, we are doing a lot of selects and there are a lot of SELECTS/UPDATES/INSERTS going on but how can we prevent such a thrashing of the database CPU?
I'm sure there are questions, please feel free to ask whatever you need to help us diagnose what the issue could be.