🚀 go-pugleaf

Hi,

We're encountering a performance degradation of SQL 7.0 in comparison tot
SQL 6.5.
After upgrading SQL 6.5 to 7.0 and applying SP1, we notice that SQL 7.0 is
up to 800% slower than 6.5 (using the same machine (SQL server switch))
with some queries, mostly with table scans.
Consider the following code:

-- Set up the benchmark environment with a table containing 10000 records
create table test ( data int null )
declare @tmp int
select @tmp=1
while @tmp <= 10000
begin
  insert test values(@tmp)
  select @tmp=@tmp+1
end

-- This is the interesting part, namely the benchmark itself
declare @nu datetime
select @nu=getdate()
select * from test where data=100  -- finds record no. 9900 by using a
table scan
select datediff(ms,@nu,getdate())

This benchmark shows the following response times:
SQL 6.5:	10ms
SQL 7.0	80ms  (=800% slower!)

Used hardware: 	Pentium 200, 72MB RAM, 1.2GB IDE disk
Used software: 	NT Server 4.0 with SP4
		SQL 6.5 with SP5
		SQL 7.0 (MSDN april 1999) with SP1
I'm using both SQL server versions on the same machine using the SQL Server
switch to switch between versions.
The same situation on another machine with 128MB of memory shows the same
results
Benchmarking is done in the master database, so SQL 6.5 compatibility mode
is not involved.
The same degradation is showing in the converted database where some stored
procedures that are using filtered data (where clause containing non
indexed columns) are slowing down from 2 secondsoin SQL 6.5 to 11 seconds
on SQL 7.0 !

I've tried different SQL server configuration options with memory, updating
statistics, keep plan etc. but nothing makes any difference; it looks like
SQL 7 has lost the ability to execute a efficient table scan.

Does anybody have a clue ?