iis/asp + sql2000 a bit slow

[Florian]

I have a performance problem, but I don't really know where the exact cause
lies.

Win2k (all patches) / IIS / ASP + VBScript

I have a table with about 10 columns and almost 1million of records that I
indexed (with index views). The page I'm talking about is doing 9 queries
(all just select) and takes too long to load.

I ran every single on of these queries with the SQL Query Analyzer, looking
at the execution plan. All queries run superfast, so I'm pretty sure that
the problem is not there.

As soon as I run this from the ASP page however it takes almost 1 sec per
query to run, a total of about 6 seconds. I don't see why this doesn't work
in one second, considering that there is absolutely no load on the server.
So I'm trying to find out where the overhead between ASP and the connection
to the SQL Server is. To eliminate IIS I wrote a test .vbs script and
executed it from the command prompt - it's slow also. There I saw that
executing the query seems to take so long.

Originally the page was accessing the data through ODBC, suspecting that
this might be a problem I changed it to sqloledb. Unfortunately it does not
seem to make a difference (tried named pipes and socket).

Another strange thing is that one of the queries is about twice as slow as
the other ones, even though it's of the exact same nature and it returns
superfast in the Query Analyzer. I also tried to access this view (the slow
one) from a remote computer with ODBC/MS Access and it's slow also.

Anyway, does anybody have an idea what's going on here? The code looks like
this:

strcon ="Provider=sqloledb;Data Source=dbserver;Network
Library=dbnmpntw;User ID=myuser;Password=mypwd;"
'strcon ="Provider=sqloledb;Data Source=127.0.0.1,1433;Network
Library=DBMSSOCN;User ID=myuser;Password=mypwd;"

Set Connection = CreateObject("adodb.connection")
Connection.ConnectionString = strcon
Connection.Open strcon

counter = 0
Set RS = Connection.Execute("select field1,total from view_distinct_field1
where field1 != '' order by total desc")
Do While Not RS.EOF
WScript.Echo RS.Fields("field1")
If counter >= Max Then
Exit Do
End If
counter = counter + 1
RS.MoveNext
Loop

This query is basically repeated 5 times, every time with a different field.
The counter is there so that only a maximum of X rows are returned.

The original way of connecting was:

Set Connection = CreateObject("ADODB.Connection")
ConnectionString = "DSN=" & SQLODBC
If (Len(SQLUser) > 0) Then
ConnectionString = ConnectionString & "; UID=" & SQLUser & "; PWD=" &
SQLPwd
End If

Connection.Open ConnectionString

For those curious, the machine is a P4 2.6GHz with 512Mb of RAM. There is
nothing on this server except for Win2k, IIS and SQL 2000. The machine is
pretty much idle, though there are clients connecting randomely and
inserting data into the table - I'd guess about 1 row every few seconds.

I just can't seem to understand what is delaying this stuff so much, I'll
continue my search with trying maybe PERL or something. What is the SQL
Analyzer doing better than all the other apps?

Thanks for any hints.

[Bob Barrows]

Florian wrote:

> I have a performance problem, but I don't really know where the exact
> cause lies.
>
> Win2k (all patches) / IIS / ASP + VBScript
>
> I have a table with about 10 columns and almost 1million of records
> that I indexed (with index views). The page I'm talking about is
> doing 9 queries (all just select) and takes too long to load.
>
> I ran every single on of these queries with the SQL Query Analyzer,
> looking at the execution plan. All queries run superfast, so I'm
> pretty sure that the problem is not there.
>
> As soon as I run this from the ASP page however it takes almost 1 sec
> per query to run, a total of about 6 seconds. I don't see why this
> doesn't work in one second, considering that there is absolutely no
> load on the server. So I'm trying to find out where the overhead
> between ASP and the connection to the SQL Server is. To eliminate IIS
> I wrote a test .vbs script and executed it from the command prompt -
> it's slow also. There I saw that executing the query seems to take so
> long.
>
> Originally the page was accessing the data through ODBC, suspecting
> that this might be a problem I changed it to sqloledb. Unfortunately
> it does not seem to make a difference (tried named pipes and socket).
>
> Another strange thing is that one of the queries is about twice as
> slow as the other ones, even though it's of the exact same nature and
> it returns superfast in the Query Analyzer. I also tried to access
> this view (the slow one) from a remote computer with ODBC/MS Access
> and it's slow also.
>
> Anyway, does anybody have an idea what's going on here? The code
> looks like this:
>
> strcon ="Provider=sqloledb;Data Source=dbserver;Network
> Library=dbnmpntw;User ID=myuser;Password=mypwd;"
> 'strcon ="Provider=sqloledb;Data Source=127.0.0.1,1433;Network
> Library=DBMSSOCN;User ID=myuser;Password=mypwd;"
>
> Set Connection = CreateObject("adodb.connection")
> Connection.ConnectionString = strcon
> Connection.Open strcon
>
> counter = 0
> Set RS = Connection.Execute("select field1,total from
> view_distinct_field1 where field1 != '' order by total desc")
> Do While Not RS.EOF
> WScript.Echo RS.Fields("field1")

WScript.Echo? I thought your were using ASP ...


I suspect that you are suffering the consequences of failing to encapsulate
your queries into a stored procedure. You're doing too much processing in
the client.

Your bottleneck is not the queries: it is the time needed to send the
queries and return their results across the network. In addition, recordset
loops are very expensive: you should use GetRows to stuff your data into
arrays and loop through the arrays instead. You will see a great increase in
performance if you follow these guidelines.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

In addition to Bob's points, see [url]http://www.aspfaq.com/2424#db[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Florian" <REMOVEUPPERCASEwizard_oz@gmx.net> wrote in message
news:wdUUb.17021$uM2.8228@newsread1.news.pas.earth link.net...

> I have a performance problem, but I don't really know where the exact

cause

> lies.
>
> Win2k (all patches) / IIS / ASP + VBScript
>
> I have a table with about 10 columns and almost 1million of records that I
> indexed (with index views). The page I'm talking about is doing 9 queries
> (all just select) and takes too long to load.
>
> I ran every single on of these queries with the SQL Query Analyzer,

looking

> at the execution plan. All queries run superfast, so I'm pretty sure that
> the problem is not there.
>
> As soon as I run this from the ASP page however it takes almost 1 sec per
> query to run, a total of about 6 seconds. I don't see why this doesn't

work

> in one second, considering that there is absolutely no load on the server.
> So I'm trying to find out where the overhead between ASP and the

connection

> to the SQL Server is. To eliminate IIS I wrote a test .vbs script and
> executed it from the command prompt - it's slow also. There I saw that
> executing the query seems to take so long.
>
> Originally the page was accessing the data through ODBC, suspecting that
> this might be a problem I changed it to sqloledb. Unfortunately it does

not

> seem to make a difference (tried named pipes and socket).
>
> Another strange thing is that one of the queries is about twice as slow as
> the other ones, even though it's of the exact same nature and it returns
> superfast in the Query Analyzer. I also tried to access this view (the

slow

> one) from a remote computer with ODBC/MS Access and it's slow also.
>
> Anyway, does anybody have an idea what's going on here? The code looks

like

> this:
>
> strcon ="Provider=sqloledb;Data Source=dbserver;Network
> Library=dbnmpntw;User ID=myuser;Password=mypwd;"
> 'strcon ="Provider=sqloledb;Data Source=127.0.0.1,1433;Network
> Library=DBMSSOCN;User ID=myuser;Password=mypwd;"
>
> Set Connection = CreateObject("adodb.connection")
> Connection.ConnectionString = strcon
> Connection.Open strcon
>
> counter = 0
> Set RS = Connection.Execute("select field1,total from view_distinct_field1
> where field1 != '' order by total desc")
> Do While Not RS.EOF
> WScript.Echo RS.Fields("field1")
> If counter >= Max Then
> Exit Do
> End If
> counter = counter + 1
> RS.MoveNext
> Loop
>
> This query is basically repeated 5 times, every time with a different

field.

> The counter is there so that only a maximum of X rows are returned.
>
> The original way of connecting was:
>
> Set Connection = CreateObject("ADODB.Connection")
> ConnectionString = "DSN=" & SQLODBC
> If (Len(SQLUser) > 0) Then
> ConnectionString = ConnectionString & "; UID=" & SQLUser & "; PWD=" &
> SQLPwd
> End If
>
> Connection.Open ConnectionString
>
> For those curious, the machine is a P4 2.6GHz with 512Mb of RAM. There is
> nothing on this server except for Win2k, IIS and SQL 2000. The machine is
> pretty much idle, though there are clients connecting randomely and
> inserting data into the table - I'd guess about 1 row every few seconds.
>
> I just can't seem to understand what is delaying this stuff so much, I'll
> continue my search with trying maybe PERL or something. What is the SQL
> Analyzer doing better than all the other apps?
>
> Thanks for any hints.
>
>

[Florian]

> WScript.Echo? I thought your were using ASP ...

Well, as I pointed out I was putting the crucial code into a .vbs file to
make sure IIS is not the cause of the problem, hence the WScript.Echo

> I suspect that you are suffering the consequences of failing to

encapsulate

> your queries into a stored procedure. You're doing too much processing in
> the client.

I have never done anything with stored procedures and I'm not sure why 6
"easy" queries are considered to be too much processing? But I will look
into how stored procedures work and try that. I usually code my web pages in
perl/mysql and I see why ;-) Can't do in this example though ...

> Your bottleneck is not the queries: it is the time needed to send the
> queries and return their results across the network. In addition,

recordset

> loops are very expensive: you should use GetRows to stuff your data into
> arrays and loop through the arrays instead. You will see a great increase

in

> performance if you follow these guidelines.

I actually converted the test script to use GetRows and that didn't seem to
help very much, if at all.


Thanks so far!!!

[Florian]

Thanks, looks like a nice site though I haven't been able to improve it
quite yet ... :(

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:euHN57Q7DHA.1596@TK2MSFTNGP10.phx.gbl...

> In addition to Bob's points, see [url]http://www.aspfaq.com/2424#db[/url]
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "Florian" <REMOVEUPPERCASEwizard_oz@gmx.net> wrote in message
> news:wdUUb.17021$uM2.8228@newsread1.news.pas.earth link.net...

> > I have a performance problem, but I don't really know where the exact

> cause

> > lies.
> >
> > Win2k (all patches) / IIS / ASP + VBScript
> >
> > I have a table with about 10 columns and almost 1million of records that

I

> > indexed (with index views). The page I'm talking about is doing 9

queries

> > (all just select) and takes too long to load.
> >
> > I ran every single on of these queries with the SQL Query Analyzer,

> looking

> > at the execution plan. All queries run superfast, so I'm pretty sure

that

> > the problem is not there.
> >
> > As soon as I run this from the ASP page however it takes almost 1 sec

per

> > query to run, a total of about 6 seconds. I don't see why this doesn't

> work

> > in one second, considering that there is absolutely no load on the

server.

> > So I'm trying to find out where the overhead between ASP and the

> connection

> > to the SQL Server is. To eliminate IIS I wrote a test .vbs script and
> > executed it from the command prompt - it's slow also. There I saw that
> > executing the query seems to take so long.
> >
> > Originally the page was accessing the data through ODBC, suspecting that
> > this might be a problem I changed it to sqloledb. Unfortunately it does

> not

> > seem to make a difference (tried named pipes and socket).
> >
> > Another strange thing is that one of the queries is about twice as slow

as

> > the other ones, even though it's of the exact same nature and it returns
> > superfast in the Query Analyzer. I also tried to access this view (the

> slow

> > one) from a remote computer with ODBC/MS Access and it's slow also.
> >
> > Anyway, does anybody have an idea what's going on here? The code looks

> like

> > this:
> >
> > strcon ="Provider=sqloledb;Data Source=dbserver;Network
> > Library=dbnmpntw;User ID=myuser;Password=mypwd;"
> > 'strcon ="Provider=sqloledb;Data Source=127.0.0.1,1433;Network
> > Library=DBMSSOCN;User ID=myuser;Password=mypwd;"
> >
> > Set Connection = CreateObject("adodb.connection")
> > Connection.ConnectionString = strcon
> > Connection.Open strcon
> >
> > counter = 0
> > Set RS = Connection.Execute("select field1,total from

view_distinct_field1

> > where field1 != '' order by total desc")
> > Do While Not RS.EOF
> > WScript.Echo RS.Fields("field1")
> > If counter >= Max Then
> > Exit Do
> > End If
> > counter = counter + 1
> > RS.MoveNext
> > Loop
> >
> > This query is basically repeated 5 times, every time with a different

> field.

> > The counter is there so that only a maximum of X rows are returned.
> >
> > The original way of connecting was:
> >
> > Set Connection = CreateObject("ADODB.Connection")
> > ConnectionString = "DSN=" & SQLODBC
> > If (Len(SQLUser) > 0) Then
> > ConnectionString = ConnectionString & "; UID=" & SQLUser & "; PWD=" &
> > SQLPwd
> > End If
> >
> > Connection.Open ConnectionString
> >
> > For those curious, the machine is a P4 2.6GHz with 512Mb of RAM. There

is

> > nothing on this server except for Win2k, IIS and SQL 2000. The machine

is

> > pretty much idle, though there are clients connecting randomely and
> > inserting data into the table - I'd guess about 1 row every few seconds.
> >
> > I just can't seem to understand what is delaying this stuff so much,

I'll

> > continue my search with trying maybe PERL or something. What is the SQL
> > Analyzer doing better than all the other apps?
> >
> > Thanks for any hints.
> >
> >

>
>

[Bob Barrows]

Florian wrote:

>> WScript.Echo? I thought your were using ASP ...

>
> Well, as I pointed out I was putting the crucial code into a .vbs
> file to make sure IIS is not the cause of the problem, hence the
> WScript.Echo
>

>> I suspect that you are suffering the consequences of failing to
>> encapsulate your queries into a stored procedure. You're doing too
>> much processing in the client.

>
> I have never done anything with stored procedures and I'm not sure
> why 6 "easy" queries are considered to be too much processing?

The difference is that with a stored procedure you send one command to the
database server, which runs the stored procedure which does the work needed
to be done by the 6 queries and returns a single set of results which you
process on the client. One trip across the network (and across process
boundaries) vs six: there's the difference.

I'm sure if you use SQL Profiler to trace the database server activity, you
will find that your six queries are executing just fine. It's the traffic
to-and-from the database, combined with the processing of results in the
client each time, that is causing the bottleneck.

I suggest that you stay with sqloledb:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated_components.asp[/url]

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Florian]

> The difference is that with a stored procedure you send one command to the

> database server, which runs the stored procedure which does the work

needed

> to be done by the 6 queries and returns a single set of results which you
> process on the client. One trip across the network (and across process
> boundaries) vs six: there's the difference.

Allright, sounds fair enough to me! I just didn't think that this would make
a difference since I'm running the tests on the local server where the DB
is - so the network shouldn't really be an issue - though I understand that
six calls and six times processing might make a difference. Right now the
script loads with the same speed whether it's local or remote.

> I'm sure if you use SQL Profiler to trace the database server activity,

you

> will find that your six queries are executing just fine. It's the traffic
> to-and-from the database, combined with the processing of results in the
> client each time, that is causing the bottleneck.

Sounds to me like ASP & VB are the bottleneck :-))

> I suggest that you stay with sqloledb:
>

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/ado_deprecated_components.asp[/url]

Thanks for the hint, I'll probably have to look into this more in the
future. The problem is that this web page is supposed to run on any SQL
Server (e.g. Oracle, SQL, MySQL, ...) and we've always been using ODBC since
performance was not a big issue (except for these pages of course :-) ).


