SQL query performance issue within asp page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL query performance issue within asp page

Author

Topic

ramone_johnny
Starting Member

35 Posts

Posted - 2003-12-08 : 01:41:34

I have the following sections of code which are functional for simple requests but perform poorly under more complex requests.

This code is taken from an events management system which looks for overlaps on venues and equipment within various dates. The code of which I have provided is taken from the RECURRING type events which may carry up to 365 dates. As you can imagine, this then means having to loop this script 365 times over. Not good, as the page sits there for about 20 seconds. And thats working locally on the server itself.

Before showing the code I should probably point out that the following *session("mystring")* carries the requested dates as such...*example only*

11/12/2003,12/12/2003,13/12/2003 etc

I understand that this may be causing the latency in processing the request, however I have no idea on how to improve it. I dont want to change the code in a way in whereby I lose functionality of the system. It is imperative that I identify and prevent overlaps and flag them to the user.

Heres the first section of code which looks for overlaps on requested venues...

***************************************************************
DIM mystring : mystring = session("mystring")
'convert to an array
DIM myarray : myarray = Split(mystring,",")
DIM the_counter 'Just declare the variable

For the_counter = 0 to UBound(myarray)
'response.write myarray(the_counter) & "
"
EventDate = MediumDate(cdate(myarray(the_counter)))
sql = "SELECT event_masterSTART, event_masterFINISH, venue_booked "
sql = sql & "FROM venue_booked WHERE venue_booked = " & Cint(request.form("sublocation_select"))
sql = sql & " AND '" & EventDate & " " & session("start_time") & "' BETWEEN event_masterSTART AND event_masterFINISH "
sql = sql & "Or venue_booked = " & Cint(request.form("sublocation_select")) & " AND '" & EventDate & " " & session("finish_time") & "' BETWEEN event_masterSTART AND event_masterFINISH "
sql = sql & "Or venue_booked = " & Cint(request.form("sublocation_select")) & " AND '" & EventDate & " " & session("start_time") & "' < event_masterSTART AND '" & EventDate & " " & session("finish_time") & "' > event_masterFINISH "
***************************************************************

Heres the section which looks for double bookings on requested equipment. As you can see it loops within a loop. I know, bad. Again I cant lose functionality. Is there an alternative?

***************************************************************
DIM dateSTRING : dateSTRING = session("mystring")
'convert to an array
DIM dateARRAY : dateARRAY = Split(dateSTRING,",")
DIM dateCOUNT 'Just declare the variable

For dateCOUNT = 0 to UBound(dateARRAY)
'IF WE FIND AN OVERLAP KILL THE QUERY
if CONTINUE = "FALSE" then
exit for
end if
EventDate = MediumDate(cdate(dateARRAY(dateCOUNT)))
'Get STRING
DIM equipSTRING: equipSTRING = request.form("right")
'response.write request.form("right")
'Get ARRAY
DIM equipARRAY : equipARRAY = Split(equipSTRING,",")
DIM equipCOUNT 'Just declare the variable
'Arrays start at zero
For equipCOUNT = 0 to UBound(equipARRAY)

sql = "SELECT event_masterSTART, event_masterFINISH, equip_booked "
sql = sql & "FROM equip_booked WHERE equip_booked = " & equipARRAY(equipCOUNT)
sql = sql & " AND '" & MediumDate(cdate(dateARRAY(dateCOUNT))) & " " & session("start_time") & "' BETWEEN event_masterSTART AND event_masterFINISH "
sql = sql & "Or equip_booked = " & equipARRAY(equipCOUNT) & " AND '" & MediumDate(cdate(dateARRAY(dateCOUNT))) & " " & session("finish_time") & "' BETWEEN event_masterSTART AND event_masterFINISH "
sql = sql & "Or equip_booked = " & equipARRAY(equipCOUNT) & " AND '" & MediumDate(cdate(dateARRAY(dateCOUNT))) & " " & session("start_time") & "' < event_masterSTART AND '" & MediumDate(cdate(dateARRAY(dateCOUNT))) & " " & session("finish_time") & "' > event_masterFINISH "
***************************************************************

And finally. The cream al a creme! The insertion page. Again this is dog slow when processing a large number of dates. BUT IT WORKS!

***************************************************************
DIM dateSTRING : dateSTRING = session("mystring")
'convert to an array
DIM dateARRAY : dateARRAY = Split(dateSTRING,",")
DIM dateCOUNT 'Just declare the variable

For dateCOUNT = 0 to UBound(dateARRAY)

DIM venue
Set venue = Server.CreateObject("ADODB.Recordset")
venue.Open "venue_booked", calConn, adLockOptimistic, adCmdTable

