How to improve ADO performance

Other SQL Server Topics
1

Hi,

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:

Create 40000 tables by executing “CREATE TABLE …”
SQL Server query.

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:

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.

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.

2

Instead of using the INSERT statement for one row at a time (or combining multiple rows into one insert statement as you described), you should investigate BULK INSERT. If the source data is coming from a file you may not need to go through C# at all when using BULK INSERT. If the data is available only in your C# program, then you should do something like this in this example.

One observation: Usually DDL operations such as creating tables or modifying tables is something that you do infrequently and deliberately (when compared to data DML operations). If you are creating a table with every call to the database, that may be an indication that you can improve the database design.

3

that question really has nothing to do with SQL server but more with your C++ application.

4

I'm curious... why are you creating 40,000 tables in a database? To what end are you doing that? If they're needed by an app, what will the app use them for?