SQL Server Performance Forum – Threads Archive

upgrading sql server 7.0 to sql server 2000

hi,
i have an visual basic programme using Sql Server 7.0.
i can do some operations 4 times in a second.
i should increase this number to 6 or more.
is it possible to increase the speed this much by upgrading sql server 2000 ?
or should Ã½ try to optimize sql server 7.0 settings? thanks

It depends on the type of query involved.
Certainly you will get optimum performance on SQL 2K as compared to ver.7.
BTW, what is the query involved. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

in fact Query is not so complicated.
since the problem is in miliseconds i don t know which one is slow.
i am using winsock.and transfer data 4 times in a second but it should be minimum 6.
i am sending the data according to this query: select top 1 * from A where
(a=1 and b=2 and c in (" & sstring1 & ") and (d=1 or (d=0 and e in (" & sstring2 & ")
and x in (select x from B where B.a=1 and B.b=0))) and f<>-1 and g in (" & sstring3 & ")) order by a,b and insert the reply message with insert into statement. some of my friends told me that i can increase the speed by using SQL server 2000. thanks..

Check query execution plan from query analyzer and how about indexes on the queried table. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

True about SQL2k, but I suggest to work in performance in current version, like Satya said, before to upgrade. All work in that direction will work in SQL2k, so is not waisting time.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

i searched the indexes from Query Analyser by ‘Perform Ãndex Analysis’
and add a new index which query analser suggested.but it didn’t help.
it is still slow.
can you suggest anything else with sql server 7.0?

How big is the database and userbase?
How about dbcc checks, maint.plans for this database?
Any other applications are sharing SQL server resources?
What is the level of service pack on SQL & OS? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Yes, run update statistics (sp_updatest) with fullscan. You can run without fullscan, but in non production time I suggest to run fullscan.
Also see how defragmented your index are.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

thanks for your answer,
what do you mean by ‘fullscan’.
can you explain more? thanks

Sorry my mistake. With sp Syntax sp_updatestats [[@resample =] ‘resample’] Return Code Values 0 (success) or 1 (failure) Arguments [@resample=] ‘resample' Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS command. New statistics will inherit the sampling ratio from the old statistics. If ‘resample’ is not specified, sp_updatestats updates statistics using the default sampling. This parameter is varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> with a default value of ‘NO’. if you want table by table: UPDATE STATISTICS table | view [ index | ( statistics_name [ ,…n ] ) ] [ WITH [ [ FULLSCAN ] | SAMPLE number { PERCENT | ROWS } ] | RESAMPLE ] [ [ , ] [ ALL | COLUMNS | INDEX ] [ [ , ] NORECOMPUTE ] ] FULLSCAN Specifies that all rows in table or view should be read to gather the statistics. FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. FULLSCAN cannot be used with the SAMPLE option. HTH Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. Bertrand Russell

]]>