startdate = cdate(dateARRAY(dateCOUNT) & " " & session("start_time"))
finishdate = cdate(dateARRAY(dateCOUNT) & " " & session("finish_time"))

venue.AddNew
venue("venue_booked") = session("sublocation_select")
venue("event_masterSTART") = startdate
venue("event_masterFINISH") = finishdate
venue("magic_key") = max_EventID
venue.Update
venue.Close
Set venue = Nothing

'***************************************************************************************
'REQUESTED EQUIPMENT AND DURATION
DIM equip
Set equip = Server.CreateObject("ADODB.Recordset")
equip.Open "equip_booked", calConn, adLockOptimistic, adCmdTable

DIM sMyString: sMyString = session("right")
'Get ARRAY
DIM aMyArray : aMyArray = Split(sMyString,",")
DIM intCounter 'Just declare the variable
'Arrays start at zero
For intCounter = 0 to UBound(aMyArray)
equip.AddNew
equip("equip_booked") = aMyArray(intCounter)
equip("event_masterSTART") = startdate
equip("event_masterFINISH") = finishdate
equip("magic_key") = max_EventID
equip.Update
Next
equip.Close
Set equip = Nothing

'***************************************************************************************
'INSERT THE ACTUAL EVENT

DIM events
Set events = Server.CreateObject("ADODB.Recordset")
events.Open "events", calConn, , adLockOptimistic, adCmdTable

events.AddNew
events("event_title") = session("event_title")
events("type") = session("type")
events("teams") = session("teams")
events("members") = session("members")
events("other_presented") = session("other_presented")
events("location_select") = session("location_select")
events("other_location") = session("other_location")
events("other_venue") = session("other_venue")
events("other_address") = session("other_address")
events("other_suburb") = session("other_suburb")
events("other_city") = session("other_city")
events("other_state") = session("other_state")
events("other_postcode") = session("other_postcode")
events("other_phone") = session("other_phone")
events("sublocation_select") = session("sublocation_select")
events("finishdate") = session("finishdate")
events("agenda_notes") = session("agenda_notes")
events("cost") = session("cost")
events("payment") = session("payment")
events("paymentdate") = session("paymentdate")
events("paymentto") = session("paymentto")
events("paymentphone") = session("paymentphone")
events("pay_notes") = session("pay_notes")
events("reserv_max") = session("reserv_max")
events("notes") = session("notes")
events("addedby") = session("addedby")
events("phone") = session("phone")
events("email") = session("email")
events("fax") = session("fax")
events("magic_key") = max_EventID
events("event_masterSTART") = startdate
events("event_masterFINISH") = finishdate
events("startdate") = cdate(dateARRAY(dateCOUNT))
events.Update
next
session.Contents.RemoveAll()
response.redirect("../events/thankyou.asp")
***************************************************************

Any thoughts guys?

Thanks,
John

Complete newbie. Please forgive me for having to ask such stupid questions...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-08 : 02:55:09

Put the code into stored procedures - it will be easier to optimise and control.
Include a data access layer in the app so that you can include tracing and enforce access methods.
see
http://www.nigelrivett.net/DBAccess.inc.html
For an idea.

Once you have done that it will be easy to isolate problems and find where the bottlenecks are and ask specific questions

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-08 : 03:14:57

Whoa, that's quite a bit to worry about!

Follow nigel's advice, he knows what he is talking about. And here are a few thoughts I have:

1. Looking for overlapping dates? Try this:


WHERE eventmaster_Start <= EndDateofSomeRange AND eventmaster_End >= StartDateofSomeRange

It should also help you with finding the double bookings

2. Looping for inserts is terribly slow. You can insert multiple rows with the help of a tally table without any loops, and its set based so its much faster.

3. This line is going to get progressively slower as the table gets bigger.
>> events.Open "events", calConn, , adLockOptimistic, adCmdTable
Steer clear of the rs.AddNew method, but if you must use this, try this:


events.Open "SELECT * FROM events WHERE 1=0", calConn, adOpenKeyset, adLockOptimistic

You don't need all the table data to do an insert, the table structure is enough.

4. Minimise the use of session variables, excessive use of Application and Session variables can drastically affect scalability.

Owais

We make a living out of what we get, but we make a life out of what we give.

ramone_johnny
Starting Member

35 Posts

Posted - 2003-12-08 : 03:51:42

Thanks guys. Im a little overwhelmed at the moment with all these different alternate methods. At this point I'm going to take a look at using stored procedures. From there who knows, but I reckon there'll be some late nights and sore eyes along the way.

No doubt Ill be back.

Complete newbie. Please forgive me for having to ask such stupid questions...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources