Hi,

I am an ASP novice. My background is in MySQL and PHP. Unfortunately, I am tasked with trying to figure out why an ASP 'classic' web page is running -very- slowly. The actual server, running IIS6 and Win 2003 Server is 3000 miles away. I'm connecting to it via Remote Desktop.

The web page executes a fairly simple SQL 2008 query of sales history---it selects a dozen or so named columns from a transaction table with inner joins to 6 columns from 2 'header' tables. I include it below.

When I execute the query via SQL Management Studio it returns 15,000 rows in < 1 sec.

When I run the web page---using that same query, it takes over 30 seconds.

The connection string on the web page is:

Provider=SQLNCLI;Server=localhost;Persist Security Info=False;User ID=test;Initial Catalog="ourcatalog";Data Source=KSI01S1"

Although the environment is SQL 2008, the database was imported from SQL2k and apparently is in some sort of 'compatibility' mode---so that it can still be 'read' by SQL 2k? I don't know if that makes a difference or not.

I did some timings and found that the bottle next is in the 'loop' for MoveNext. This is fairly alien to me. I am used to MySQL where one generally retrieves the recordset in one go into an Array. I'm assuming there is a cursor involved, right? Is there a way to optimise -that-?

I checked the code further the RecordSet opens with a readonly forward only cursor type.

Any ideas?

TIA,

---JC

// Select Statement
SELECT SODETAILS.PRODUCTID AS SODETAILS_PRODUCTID,
SODETAILS.DESCRIPTION AS SODETAILS_DESCRIPTION,
ORDERQUANTITY, SHIPQUANTITY, SODETAILS.UNITPRICE AS SODETAILS_UNITPRICE,
EXTENDEDPRICE, SODETAILS.SELLUOFM AS SODETAILS_SELLUOFM,
SODETAILS.FREIGHTPRICE AS SODETAILS_FREIGHTPRICE,
SODETAILS.ORDERTYPE AS SODETAILS_ORDERTYPE,
SOHEADERS.TRANSACTIONID AS SOHEADERS_TRANSACTIONID, USERID,
ORDERSTATUS, ORDERDATE,
SHIPDATE, COSTCENTERID, SOHEADERS.CONTACTID AS SOHEADERS_CONTACTID,
FORMNUMBER, SPECSTYLE, QuickReleaseCategory,
FOB, SODETAILS.ContactID AS SODETAILS_ContactID, onlineordertype,
SOHEADERS.CUSTOMERID AS SOHEADERS_CUSTOMERID
FROM (SODETAILS INNER JOIN SOHEADERS ON SODETAILS.TRANSACTIONID = SOHEADERS.TRANSACTIONID)
INNER JOIN PRODUCTS ON SODETAILS.PRODUCTID = PRODUCTS.PRODUCTID
WHERE(SOHeaders.CustomerID LIKE('WMUSA%')) and SOHEADERS.SHIPDATE >= 73000


// Loop MoveNext
Do While Not Recordset.EOF
PRODUCTID.Value = Recordset.Fields("PRODUCTID")
DESCRIPTION1.Value = Recordset.Fields("DESCRIPTION1")
COSTCENTERID.Value = Recordset.Fields("COSTCENTERID")
SODETAILS_ORDERTYPE.Value = Recordset.Fields("SODETAILS_ORDERTYPE")
SOHEADERS_CUSTOMERID.Value = Recordset.Fields("SOHEADERS_CUSTOMERID")
ORDERID.Value = Recordset.Fields("ORDERID")
SHIPQUANTITY.Value = Recordset.Fields("SHIPQUANTITY")
UNITPRICE.Value = Recordset.Fields("UNITPRICE")
SELLUOFM.Value = Recordset.Fields("SELLUOFM")
SODETAILS_FREIGHTPRICE.Value = Recordset.Fields("SODETAILS_FREIGHTPRICE")
EXTENDEDPRICE.Value = Recordset.Fields("EXTENDEDPRICE")
INVOICE.Value = Recordset.Fields("INVOICE")
SHIPDATE.Value = Recordset.Fields("SHIPDATE")
TotalSum_EXTENDEDPRICE.Value = Recordset.Fields("TotalSum_EXTENDEDPRICE")
Report_CurrentDateTime.Value = Recordset.Fields("Report_CurrentDateTime")
Report_CurrentPage.Value = Recordset.Fields("Report_CurrentPage")
Recordset.MoveNext
Loop