Suntower
Posts: 225
|
| Posted: 11/29/2011, 12:11 PM |
|
Hi,
Working on an ASP Classic app done with CCS3. A particular report is -very- slow to render and I can't figure it out. Although I'm quite familiar with SQL Server, I am a novice with ASP and IIS.
The report was created with the CCS3 std template---nothing fancy at all.
If I run the SQL Query behind the report from server desktop, it renders in .5 seconds.
If I run the CCS3 report from any web browser? 15 seconds to view 1st page over a 20mbps connection.
The server machine is a Xeon 5130 with 4gb ram.
I've pinged the server and static pages render quite fast.
Is there some sort of 'throttle' in IIS or SQL Server which causes web pages to resolve so much slower than desktop SQL Queries... or is this something more to do with the page rendering aspect of IIS?
In short, what, if anything, can one do to optimise the rendering of at least the 1st page?
TIA,
---JC
_________________
---On a campaign for more examples and better docs! |
 |
 |
datadoit
|
| Posted: 11/29/2011, 1:44 PM |
|
I would suspect it's something to do with your connection from the
application to the database. You can validate that by creating a simple
HTML page and toss in some large images or something to see the speed.
|
|
|
|
Suntower
Posts: 225
|
| Posted: 11/29/2011, 2:50 PM |
|
Quote datadoit:
I would suspect it's something to do with your connection from the
application to the database. You can validate that by creating a simple
HTML page and toss in some large images or something to see the speed.
If you mean, create a static page with some large images, I did that and the response is very good.
So does this imply that the problem is with the ADOB connection to SQL Server? If so, what can I do to improve -that-?
Thanks,
---JC
_________________
---On a campaign for more examples and better docs! |
|
|
|
datadoit
|
| Posted: 11/29/2011, 5:36 PM |
|
Next thing to check would be the networking between the application and
the database servers. DNS resolution perhaps. If they're running on
the same machine, then no need to use a public-resolvable address, use
localhost or the local IP address.
|
|
|
|
Suntower
Posts: 225
|
| Posted: 11/30/2011, 9:32 AM |
|
Quote datadoit:
Next thing to check would be the networking between the application and
the database servers. DNS resolution perhaps. If they're running on
the same machine, then no need to use a public-resolvable address, use
localhost or the local IP address.
OK, the connection string from CodeCharge is...
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=myuser;Initial Catalog=mycatalog;Data Source=KSI01S1"
1. There's no 'server' parameter--so I assume the db is connecting somehow using a Windows login or the app's URL. How do I specify a localhost?
2. I read elsewhere that for SQL 2005 and 2008 one should replace SQLOLEDB to SQLNCLI. I tried that and it didn't help much.
Ideas?
---JC
_________________
---On a campaign for more examples and better docs! |
|
|
|
jsmonkey
Posts: 39
|
| Posted: 12/07/2011, 7:09 AM |
|
Not sure if this is the same or too basic, however, I had some slow reports and this was my issue and fix:
In CCS, I found that ALL of the fields in my main table were being pulled. So, I used the Visual Query builder and only selected the fields I needed to show in the report.
Please post your fix if this is not it.
-JS
_________________
-J |
|
|
|
Suntower
Posts: 225
|
| Posted: 01/25/2012, 5:25 PM |
|
Quote datadoit:
Next thing to check would be the networking between the application and
the database servers. DNS resolution perhaps. If they're running on
the same machine, then no need to use a public-resolvable address, use
localhost or the local IP address.
Can you elaborate a bit more? I posted my connection string in a previous msg and I -thought- that told SQLServer all it needed to access the connection locally (ie. not using an IP).
And to the other poster: The query already uses named fields (no wildcards).
Again: in SQL Management Studio, the query returns all rows in < 1 second. Take 20+ seconds via the Codecharge app (ASP 'classic').
Help!
---JC
_________________
---On a campaign for more examples and better docs! |
|
|
|
Suntower
Posts: 225
|
| Posted: 01/30/2012, 12:10 PM |
|
OK, I did some timings. The recordset opens in < 1 second. But then there is a 'loop' which uses MoveNext to go through each record. And -that- is the slow spot. The code is below. Given this, is there a way to 'optimise' MoveNext for the above query?
Sorry if this is a total noob question. This is fairly alien to me. I am used to MySQL/PHP 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-?
Thanks,
---JC
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
_________________
---On a campaign for more examples and better docs! |
|
|
|
datadoit
|
| Posted: 01/30/2012, 1:07 PM |
|
Just a shot, but how are your indexes looking?
|
|
|
|
Suntower
Posts: 225
|
| Posted: 01/30/2012, 1:32 PM |
|
Quote datadoit:
Just a shot, but how are your indexes looking?
The query follows. Again, this resolves in < 1 sec via SQL Manage Studio.
I have an index on SOHEADERS.CustomerID, SOHEADERS.ShipDate which is the 'filter' and, of course, keys on all related columns. I am not allowed to convert this to a View or do anything that would break the CodeCharge template.
I read all over the Interweb about 'slow MoveNext' and it usually involves changing the cursortype, but CodeCharge already has that covered in Common.ASP.
Ideas?
TIA,
---JC
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
_________________
---On a campaign for more examples and better docs! |
|
|
|
zhucehao
Posts: 7
|
| Posted: 06/07/2012, 7:42 AM |
|
Please post your fix if this is not it.
_________________
|
|
|
|
|
