VBSCRIPT Running incredibly slow

[melicious]

I have a program that was built some time ago by people who are no longer in the area, and the whole thing is an issue. However, I think I have one problem pinned to this script. When connecting to an Access database, this thing takes about 3 mintues to process the "Save" functions. I need this more efficient! :


IF Request.Form("formaction")="Next" or Request.Form("formaction")="Save" then
verify=false
If NOT(IsNull(Request.Form("date"))) AND (Request.Form("date") <> "") then verify=true:entrydate=Request.Form("date") Else Verify=false End IF
If NOT(IsNull(Request.Form("job"))) AND (Request.Form("job") <> "") then verify=true:job=Request.Form("job") Else Verify=false End IF
If NOT(IsNull(Request.Form("time_start"))) AND (Request.Form("time_start") <> "") then verify=true:time_start=Request.Form("time_start") Else verify=false End IF
If NOT(IsNull(Request.Form("time_stop"))) AND (Request.Form("time_stop") <> "") then verify=true:time_stop=Request.Form("time_stop") Else verify=false End IF
If NOT(IsNull(Request.Form("hours"))) AND (Request.Form("hours") <> "") then verify=true:hours=Request.Form("hours") Else verify=false End IF
If NOT(IsNull(Request.Form("work"))) AND (Request.Form("work") <> "") then work=Request.Form("work") End IF
If NOT(IsNull(Request.Form("miles"))) AND (Request.Form("miles") <> "") then miles=Request.Form("miles") Else miles=0 End IF
If NOT(IsNull(Request.Form("rec_type"))) AND (Request.Form("rec_type") <> "") then verify=true:rec_type=Request.Form("rec_type") Else verify=false End IF
If IsNumeric(Request.Form("req_num")) then req_num=Request.Form("req_num") Else req_num = 0 End IF
'If NOT(verify) then Response.redirect("timeentry2.asp?msg=1") End IF
Set dbconn= server.CreateObject("ADODB.connection")
dbconn.open Application("data1")
IF rec_num <> "" then
timesheetsdata = "Select * from tbl_timesheet where rec_num=" & rec_num
newrecord=false
Else
timesheetsdata = "tbl_timesheet"
newrecord=true
'Response.Write("new entry")
End IF
Set timesheets = Server.CreateObject("ADODB.Recordset")
timesheets.ActiveConnection = dbconn
timesheets.Source = timesheetsdata
timesheets.Cursorlocation = 3
timesheets.Cursortype = 2
timesheets.locktype = 3
timesheets.Open
If newrecord then timesheets.addnew: End IF
timesheets("emp_num").Value = Session("authname")
timesheets("Date").Value = entrydate
IF time_start <> "" then timesheets("time_start").Value = time_start End If
IF time_stop <> "" then timesheets("time_stop").Value = time_stop End If
timesheets("Job").Value = Job
timesheets("Work_performed").Value = work
timesheets("hours").Value = hours
timesheets("mileage").Value = miles
timesheets("rec_type").Value = rec_type
timesheets("req_num").Value = req_num
timesheets.update

IF Request.Form("formaction")="Save" then
timesheets.movelast
rec_num = timesheets("rec_num").Value
End IF

