February 28, 2009 at 1:58 am
I have a database with 10 tables. In one table "ABC" there is more than 1 million of records. So i have added index on the table "ABC" as per the fetch query. During insert operation almost 8000 of records are inserted in "ABC" table and around 500 records are inserted in other tables. In insert we are generating XML and sending bulk data of around 200 records for insert operation in 'ABC' table. This sp is called around 40 times from .Net C# windows application , so in this way 8000 records are inserted. But insert operation takes almost 1 minute. Even when records are fetched in same manner. SP is called 40 times on "ABC" table which return 200 records per call. So even fetching record takes almost 1 minute. Eventhough when we deleted half millions of record then also insert and fetch operation was taking around 1 minute.
We tried to improve performance by rebuilding the index with different fill factor but it did not help to improve the performace.
February 28, 2009 at 6:11 am
I'd suggest a couple of different things. First, have you checked the execution plans on your select statements to be sure that the indexes on your tables are being used. By used I mean, an index seek, not an index scan or a table scan. Also, just check the query and execution plan in general to see if you're experiencing other problems.
If you're using XML, the OPENXML and FOR XML statements within SQL Server are fairly expensive in terms of memory.
You should look at this white paper from Microsoft on tuning your system through waits and queue's. It doesn't sound like you know precisely where the problems lie. This methodology should hlep.
If you want more specific help, you'll need to post the queries, structures, some example code and your actual execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply