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