Thanks!

[Florian]

> The difference is that with a stored procedure you send one command to the

> database server, which runs the stored procedure which does the work

needed

> to be done by the 6 queries and returns a single set of results which you
> process on the client. One trip across the network (and across process
> boundaries) vs six: there's the difference.

OK, I couldn't help but try out to connect to the SQL DB via ODBC from a
remote machine (using PHP on Apache) only to realize that it takes about the
same time ... so I suppose I'll be learning stored procedures now :)

Thanks.

[Florian]

> The difference is that with a stored procedure you send one command to the

> database server, which runs the stored procedure which does the work

needed

> to be done by the 6 queries and returns a single set of results which you
> process on the client. One trip across the network (and across process
> boundaries) vs six: there's the difference.

Just FYI, I am getting closer to finding out where the problem is. It's not
ASP, ODBC, record sets etc. - I suspect that it has something to do with SQL
Server. I know this is not the right place to post this but I'll mention it
anyway.

I create the stored procedure as you suggested (which was less complex than
I thought btw) and this is what happens. As I might have mentioned, I'm
running 6 SQL queries against the same table but for a different field every
time. I created indexed views to speed even that up (and that should be
fine).

I am sitting on a client here using SQL Query Analyzer, connected to the SQL
Server. When I put all the 6 queries into query analyzer like this

select source from view ........ ;
select computer from view .....;
....
select type from view ....;

then all 6 queries execute in less than one second - it's literally there
right away.

Then I create a stored procedure that does EXACTLY the same thing. And guess
what, it takes 4 seconds. Not sure what that's all about, all I see is that
with the stored procedure the CPU util goes up to 100% for those 4 seconds,
when I run the queries just sequentially it goes up to 10% for the part of
the second.

Seems like SQL server is doing something special in the stored procedure ...
I'll read and post in another group to see if there is something else to
optimize. So far I feel optimized out :)

Thanks.

[Aaron Bertrand [MVP]]

> Then I create a stored procedure that does EXACTLY the same thing. And
guess

> what, it takes 4 seconds.

Are you passing parameters to the stored procedure?

If you show your actual code (I've heard more than enough narrative
already), people might be able to get a better clue than what's going on.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Florian]

> Are you passing parameters to the stored procedure?

>
> If you show your actual code (I've heard more than enough narrative
> already), people might be able to get a better clue than what's going on.

Sure, no problem. The queries are so simple though which is why I didn't
it'd make a difference. Here is the stored procedure:

CREATE PROCEDURE GetTop

AS

SELECT TOP 10 field1,total from dbo.view_distinct_field1 where field1 != ''
order by total desc
SELECT TOP 10 field2,total from dbo.view_distinct_field2 where field2 != ''
order by total desc
SELECT TOP 10 field3,total from dbo.view_distinct_field3 where field3 != ''
order by total desc
SELECT TOP 10 field4,total from dbo.view_distinct_field4 where field4 != ''
order by total desc
SELECT TOP 10 field5,total from dbo.view_distinct_field5 where field5 != ''
order by total desc
SELECT TOP 10 field6,total from dbo.view_distinct_field6 where field6 != ''
order by total desc

