Major performance problem using IIS/ADO/SQL2000

Major performance problem using IIS/ADO/SQL2000

Post by Per Butschko » Tue, 03 Apr 2001 05:18:16



Hi.

I have a major problem. I decided that it was time to upgrade MDAC on our 2
webservers, and ran an installation on both servers. However, to my surpise,
a major problem has occured.

Both servers runs applications, that uses a SQL2000 database. When making
quiries using a single connection to a single recordset, everything works as
usual. But when making f.ex.:

RS = Conn.Execute("SELECT * FROM DB-1")
Do While Not RS.EOF
    RS2 = Conn.Execute("SELECT * FROM DB-2")
    RS2.Movenext
Loop
RS2.Close

- i can expect up to 1 minute waitingtime, no matter how few records are in
both recordssets. The query time before upgrading MDAC was
1 second !!!

It seems that nested queries are almost impossible.

Can anybody help???

Kind regards,
Per Butschkow

 
 
 

1. MAJOR Performance problems: ADO/SQL2K/COM+

Hi - I hope someone out there can shed some light on a very strange
problem I'm having. I've got an n-tier COM+ application for an ASP web
site, which runs just fine on our development servers but grinds to a
halt on our staging servers. This is a major bummer, since that's
where the clients get to see it!

The overall architecture is this: one IIS machine running ASP and the
COM+ layers, one SQL server machine running SQL2K. The COM+ tier is
partitioned into a business tier as a COM+ server app and a generic
data access layer provided as a COM+ library app. The DAL executes
stored procedures and returns disconnected firehose recordsets.

This all runs super fast on the dev boxes, but super slow on staging.
Admittedly they're inferior hardware, but I wouldn't expect this...

I've done some execution tracing and found out something rather odd:
it's taking on average 3 seconds just to CREATE the ADO objects!! The
code in question is:

Dim rs As ADODB.Recordset, cmd As ADODB.Command
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

' Init the ADO objects  & the stored proc parameters
cmd.ActiveConnection = ConnectionString
cmd.CommandText = SPName
cmd.CommandType = adCmdStoredProc

Anyone have any idea what could make this simple bit of code take SO
LONG? I'd really appreciate any advice.

Dunc

2. E_US125D Ambiguous replace???

3. Major problem with performance, transaction log is used ALL the time

4. is not supported by the provider

5. using SQL2000 function performance problems

6. Suggestions on supporting SQL Database from Remote location

7. ADO Update problems MAJOR PROBLEMS !!

8. Problems with Ingres on Sequent!

9. Major Performance Problems

10. Major performance problems with SP2

11. Major Performance Problem - SQL 7.0

12. Major Performance Degradation problem

13. Major performance problem with Nulls?