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.
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.
// 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,
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