Need for speed

Ricjd · Apr 22, 2003

Hey there.

I have been working all day to get this script down in load up time. I have got it form 21 secs to 3.5 secs (yes i am that sad that I measured it). Anyway even though 3.5 secs is nearly acceptable I still want to get it down further. What it does is display bookings in an inline frame. The code below is from the inline frame. If you fancy like a challenge or something then have a look at the code below and try and find a way of getting down maybe an extra second or so. Any ideas more than welcome.

Many many thanks to anyone who even looks at this code and hope to hear from you soon.

P.S. You might be better copying and pasting this into your preferred editor

[tt]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> Change Accomodation Details </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<link rel="stylesheet" type="text/css"href="main.css">

</HEAD>

<BODY topmargin="0" leftmargin="0">

<%

sub setColour(dayNow, monthNow, yearNow) 'sub routine to set the colour of the week days

wDay = weekday(cdate(monthNow & "/" & dayNow & "/" & yearNow))
if wDay = 1 or wDay = 7 then
response.write("<font color = '#FF0000'>" & dayNow)
response.write("<br>" & WeekdayName(wDay, true) & "</font>&quot

else
response.write(dayNow)
response.write("<br>" & WeekdayName(wDay, true))
end if

end sub

dim prop, propArray, monthView, yearView 'request all strings and set up varibles
monthView = Request.QueryString("monthView&quot

yearView = Request.QueryString("yearView&quot

props = Request.QueryString("props&quot

propArray = split(props,"_&quot

startDate = monthView & "/1/" & yearView
finnishDate = monthView + 1 & "/1/" & yearView

for u = 1 to ubound(propArray)

sql = sql & "bookingStatus = 'book' AND bookingStart >= #" & startDate & "# AND bookingProp = " & propArray(u) & " OR bookingStatus = 'book' AND bookingFinish < #" & finnishDate & "# AND bookingProp = " & propArray(u) & " OR "
sql2 = sql2 & "bookingStatus = 'book' AND bookingStart < #" & startDate & "# AND bookingProp = " & propArray(u) & " AND bookingFinish > #" & startDate & "# OR "

next

sql = (left(sql,(len(sql) - 4)))
sql2 = (left(sql2,(len(sql2) - 4)))

if monthView = "6" or monthView = "9" or monthView = "11" or monthView = "4" then 'get days in month
daysInMonth = 30
elseif monthView = "2" then
daysInMonth = 28
else
daysInMonth = 31
end if

Set bookRS = Server.CreateObject("ADODB.recordset&quot

Set bookConn = Server.CreateObject("ADODB.Connection&quot

bookConn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\cpRental.mdb&quot

& ";"
strSQL = "SELECT bookingID, bookingStart, bookingFinish, bookingProp, bookingClient FROM booking WHERE " & sql & " ORDER BY bookingStart"
bookRS.Open strSQL, bookConn

response.write("<TABLE class='view'>&quot

'start table

response.write("<TR>&quot

'write days ion month
for i = 1 to daysInMonth
response.write("<TD width='60' height='30' class='date' nowrap>&quot

call setColour(i, monthView, yearView)
response.write("</TD>&quot

next
response.write("</TR>&quot

for u = 1 to ubound(propArray)

apartmentFree = "F"

Set book2RS = Server.CreateObject("ADODB.recordset&quot

Set book2Conn = Server.CreateObject("ADODB.Connection&quot

book2Conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\cpRental.mdb&quot

& ";"
strSQL = "SELECT bookingID, bookingStart, bookingFinish, bookingProp, bookingClient FROM booking WHERE " & sql2 & " ORDER BY bookingStart"
book2RS.Open strSQL, book2Conn

Do While not book2RS.EOF
if cstr(book2RS("bookingProp&quot

) = cstr(propArray(u)) then
Set guestRS = Server.CreateObject("ADODB.recordset&quot

Set guestConn = Server.CreateObject("ADODB.Connection&quot

guestConn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\cpRental.mdb&quot

& ";"
strSQL = "SELECT GuestSurname FROM Guest WHERE GuestID = " & book2RS("bookingClient&quot

guestRS.Open strSQL, guestConn

apartmentFree = guestRS("GuestSurname&quot

bookingID = book2RS("bookingID&quot

guestRS.Close
guestConn.close
set guestRS=Nothing
set guestConn=Nothing
end if
book2RS.MoveNext
loop

book2RS.Close
book2Conn.close
set book2Conn=Nothing
set book2RS=Nothing

response.write("<TR>&quot

for i = 1 to daysInMonth
response.write("<TD width='60' height='30' class='cell' nowrap>&quot

Do While not bookRS.EOF

if cstr(bookRS("bookingProp&quot

) = cstr(propArray(u)) then

if monthView & "/" & (i) & "/" & yearView = cstr(bookRS("bookingFinish&quot

) then
apartmentFree = "F"
elseif monthView & "/" & (i) & "/" & yearView = cstr(bookRS("bookingStart&quot

) then
Set guestRS = Server.CreateObject("ADODB.recordset&quot

Set guestConn = Server.CreateObject("ADODB.Connection&quot

guestConn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\cpRental.mdb&quot

& ";"
strSQL = "SELECT GuestSurname FROM Guest WHERE GuestID = " & bookRS("bookingClient&quot

guestRS.Open strSQL, guestConn

apartmentFree = guestRS("GuestSurname&quot

bookingID = bookRS("bookingID&quot

guestRS.Close
guestConn.close
set guestRS=Nothing
set guestConn=Nothing
end if

end if

bookRS.MoveNext
loop
bookRS.MoveFirst
if apartmentFree = "F" then
response.write("<A HREF='confirm.asp?startdate=" & monthView & "/" & (i) & "/" & yearView & "?=" & propArray(u) &"' TARGET='_top'><font color='#FF0000'>Free</font></A>&quot

else
response.write("<A HREF='default.asp?Lang=Eng&Page=occupent&booking=" & bookingID & "' TARGET='_top'>" & left(apartmentFree,10) & "</A>&quot

end if

response.write("</TD>&quot

next
response.write("</TR>&quot

next

bookRS.Close
bookConn.close
set bookConn=Nothing
set bookRS=Nothing

response.write("</TABLE>&quot

%>

</BODY>
</HTML>[/tt]

jlsmithhartfiel · Apr 22, 2003

One of the things I checked when trying to speed up my site, was to find out the time the sql query took and then the rendering time. I personally found the sql query took longer & was able to optimize it. This will at least give you the base time it will take to get the data back & then you can look at which part can be made more efficient.

HTH,

Jessica [ponytails2]

pgferro · Apr 22, 2003

For my personal experience, best results are obtained with proper indexing and the use of stored procedures...

jfrost10 · Apr 22, 2003

And if you don't have sql server and are using Access, you'll still get a performance boost if you put all your sql strings into stored queries within the datafile.

D'Arcy

Tarwn · Apr 22, 2003

Could you clarify what this page is doing?

Here is what I got from looking at it:

1) Passed information: Start date, end date, a collection of booking properties

2) Your attempting to draw a calendar that displays in the blocks (dates) if a specific piece of property (or one of a group) is free or a link if the specific property is booked

3) I got a little lost on the rest

At a guess I would say you should be able to reduce the number of trips to the database by more than half, but I would need more information in order to advise in particulars.

Perhaps a description of the database and what output your attempting to show?

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

whloo · Apr 22, 2003

i think you shld take the below createobject and open connection out of the for loop. you create too many unnecessary object and connection. those will slow down your performance.

Set bookRS = Server.CreateObject("ADODB.recordset&quot

Set bookConn = Server.CreateObject("ADODB.Connection&quot

bookConn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("db\cpRental.mdb&quot

& ";"

dilettante · Apr 22, 2003

Yep, too much thrashing with ADO objects and way too much string concatenation going on here.

Jeebers Cripes, build that connection string once and reuse it at least.

And maybe some examination of your queries will help you come up with something that avoids the need to make database probes inside of loops.

A bunch of separate response.write( )s can beat concatenating up a string and doing a single one.

Use VBScript date functions to figure out stuff like days in a month, don't thrash with it in script. Use Date variant data items for dates, don't play around with hunks of strings to represent date parts unless you need to.

For hem'sake use Option Explicit and "call your shots" (Dimension your data items).

And use preplanned queries (queries defined in the database).

Ricjd · Apr 23, 2003

hey. i think i am being told 3 main things.

1 index the database.

2 stored queries

3 and one databse connection

the first point i am not too sure if a can do it as it is for a live system where booking will be added everyday.

the second point, i am not using sql server but would like to. the only problem i have is that i do not know who to set it up properly. if any one could point me into the right direction of a web sie which could explain it then this would be helpful. also explain stored procudures. and another thing with stored procudures is will i be able to pass varibls to them? if this is not possible will i be able to pass varible to stored procedures in a access database, and how would i call these procedures?

third point which is what i am being old ALL over the place. my original code had 3 times as many connections and was EXTREMLY slow. i have tried putting it all in one connection string but it slowed it down to over a minute!

if anyone could offer more help i would be extremly grateful.

rick

xutopia · Apr 23, 2003

Sometimes it is faster to store the recordset in a variable using getRows and closing recordset and connection right after.

There are some decent articles online if you google it you might find some things on how to optimize your code.

Also if you really do a lot of looping you might want to google "unrolling" as an optimization technique. I found that it helped me a lot with some of my code (JS/Java mainly but the logic can still work in VB).

Good luck.

Gary

http://www.xutopia.com

Haran
==========================

Tarwn · Apr 23, 2003

4) abopve should be re-examining the logic your using in your loops. Right now you have way to many calls to the database embedded inside loops which means that your going to be getting considerably slower with each set of records added.
Looking at your loops you are currently (getting #ofProps * (daysInMonth * (booksRS.RecordCount + 1)) + 1) + 1 recordsets for your heaviest page load.

If we assume they only want 2 properties, in the shortest month (feb, 28 days), and you have had a record low number of bookings (say 10), then you will have opened 311 recordsets by the time your done...
unless I missed an Exit Loop or Exit For statement somewhere.

Say they are comparing 5 properties, in december, and you have a lot of bookings around that time...

Maybe if you outline what your trying to do in that section than we can help give you some directions in rethinking the logic involved in it

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need for speed

Ricjd

Programmer

jlsmithhartfiel

Programmer

pgferro

Programmer

jfrost10

Programmer

Tarwn

Programmer

whloo

Programmer

dilettante

MIS

Ricjd

Programmer

xutopia

Programmer

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor