I am using ADO/Visual C++ 2008 to access SQL Server 2008 database.
I mainly use the Execution method of the ADO connection to the SQL Server to perform the following tasks:
1. Create 40000 tables by executing “CREATE TABLE …” SQL Server query.
2. Add about 3000, 0000 records to these tables by executing “INSERT INTO …” query. In each invoke of Execute method, only one SQL statement is executed.
Now I find my program is very slow. And the bottleneck is the Execution method, which takes about 90% of the total time.
So I want to improve the performance.
I have the following several strategies:
1. Whether Execute method can be invoked asynchronously? If yes, then after I invoke the Execute method to submit the SQL query to SQL Server engine, before the query is executed completed, the Execute method can return and I can run other parts of the program, which will save a lot of time.
2. Another solution is to merge several SQL statements together and submit them together with one invoke of Execute method. But I make a test of such a solution and find the time consumed will be longer than Executing one statement at a time. So it seems this solution is also not feasible?
Please tell me whether the above strategies are feasible? And whether there are more solutions for my problem.