none
How to improve ADO performance RRS feed

  • Question

  • 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:

     

    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.

     

    1. 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.

    Friday, January 1, 2016 8:18 AM

Answers

  • Hi chcw,

    >>So I want to improve the performance.

    I recommend using stored procedures which create table, because stored procedures can reduce network usage among clients and servers – stored procedures perform intermediate processing on the database server reducing unnecessary data transfer across the network. And stored procedures are tunable to improve the performance. if same stored procedure executed, it will use the previously cached execution plans. In addition, you could try to create a temp table, and put the data into temp table, and then you could use insert into select from temp table instead of insert the date into table one after another.

    >>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.

    You could invoke asynchronous execute method, and the following link for your reference.

    https://msdn.microsoft.com/en-us/library/ms681467(VS.85).aspx

    >>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?

    You could try to use stored procedures.

    Best regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Monday, January 4, 2016 1:24 AM
    Moderator