> The query is very simple:
> SELECT * FROM TheTable WHERE TheField ='TheValue'

> The number of records is about 200000. TheField is the part of the primary
> key on this table, but not the first field in the primary key. I noticed
> that in 7.0 there is a system index on every field included into primary
> key, so TheField is an indexed field.

> In MSSQL 7 it takes 1-1.5 sec to execute the query even if there are no
> records to return. In 2000 the same query takes milliseconds on the same
> table.

> Is MSSQL 2000 really so much faster then 7.0 in this case, or am I missing
> anything?

> Thanks,
> Alexander

> I have also heard this from other dba's working with both, that 2000 was
not
> as fast...

> Can anyone please either verify this for me or set me straight... I have
to
> know soon if I need to include this move in my budget for next year!<g>

> Thanks,
> -- Raymond
> Raymond J. Schappe
> Isthmus Technology Solutions, LLC
> ______________________________________
> > I haven't noticed that big of an improvement of MSSQL2000 over 7.0 so my
> > guess is you're missing something.  Are there any differences in the
> > execution plans?  I would run DBCC DROPCLEANBUFFERS and re-test.

> In general SQL 2000 is faster than SQL7, but mileage varies:

> SQL2000 is much faster when run on > 8 procs, especially with SP1.
> If you are running Enterprise and do lots of sequential scans then it will
> be much faster because of the merry go round scan optimisation for read
i/o.
> If you choose to re-write to make use features like Indexed views then it
> will be faster.
> Backups are faster and don't affect the perf of the database as much while
> they are happening.
> ... etc etc etc

> In general the most variation in seen in the query processor's generation
of
> query plans. In 7.0 you may have been lucky and a double error may have
> generated a good plan, but it happens by accident. In 2000 the optimiser
is
> much smarter but for any given query it may select a different, more
correct
> plan but could be slower. Thats just the way cost based optimisers work.
> SQL2000's stats collection is much better than 7.0 so it should be better
in
> general.

> -Euan

> Thank you very much for the information!

> Out of curiosity... what do you use to access MS SQL Server??? (ADO, ODBC,
> DBLIB, etc) and what do you use (BDE, dbExpress, etc) on the Delphi

> I still have not had a good chance to review the new MS licensing... but
it
> looks like NT is now officially retired and SQL 7 may be retired as
well...
> and the new licensing costs look a bit steep (especially for small
> companies - ~20 to 30 users)... Linux and Advantage database server is
> looking more interesting all the time!!!

> I would appreciate any ideas you (or anyone) may have!!!

> Thanks,
> -- Raymond
> Raymond J. Schappe
> Isthmus Technology Solutions, LLC
> ______________________________________

> > That may or may not have been what the article said but the report that
> was
> > referred to in the article was not authorised by MS and given the
results
> > they did nto have a good testing procedure.

> > In general SQL 2000 is faster than SQL7, but mileage varies:

> > SQL2000 is much faster when run on > 8 procs, especially with SP1.
> > If you are running Enterprise and do lots of sequential scans then it
will
> > be much faster because of the merry go round scan optimisation for read
> i/o.
> > If you choose to re-write to make use features like Indexed views then
it
> > will be faster.
> > Backups are faster and don't affect the perf of the database as much
while
> > they are happening.
> > ... etc etc etc

> > In general the most variation in seen in the query processor's
generation
> of
> > query plans. In 7.0 you may have been lucky and a double error may have
> > generated a good plan, but it happens by accident. In 2000 the optimiser
> is
> > much smarter but for any given query it may select a different, more
> correct
> > plan but could be slower. Thats just the way cost based optimisers work.
> > SQL2000's stats collection is much better than 7.0 so it should be
better
> in
> > general.

> > -Euan