RETURN
GO

The table has the following fields:

field0 int
field1 varchar(64)
field2 varchar(32)
field3 varchar(128)
field4 varchar(128)
field5 varchar(128)
field6 varchar(32)
field7 datetime
field8 text

It's a table that has a lot of rows, currently around 800.000. It's just log
data, so every field has different data of the log entries. To make the
distinct queries faster I created indexed views for field1 - field6 which
look like this:

CREATE VIEW dbo.view_distinct_field1
WITH SCHEMABINDING
AS
SELECT field1, COUNT_BIG (*) AS total
FROM dbo.MyTable
GROUP BY field1

The other views look exactly the same, just substitute field1 with field2
etc. That's all there is, nothing else.

Now as I mentioned, running the stored procedure takes about 4 seconds,
whereas running the the 6 queries in SQL Query Analyzer takes less than 1
second.

So in Query Analyzer executing this:

execute GetTop

takes 4 seconds, but executing this:

SELECT TOP 10 field1,total from dbo.view_distinct_field1 where field1 != ''
order by total desc
SELECT TOP 10 field2,total from dbo.view_distinct_field2 where field2 != ''
order by total desc
SELECT TOP 10 field3,total from dbo.view_distinct_field3 where field3 != ''
order by total desc
SELECT TOP 10 field4,total from dbo.view_distinct_field4 where field4 != ''
order by total desc
SELECT TOP 10 field5,total from dbo.view_distinct_field5 where field5 != ''
order by total desc
SELECT TOP 10 field6,total from dbo.view_distinct_field6 where field6 != ''
order by total desc

takes less than 1 second, immediately returns lightning fast ;) I have
absolutely no idea why, especially since stored procedures are supposed to
be faster.

One thing I *just* realized in the execution path is that the stored
procudure doesn't seem to use the clustered index, but instead does a full
row scan ... ? I'll post updates on my findings soon, no idea why it does
that ...

Thanks ...

[Florian]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:%23EaS40b7DHA.488@TK2MSFTNGP12.phx.gbl...

> > Then I create a stored procedure that does EXACTLY the same thing. And

> guess

> > what, it takes 4 seconds.

>
> Are you passing parameters to the stored procedure?
>
> If you show your actual code (I've heard more than enough narrative
> already), people might be able to get a better clue than what's going on.
>
> --

I'm getting closer here and eliminating problems one at a time. The stored
procedure was slow in SQL Query Analyzer because it was apparently created
"wrong". If I remove the stored procedure and then recreate from within SQL
Query Analyzer with the following commands:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetTop
...
...
GO