If NOT req_num = 0 then
'dbconn.execute "Insert Into tbl_labor (emp_num,[date],Time_start,Time_stop,cust_num,description,billable_hours,req_num) " & _
' "VALUES ("&Session("authname")&",#"&entrydate&"#,#"&time_start&"#,#"&time_stop&"#,'"&Job&"','"&work&"',"&hours&","&WOselect&")"
OpenWOdata = "Select * From tbl_labor WHERE req_num=" & req_num & " AND emp_num=" & Session("Authname") & " AND Date=#" & entrydate & "# AND Time_start=#" & Time_Start & "# AND Time_Stop=#" & Time_Stop & "#"
'Response.Write OpenWOdata
Set OpenWO = Server.CreateObject("ADODB.Recordset")
OpenWO.ActiveConnection = dbconn
OpenWO.Source = OpenWOdata
OpenWO.Cursorlocation = 3
OpenWO.Cursortype = 2
OpenWO.locktype = 3
OpenWO.Open
If OpenWO.BOF OR OpenWO.EOF then
OpenWO.Addnew
End If
OpenWO.Fields("emp_num").Value = Session("authname")
OpenWO.Fields("Date").Value = entrydate
IF time_start <> "" then OpenWO.Fields("time_start").Value = time_start End If
IF time_stop <> "" then OpenWO.Fields("time_stop").Value = time_stop End If
OpenWO.Fields("cust_num").Value = Job
OpenWO.Fields.Item("description").Value = work
OpenWO.Fields("billable_hours").Value = hours
OpenWO.Fields("req_num").Value = req_num
OpenWO.update
OpenWO.Close
End IF
IF Request.Form("formaction")="Next" Or Request.Form("formaction")="Clear" then
rec_num=""
req_num=0
'Session("date")=""
time_start=""
time_stop=""
job=""
work=""
hours=""
miles=""
'Session("rec_type")=""
End If
'---- End TimeSave ---------------------------
'----------------------------------------------
Else
'----------------------------------------------
'---- Begin old timechange.asp ---------------
IF rec_num <> "" then
timesheetsdata = "Select * From tbl_timesheet Where rec_num=" & rec_num
Set timesheets = Server.CreateObject("ADODB.Recordset")
timesheets.ActiveConnection = dbconn
timesheets.Source = timesheetsdata
'timesheets.Cursorlocation = 3
'timesheets.Cursortype = 2
'timesheets.locktype = 3
timesheets.Open
IF NOT(timesheets.EOF) then
rec_num=timesheets("rec_num").Value
entrydate=timesheets("Date").Value
time_start=timesheets("time_start").Value
time_stop=timesheets("time_stop").Value
job=timesheets("Job").Value
work=timesheets("Work_performed").Value
hours=timesheets("hours").Value
miles=timesheets("mileage").Value
rec_type=timesheets("rec_type").Value
req_num=timesheets("req_num").Value
End IF
timesheets.close
'Response.redirect("timeentry2.asp")
End IF
'---- End timechange --------------------------
'----------------------------------------------
End IF
Set custlist = Server.CreateObject("ADODB.Recordset")
custlist.activeconnection = dbconn
custlist.source = "Select customer, cust_num From tbl_customer Order by customer"
custlist.open
Set projlist = Server.CreateObject("ADODB.Recordset")
projlist.activeconnection = dbconn
projlist.source = "Select project_name From tbl_project_main Order by project_name"
projlist.open
openWOlist_data = "SELECT tbl_customer.Customer, tbl_work_order.req_num, tbl_work_order.req_date, tbl_work_order.problem, tbl_work_order.complete" & _
" FROM tbl_customer INNER JOIN tbl_work_order ON tbl_customer.cust_num = tbl_work_order.cust_num" & _
" WHERE ((Not (tbl_work_order.complete)='yes') Or (tbl_work_order.complete) Is Null Or (tbl_work_order.complete)='') ORDER BY tbl_work_order.req_date desc"
Set OpenWOlist = Server.CreateObject("ADODB.Recordset")
OpenWOlist.activeconnection = dbconn
OpenWOlist.source = openWOlist_data
OpenWOlist.open

 

[exdeath]

For starters, use a real RDBMS. Access is a joke.

MySQL, PostgreSQL, SQL Server Express, etc, all free.

Start commenting out blocks until you verify where it's choking, then check what that call is doing as far as network delays, disk I/O, CPU, etc.

 

[melicious]

I would, but the company doesn't want to change the database. So I have to make do.

 

[KLin]

Probably running slow because of the amount of data in the tables. I see .movelast function being called which moves the cursor to the very last record.

How many records are in tbl_timesheet?

 

[WannaFly]

Although I agree with exdeath, thats not the point of this thread.

Put in some message boxes or log to a file a bunch of timestamps to help you figure out where the performane problem is.

Also, how many rows are on tbl_customer and tbl_project_main, because you are selecting from them with no where clause.