Recommendations on how to speed up code

[Wilk Teverbaugh]

I'm using get rows to build a table for a calendar.
The specific view for this calendar is for an entire month.
Each appointment slot is one half hour long.
If I were to generate a page for the month of november I would have to check
and see if an appointment is scheduled for each appointment slot throughout
the entire month... this is 240 slots! At this point I've written it so it
only checks for an appointment if a particular day has an appointment, even
so, with only 20 or so appointments scheduled for the entire month, it's
taking several seconds to return the results.

I was thinking it might be best to pay a javascript programmer to write a
script that will parse through the whole mess to offload it onto the client.
What would you recommend?

 

[Bob Barrows]

I'm using get rows to build a table for a calendar.
The specific view for this calendar is for an entire month.
Each appointment slot is one half hour long.
If I were to generate a page for the month of november I would have
to check and see if an appointment is scheduled for each appointment
slot throughout the entire month... this is 240 slots! At this point
I've written it so it only checks for an appointment if a particular
day has an appointment, even so, with only 20 or so appointments
scheduled for the entire month, it's taking several seconds to return
the results.

I was thinking it might be best to pay a javascript programmer to
write a script that will parse through the whole mess to offload it
onto the client. What would you recommend?

Hard to say without knowing your database version, table structure, etc. I
would be attempting to do as much of this processing in the database.

