Help: Asp loading time
-
I have an asp home page has 2 major functions: One function should query about 500 records from a database, format and display the records;
The second function queries about 400 records and only displays 10 latest according to a certain flag.The problem is that the page takes too long to load. I have the display in tables and a few cached images. I have tested the asp separately and the loading time is not very bad but when i incorporate the function in htm/asp page it takes ages.
Can anyone help????
-
Err, you're doing data access in the ASP page itself? To put it
midly, that's probably not the best of ideas. Though it won't
help your performance problems, you should split the data access stuff
into a seperate layer.
You really need to give some more information. What database are
you using? Can you post your SQL so that people can take a look
at it? Databases aren't my forte, but I'm sure someone around
here can help.
-
Finally i have managed to work my way around the asp. At least i have a processing speed of 1.5 sec on both functions. The rest i leave to the layout designer.
-
@human said:
Finally i have managed to work my way around the
asp. At least i have a processing speed of 1.5 sec on both
functions. The rest i leave to the layout designer.
That's still not at all fast for what you're trying to do. What database are you using?
-
@human said:
Finally i have managed to work my way around the
asp. At least i have a processing speed of 1.5 sec on both
functions. The rest i leave to the layout designer.
I'm glad you found a (partial) solution, but why not share it with us?
I find it quite rude to post a question then say "nevermind, I figured
it out myself" without giving back to others who were trying to help
you.
-
<FONT face=Verdana size=1>Ok guys, sorry for being rude. Here is the code. Im using a mysql dabase. These are the 2 functions running on home page.</FONT>
<FONT face=Verdana color=#ff1493 size=1>Function display(strPageId,strDisplStyle)
'[open database]</FONT><FONT face=Verdana color=#ff1493 size=1> Sql="SELECT * FROM table1"
SET rst=strConnect.Execute(Sql)</FONT><FONT face=Verdana color=#ff1493 size=1> While Not dbObjRst.eof
If fld2="1" then
recordsArray(0,intRecCount) = rst("FldId")
elseIf fld2="2" then
recordsArray(1,intRecCount) = countrecs("table2")
end If
'[increment array counter]
intRecCount=intRecCount+1</FONT><FONT face=Verdana color=#ff1493 size=1> rst.MoveNext
Wend
rst.close: Set rst=Nothing
writerecords intRecCount,recordArray
End Function</FONT><FONT face=Verdana color=#ff1493 size=1>Function lastUpdates(intRecsNumber)
'[declarations]</FONT><FONT face=Verdana color=#ff1493 size=1> Sql="SELECT * FROM table3 ORDER BY recDate DESC;"</FONT>
<FONT face=Verdana color=#ff1493 size=1> rs.CursorLocation = adUseClient
rs.Open Sql, strConnect, adOpenStatic, adLockReadOnly
fieldsCount=rs.fields.count
intCounter = rs.RecordCount
If Not intCounter = 0 Then arlsts = rs.GetRows()
rs.close: set rs=nothing
'[loop found rows]
For r=0 to intCounter-1
'[exit loop after desired records]
If cINT(r)=cINT(intRecsNumber) Then Exit Function</FONT><FONT face=Verdana color=#ff1493 size=1> '[display record in selected HTML format]
displayrec arlsts (11, cINT(r))
Next
End Function</FONT><FONT face=Verdana size=1>Both functions process like 500 records.</FONT>
-
@rsynnott said:
@human said:
Finally i have managed to work my way around the asp. At least i have a processing speed of 1.5 sec on both functions. The rest i leave to the layout designer.
That's still not at all fast for what you're trying to do. What database are you using?If there is a faster way,,,,im listening. The db is mysql
-
@human said:
@rsynnott said:
@human said:
Finally i have managed to work my way around the asp. At least i
have a processing speed of 1.5 sec on both functions. The rest i
leave to the layout designer.
That's still not at all fast for what you're trying to do. What database are you using?If there is a faster way,,,,im listening. The db is mysql
Erk. Bear in mind that I'm scared of ASP, (and becoming more so), and my db experience is about three weeks of building a SQLite database for fun.
<font> Sql="SELECT * FROM table1"
SET rst=strConnect.Execute(Sql)</font><font> While Not dbObjRst.eof
If fld2="1" then
recordsArray(0,intRecCount) = rst("FldId")
elseIf fld2="2" then
recordsArray(1,intRecCount) = countrecs("table2")
</font><font>I guess fld2 is shorthand for field2 so couldn't you just do
</font><font>"SELECT fld2 FROM table" instead? That'll give a speed improvement.
Or, you even change your if/elseif to
SELECT FldID FROM table1 WHERE Fld2 = 1
</font><font>(I'm assuming Fld2 is either a 1 or 2...) Hell, where does "FldId" come from? Where does it go? Where does recordsArray come from? Double erk. Mystery code.
</font><font>But generally, I know that for speed, you don't use SELECT * FROM TABLEx
</font><font>(You could also probably obtain a record count on a table from the metadata most databases store about themselves. Have a look at MySQL's SHOW commands....
</font><font>In SQLite it's SELECT index FROM SQLITE_Master WHERE TYPE = 'indexes' AND TBL_NAME = "table1" ... so I can't be too helpful on that.)
</font><font>
</font>
-
Ok, stop with the Erk now, this is sample code.
<FONT size=+0>I guess fld2 is shorthand for field2 so couldn't you just do
</FONT><FONT size=+0>"SELECT fld2 FROM table" instead? That'll give a speed improvement.
Or, you even change your if/elseif to
SELECT FldID FROM table1 WHERE Fld2 = 1</FONT><FONT size=+0>Fld2 is either 1 or 2. I need all the fields from the table thats why i use "SELECT *" but only the ones with value 1 are displayed with a record count corresponding to the field Id of the fields with value 2. Its a complicated process.
</FONT>
recordsArray is supporsed to work as temp storage for easier sorting, like a query in MS ACCESS, so it is again looped for the display with the sorted records and corresponding counts.
-
An issue could be (just guessing now) that your tables are top-heavy
and cramming 500 records in an inefficient table design may cause the
browser to choke.
Also 'loading time is not very bad'
still hints that the loading time is long. Maybe that's because you're
quering 900 records and throwing away 390, which means those records
shouldn't have been selected in the first place, and displaying 500
records on a page is a WTF in and of itself. With intelligend page
design, you can bring those queries down to a couple dozen records,
undoubtedly speeding up the page.
-
Does mySQL not have something akin to SELECT TOP 10 * FROM Table3 ORDER BY whatever ?
That way you only select 10 records for your top 10 (or hoewever many you need), instead of so very many more.
It may help a little bit.
Drak
-
Maybe the rest of the delay is in:
writerecords intRecCount,recordArray
or perhaps the page is so large that your browser has trouble rendering it? I know that a large table does not make IE any faster...
Drak
-
@Drak said:
Does mySQL not have something akin to SELECT TOP 10 * FROM Table3 ORDER BY whatever ?
That way you only select 10 records for your top 10 (or hoewever many you need), instead of so very many more.
It may help a little bit.
Drak
MSSQL has TOP n
mySQL has LIMIT(n1,n2). Kind of like substr() for records.
Same idea, different functionality.
-
Slight error, the syntax is
LIMIT n1,n2
where n1 is the 0-based index of the first desired row, and n2 the amount of rows.
-
@human said:
Ok, stop with the Erk now, this is sample code.
<FONT size=+0>I guess fld2 is shorthand for field2 so couldn't you just do
</FONT><FONT size=+0>"SELECT fld2 FROM table" instead? That'll give a speed improvement.
Or, you even change your if/elseif to
SELECT FldID FROM table1 WHERE Fld2 = 1</FONT><FONT size=+0>Fld2 is either 1 or 2. I need all the fields from the table thats why i use "SELECT *" but only the ones with value 1 are displayed with a record count corresponding to the field Id of the fields with value 2. Its a complicated process.
recordsArray is supporsed to work as temp storage for easier sorting, like a query in MS ACCESS, so it is again looped for the display with the sorted records and corresponding counts.</FONT>
Allways avoid the "Select * from blah" whenever possible. Try to pinpoint exactly what you need inorder to keep overhead down. so the SELECT FldID FROM table1 WHERE Fld2 = 1 is a good idea to start with. Only select the columns that you need data from and always include a where clause when possible to minimize the processing you do on the client.
-
Thankx guys for your support.
I have gotten rid of the "SELECT *" most of the tables and the speed is wonderful. The tables were the main culprits, so now im using style sheets. Works fantastic.
Thanks again
-
@human said:
I have gotten rid of the "SELECT *" most of the tables and the speed
is wonderful. The tables were the main culprits, so now im using style
sheets. Works fantastic.
Oh, THOSE types of tables :D
-
@human said:
Thankx guys for your support.
I have gotten rid of the "SELECT *" most of the tables and the speed is wonderful. The tables were the main culprits, so now im using style sheets. Works fantastic.
Thanks again
Another victory for CSS.