Then SUDDENLY it works. I mean the SP works fine all of a sudden, returning
as fast as if I run the 6 queries sequentially. GREAT I thought and
immediately changed the web page to use the SP. Well, apparently this is not
having any effect on the asp page, as it's still dog slow, no difference. I
used recordset and GetRows and they're both equally slow. For some reason
the stored procedure, when run from ASP/SQLOLEDB can't make use of the
clustered indexed view. Why? I don't know.

I tried executing the parameters "SET ANSI_NULLS ..."

Set RS = Connection.Execute("SET ANSI_NULLS ON")
Set RS = Connection.Execute("SET QUOTED_IDENTIFIER ON")

before I executed the stored procedure, but it didn't seem to have any
effect. I'm kinda stuck now, still trying to find something but this is
definitely odd.

Any other hints are appreciated, I'll look into some connection tweakings
for SQLOLEDB, maybe there is yet something else one can do.


thanks ...

[Florian]

OK, I have, finally and after spending much more time than I hoped, solved
the problem and the page displays in a fraction of a second now. The source
of the problem was that the previous sql queries did never take advantage of
the indexed views, for various reasons.

I'd like to summarize, just in case anybody runs into a similar problem
using indexed views and stored procedures.

1) First I optimized the queries by creating an indexed clustered view with
the group by option. This way I got the unique values from all rows without
having to run a distinct query which would require a full row scan for every
field.

Seems as if it's helpful to run these commands before creating the views in
SQL Query Analyzer though I didn't notice a difference to creating them in
Enterprise Manager manually (without these options set).

SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS ON
GO

2) I created a stored procedure from within SQL Query Analyzer and NOT from
enterprise manager. It is important to note that the stored procedure needs
to be created from SQL Query Analyzer with the ANSI_NULLS and the
QUOTED_IDENTIFIER options activated, for example:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE MyProcedure
AS
BEGIN
...
END
GO

I'd like to note while it's definitely beneficial to create a stored
procedure, it was not the reason for the delay. The first two SET options
seem to be very important though, this was the thing that ultimately sped
the SP up in Query Analyzer.

3) I changed the ASP page to not use DNS names but instead use SQLOLEDB.

4) I changed the asp page to use GetRows() instead of Recordsets. Again,
this is definitely helpful but doesn't seem to have been the cause of the
problem.

5) I added the line

Set RS = Connection.Execute("SET ARITHABORT ON")

which does the trick. Why? I don't know. I experimented by taking this line
out, after I had created the views with this ARITHABORT set to on, but then
it became slow again. As soon as I put this in, the things speeds up like
crazy! So, all the steps 1-4 didn't have any effect in the ASP page unless I
set ARITHABORT on.

I personally don't think that the ODBC really is the cause for slowdown,
instead I think that I haven't figured out how to make the ODBC connection
set this option - or mabye it's not supported at all. I'm saying this
because the page, when using ODBC, behaves just like the SQLOLEDB without
this activated. Makes sense?

Frankly I think this setting should be called USEINDEXEDVIEWS. I spent quite
a few hours trying to figure this out, probably that many because I'm no DBA
and usually don't do more on databases than set them up and run some select
queries. This was the first time I was exposed to this.

Nevertheless I am more than suprised that this ARITHABORT has the mentioned
effect. According to the reference, ARITHABORT

"Terminates a query when an overflow or divide-by-zero error occurs during
query execution."

.. However, it also states that

"SET ARITHABORT must be ON when creating or manipulating indexes on computed
columns or indexed views. If SET ARITHABORT is OFF, CREATE, UPDATE, INSERT,
and DELETE statements on tables with indexes on computed columns or indexed
views will fail. For more information about required SET option settings
with indexed views and indexes on computed columns, see Considerations When
Using SET Statements in SET."

Well, I did specify this setting once I recreated the indexed views, but I
still had to set it in the ASP page. Well, it works now and I sure learned a
lot trying to figure this thing out, I was close to giving up and spend the
next few days ranting about SQL Server. Well, seems like I still have to
rant. SQL Server is easy to setup and administer, no doubt, but once you do
something a bit out of the order you're screwed. I might not understand the
underlying database, but when I create an indexed view then I expect this
indexed view to work without activating cryptic settings - but that's just
me.

I'm curious to try the same thing out on other databases like Oracle and
MySQL - once stored procedures and such are support on MySQL that is.

Thanks everybody for their help, it's appreciated.