Show us your current query (not the vbscript code that generates the query
if you are using dynamic sql - show us the query statement that gets sent to
the database - use Response.Write to write this statement to the browser
window.

Then give us a better idea about how you want your results to appear.

Bob Barrows

 

[Michael G. Schneider]

I'm using get rows to build a table for a calendar.
The specific view for this calendar is for an entire month.
Each appointment slot is one half hour long.
If I were to generate a page for the month of november I would have to check
and see if an appointment is scheduled for each appointment slot throughout
the entire month... this is 240 slots! At this point I've written it so it
only checks for an appointment if a particular day has an appointment, even
so, with only 20 or so appointments scheduled for the entire month, it's
taking several seconds to return the results.

Maybe I missunderstand you, but it sounds as if you were creating several
SQL statements for generating a full month calendar. Maybe even one SQL
statement per slot?

I suggest you only execute a single SQL statement, run through the
recordset, and build a structure with all appointments. Maybe this should be
an array of 31 cells, each cell containing an array of the appointments for
that day. Then when composing the HTML output, I would use the Join function
for merging all data into a table.

If the code is stable, I would thing about creating an ActiveX DLL,
realizing an object model for the appointment data.

I was thinking it might be best to pay a javascript programmer to write a
script that will parse through the whole mess to offload it onto the client.
What would you recommend?

I would not recommend to do so.

Michael G. Schneider

 

[Wilk Teverbaugh]

Hard to say without knowing your database version, table structure, etc. I
would be attempting to do as much of this processing in the database.

Show us your current query (not the vbscript code that generates the query
if you are using dynamic sql - show us the query statement that gets sent to
the database - use Response.Write to write this statement to the browser
window.

Then give us a better idea about how you want your results to appear.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I'm running SQL server 2000.

Here is my SELECT statement-

"SELECT appointmentStart, appointmentEnd, recurring, ownerID FROM wcdemo
WHERE ownerID = '1' AND (recurring = 1 OR recurring = 2 OR recurring = 4 OR
recurring = 6 OR recurring = 8) OR (appointmentStart >= 11/1/2003 AND
appointmentEnd <= 11/30/2003) ORDER BY appointmentStart ASC"

The HTML table has 9 columns, 30 rows. The far left column represents the
day, and the following 8 represent a single time slot, for the hours between
9:00 AM to 5:00 PM at 1 hour increments.

At this point I'm iterating through my get.rows array for each day, and than
again for the hours in the day, which is terribly slow!

 

[Wilk Teverbaugh]

Maybe I missunderstand you, but it sounds as if you were creating several
SQL statements for generating a full month calendar. Maybe even one SQL
statement per slot?

I suggest you only execute a single SQL statement, run through the
recordset, and build a structure with all appointments. Maybe this should be
an array of 31 cells, each cell containing an array of the appointments for
that day. Then when composing the HTML output, I would use the Join function
for merging all data into a table.

If the code is stable, I would thing about creating an ActiveX DLL,
realizing an object model for the appointment data.


I would not recommend to do so.

Michael G. Schneider

I'm using get.rows to pull my info with a single SELECT statement. After
that, I put the returned data into a custom array, which I use to delimit
certain values uniquely. After that, I run function for each day that looks
for scheduled appointments. When you say DLL, do you mean in C++ or VB6?
Would a VB6 DLL run faster?

 

[Aaron Bertrand [MVP]]

Great use for a calendar table! Try this example out.



SET NOCOUNT ON

CREATE TABLE TimeSlots
(
tsID INT IDENTITY(1,1)
PRIMARY KEY NONCLUSTERED,
dt SMALLDATETIME,
ts TINYINT
)
GO

/*
Might also want a check constraint, e.g. CHECK h >=1
AND h <= 12. Assuming you allow 12 appts / day

You could also have a computed column that displays
what time this timeslot actually represents, e.g.
h = 5, time = dateadd(minute, 30 * (h-1), dt + 9:00)

-- it would be more complicated than that, of course,
-- if you don't allow appointments over a lunch hour
*/



CREATE CLUSTERED INDEX dt_ts ON TimeSlots(dt, ts)
GO


CREATE TABLE Appointments
(
AppointmentID INT IDENTITY(1,1)
PRIMARY KEY NONCLUSTERED,
tsID INT NOT NULL FOREIGN KEY REFERENCES
TimeSlots(tsID),
description VARCHAR(32)
)
GO

CREATE INDEX tsID ON Appointments(tsID)
GO

DECLARE @dt SMALLDATETIME, @i INT
SET @dt = '20031101'
WHILE @dt <= '20031130'
BEGIN
SET @i = 1
WHILE @i <= 12
BEGIN
INSERT TimeSlots(dt, ts)
VALUES(@dt, @i)
SET @i = @i + 1
END
SET @dt = @dt + 1
END
GO

INSERT Appointments(tsID, description)
VALUES(23, 'Dentist')

INSERT Appointments(tsID, description)
VALUES(37, 'Chiropractor')

INSERT Appointments(tsID, description)
VALUES(31, 'Doctor')
GO


-- now your query becomes:

SELECT
realtime = DATEADD(MINUTE, 30 * (ts-1),
DATEADD(HOUR, 9, ts.dt)),
a.description
FROM
TimeSlots ts
LEFT JOIN
Appointments a
ON
ts.tsID = a.tsID
-- here's the clustered index payoff
WHERE
ts.dt >= '20031101'
AND ts.dt < '20031201'
GO

-- no looping, no lookup queries for every time
-- slot, etc.

DROP TABLE Appointments
DROP TABLE TimeSlots



You never mentioned if appointments had to be unique, so I didn't code in
anything for that.

This assumes SQL Server, but you would only have to make minor adjustments
for other db platforms. It's always useful, though, to tell us which
platform and version you are using!

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 

[Wilk Teverbaugh]

Wowsers!!! Nice

Very nice!

So a clustered index basically works like a template in a way? This code
jams.. thank you very much for the help.
Looks like I've got a little SQL homework to do now.

PS- Thanks for pointing out my IP in the message header too! (I didn't
realize)

 

[news.onetel.net.uk]

optimising your query is obviously the best step, however, you might also
want to consider the HTML output.

I have some large HTML tables that are generated from queries. IE/NS will
not display the table until the entire thing is built (fair enough really) -
but this does leave the user waiting with nothing obviously happening on
screen.

You might want to try splitting your table into nicer chunks - that way the
user will see parts of the table appearing -- maybe enough for them to
believe the whole page has loaded (but the rest is still clunking away past
the end of their window...). It is tricky to take what was once one large
table and split it into horizontal segments that line-up in the end - but
not impossible....

 

[Michael G. Schneider]

I'm using get.rows to pull my info with a single SELECT statement.
After that, I put the returned data into a custom array, which I use to
delimit certain values uniquely. After that, I run function for each day
that looks for scheduled appointments. When you say DLL, do you
mean in C++ or VB6? Would a VB6 DLL run faster?

I think it is most important to first find out, which is the bottleneck. Is
it the SQL or is it the HTML-generation.

If you are already using a single SELECT, and if your SQL is not that bad, I
suspect that optimzing the HTML-generation results in better performance.
You could test this by commenting out all HTML-generation code. So just open
the recordset and run through it. How long does ist last?

If it is the HTML-generation, that needs to be optimized, it's probably hard
to give specific suggestions (without seeing all the code). One very
important point is the fact, that ASP is not that fast, when it come to
string manipulation. For example, if your code is something like

Set rs = OpenRecordset(...)
Do While Not rs.EOF
strBuffer = strBuffer & ...
Loop
Response.Write strBuffer

and you are creating the output-table by concatenating a string, this can
become slow. Usually I try to avoid statements like

strBuffer = strBuffer & "<tr><td>"
strBuffer = strBuffer & ...data
strBuffer = strBuffer & "</td></tr>"

It is much better to put all chunks of html-output into a growing array, and
when finished outputting the join'ed array. Something like (syntax not
checked)...

Dim aBuffer(100)
Dim lBufferSize

lBufferSize = 0

If lBufferSize > UBound(aBuffer) Then
Redim Preserver aBuffer(UBound(aBuffer)+100)
End If
aBuffer(lBufferSize) = ...data...
lBufferSize = lBufferSize+1

Response.Write Join(aBuffer)

I usually develop all code in ASP. Then, if it is stable and I might need it
in another project, I move it into an ActiveX DLL. Most of the time this is
a DLL written with VB6. Sometimes it is written with VC++ - for example for
such tasks as the above string concatenation.

Michael G. Schneider