473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Recommendations on how to speed up code

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?


Jul 19 '05 #1
8 2028
Wilk Teverbaugh wrote:
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

--
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"
Jul 19 '05 #2
"Wilk Teverbaugh" <em***@notanaddress.com> schrieb im Newsbeitrag
news:uN**************@tk2msftngp13.phx.gbl...
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
Jul 19 '05 #3

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:OP**************@TK2MSFTNGP09.phx.gbl...
Wilk Teverbaugh wrote:
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

--
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!





Jul 19 '05 #4

"Michael G. Schneider" <mg**********@mgs-software.de> wrote in message
news:u0**************@TK2MSFTNGP11.phx.gbl...
"Wilk Teverbaugh" <em***@notanaddress.com> schrieb im Newsbeitrag
news:uN**************@tk2msftngp13.phx.gbl...
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


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?



Jul 19 '05 #5
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" <em***@notanaddress.com> wrote in message
news:uN**************@tk2msftngp13.phx.gbl...
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?

Jul 19 '05 #6

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)

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:uU**************@tk2msftngp13.phx.gbl...
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" <em***@notanaddress.com> wrote in message
news:uN**************@tk2msftngp13.phx.gbl...
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?


Jul 19 '05 #7
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....

Jul 19 '05 #8
"Wilk Teverbaugh" <em***@notanaddress.com> schrieb im Newsbeitrag
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
Jul 19 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: jm | last post by:
Somebody on here recommended Dreamweaver with PHP. I am coming from (still using really) ASP and ASP.NET. PHP is more like ASP and not a whole lot like .net, but I wanted to know how Dreamweaver...
0
by: Gloria Woerheide | last post by:
I'm new to .Net, C# and web app development, having come from a Windows development environment. I need to create a working prototype by the end of the year, in my spare time (ha). The answer to...
5
by: magmike | last post by:
I have tried two so far, ASP Runner and Iron Speed. Iron Speed has so many more features than ASP Runner, however, seems setup for use with SQL. I am simply using Access Databases, and not SQL....
51
by: Matt | last post by:
Hello, I'm a hiring C++ developer employer looking for existing, online C++ aptitude tests. I have not yet extensively researched this yet, but as an example, I thought this test looked...
0
by: NotGiven | last post by:
Hi: I am a notes programmer where I used a subset of VB, called lotus script, to write code. I have written classes and have had Java training - been a while though. I bought a good book by...
23
by: craig | last post by:
This is a little off topic, but I thought it couldn't hurt to ask... I am about to purchase a new development laptop computer. Before I do, I thought I might ask the C# development community if...
13
by: suzy | last post by:
Hi, Does anyone know of a cheap hosting company that will allow me to host asp.net with custom c# DLLs? A SQL Server would also be handy but not necessary if the cost is pushed too high. ...
2
by: sloan | last post by:
I have a web site. 90% of it is static. Lets say I have a menu system Home VicePresidents ContactUs Home is static ContactUs is static
14
by: xander.grespesky | last post by:
hi all, I'm looking for recommendations for unix "shell access" services that provide a c++ compiler (gcc or intel). free would be preferable. basically i'm looking at testing out some...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.