IMPORTANT UPDATE ON PERFORMANCE!

I eliminated all existing indexes and constraints on the table and got orders of magnitude better results! I did a select * into socopy from sostrs. Now I had a copy of my table. The copy had no indexes. (I didn't realize select into would do it this way - but it was perfect for my purposes)

On the new table executing update socopy set nreserved = 0 where ccustno='ACME' took milliseconds - query analyzer shows zero elapsed time.

Just as important - executing select * from socopy where ccustno='ACME' took milliseconds also - no degradation whatsoever of select time.

Also, executing 'update socopy set nreserved = 0' now took 3 seconds in query analyzer (for 86K rows)

In vfp local - update sostrs set nreserved = 0 took about 1/2 second for 86k rows. But executing replace all nreserved with 0 took about 3 seconds.

The bottom line is that the ELIMINATION of the other indexes improved UPDATE performance by orders of magnitude and had no impact on SELECT performance. So now I must ask the obvious question - WTF are indexes for in SQL Server? I have seen many posts on here about clustered indexes etc, etc, and the importance of them. I have never really sat down to benchmark things. Now that I have started the process I am totally blown away and confused by it. My results so far don't coincide with what I expected from a theoretical point of view. But I am very new to this performance testing and tuning and benchmarking - so hopefully some gurus out here can guide me.

Perhaps in this case it was something about the nature of the indexes that I had on sostrs in particular, that was/is a specific drag on performance. I need to go back and look at their keys, etc. Again, this is a database that was not created by me - it is Accountmate/SQL accounting software. So, I don't think I can just get rid of all the indexes - I don't know nearly enough about SQL Server to understand the particular purpose for which they were created - and what the impact would be on the system as whole. On the other hand, perhaps I have overestimated the knowledge of the designers of Accountmate - perhaps they just thought they needed all these indexes because the local table product had them?


>I tried this. It suggests that an index on ccustno would improve performance by 10% - if I understood the recommendation correctly. Let me also say that there are about 7 or 8 existing indexes - this is not a database I created. Maybe getting rid of them would improve performance. But I can't believe we are talking 'orders of magnitude' here. The select performance is roughly equivalent - but updating - forget about it.
>
>
>>A number of factors can effect performance on a SQL Server machine. First off, make sure that you have an index on the SQL table matching your WHERE statement. You can use the Index Tuning Wizard in Query Analyzer by typing in your T-SQL statement in a Query Window, then select 'Query','Index Tuning Wizard' from the menu. Follow the prompts and it will determine if an index will improve the performance. Be careful not to add too many indexes since that also can slow down UPDATE/INSERT commands because each index needs to be updated.
>>
>>HTH,
>>Kurt
>>>I am converting an application from vfp local table to sql server. I am not that knowledgeable about sql server performance tuning issues etc.
>>>
>>>On my pentium 4, 1.7 ghz w/ 512 megs of ram here is a comparison of the same update statement:
>>>
>>>my table sostrs has the same columns (about 95%) in both vfp and sql
>>>sostrs has 86000 rows
>>>
>>>vfp - ccustno has an index
>>>update sostrs set nreserved = 0 where CCUSTNO='ACME'
>>>550 rows updated in less than 1/10 of a second
>>>
>>>sql - index on primary key cuid
>>>update sostrs set nreserved = 0 where CCUSTNO='ACME'
>>>550 rows updated in 30 seconds (query performed in query analyzer)
>>>
>>>select * from sostrs where ccustno='ACME'
>>>
>>>takes 1/20 of a second in vfp and 0.00 in query analyzer
>>>
>>>
>>>Obviously vfp is orders of magnitude faster here. How do I tune my sql database for updating?