Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Lightbulb Unanswered: SQL Server/ADO Performance

    Hello All!

    This is my first posting in this forum so thanks for any help in advance!

    We are currently working on a project here with SQL Server 2000 standard edition. People would like to observe the data in the SQL Server database and analyse it with graphs. I used ADO for retrieving and Visual C++ for the GUI. We were working on a 2GHz PC first of all and then switched to a 1.3 Ghz dual pentium PC with Windows 2000 Server on it. The SQL server settings should be the same on both machines. However after the move we could read the data in 20-25 secs, although it was 1-2 secs previously. I am looping through the data via ADO recordset from three tables of about 45000 records in total. I tried to do the optimisations in ADO, like assigning fields prior to going through the loop, however this is still a long time to wait for the data to come up for the users. Shall I make a hardware change again, or is there a better solution in SQL server, ADO to perform better on this dual 1.3Ghz machine?

    Thanks for your replies...

  2. #2
    Join Date
    Sep 2003
    Posts
    39
    1. Check your indices.
    2. Check the amount of memory assigned to Sql Server.
    3. Check your indices.
    4. Check the size of your temp table.
    5. Check your indices.
    6. Have Sql Server handle most of the business logic instead of calculating things with ADO.
    7. Check your indices ...

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    I checked the assigned memory, temp table and of course the indices,
    they seem to be all OK.

    The query I send through ADO return the recordset fast <1sec. It is when I loop through the recordset in my case about 45000 times and assign the values, it takes the considerable amount of time.

  4. #4
    Join Date
    Sep 2003
    Posts
    39
    You may want to disconnect the recordset after you retrieve it. I'm suffering from temporal amnesia here because I can't remember the command

    It's objRs.getRows I think ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •