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?