slowdowns on server over time...looking for asp errors

[Larry Rekow]

an app i've written using FP2000 and Access 2000works well enough, but
the server, after a few days, slows down and needs to be rebooted to
speed things up again. some of the pages i've written use the wizards
in FP, but many of the update pages i've used by writing them in just
ASP. I *think* I've closed the various things i've SET, but not sure
about some of them. for instance, in the following:

Set objHamDB=Server.CreateObject("ADODB.Connection")
objHamDB.ConnectionTimeout=60
objHamDB.Open import_entry
Set recordSet=Server.CreateObject("ADODB.Recordset")
recordSet.Open "SELECT (max(ref)+1) as MyMax FROM Results", objHamDB
newnum=recordSet("MyMax")
sqlStatement="INSERT INTO
Results(account,mbl,[container],vessel,po,pcs,commodity,clearance_port,ref,[timestamp],[user],eta)
VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&st rcommodity&"','"&strclearance_port&"','"&newnum&"' ,
now(),'"&struser&"',"&streta&")"
Set recordSet=objHamDB.Execute(sqlStatement)
objHamDB.Close
Set objHamDB=Nothing

do I also need to close the recordset? sorry if this is a bonehead
question, but I wouldn't be surprised a bit if my ASP coding were less
than optimal.

thanks.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

[Roland Hall]

"Larry Rekow" wrote in message
news:f9o8d0dg7n0hkm48u4uqc9r70ufqt9gs9b@4ax.com...
: an app i've written using FP2000 and Access 2000works well enough, but
: the server, after a few days, slows down and needs to be rebooted to
: speed things up again. some of the pages i've written use the wizards
: in FP, but many of the update pages i've used by writing them in just
: ASP. I *think* I've closed the various things i've SET, but not sure
: about some of them. for instance, in the following:
:
: Set objHamDB=Server.CreateObject("ADODB.Connection")
: objHamDB.ConnectionTimeout=60
: objHamDB.Open import_entry
: Set recordSet=Server.CreateObject("ADODB.Recordset")
: recordSet.Open "SELECT (max(ref)+1) as MyMax FROM Results", objHamDB
: newnum=recordSet("MyMax")
: sqlStatement="INSERT INTO
:
Results(account,mbl,[container],vessel,po,pcs,commodity,clearance_port,ref,[
timestamp],[user],eta)
: VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
:
&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&st rcommodity&"','"&strcleara
nce_port&"','"&newnum&"',
: now(),'"&struser&"',"&streta&")"
: Set recordSet=objHamDB.Execute(sqlStatement)
: objHamDB.Close
: Set objHamDB=Nothing
:
: do I also need to close the recordset? sorry if this is a bonehead
: question, but I wouldn't be surprised a bit if my ASP coding were less
: than optimal.

Open late, close early, empty variables.
Close anything you've opened and anything that has a value with 'set', set
that variable = nothing to empty it when you're done with it.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - [url]http://www.microsoft.com/technet/scriptcenter/[/url]
WSH 5.6 Documentation - [url]http://msdn.microsoft.com/downloads/list/webdev.asp[/url]
MSDN Library - [url]http://msdn.microsoft.com/library/default.asp[/url]

[Bob Barrows [MVP]]

Larry Rekow wrote:

> sqlStatement="INSERT INTO
>

Results(account,mbl,[container],vessel,po,pcs,commodity,clearance_port,ref,[
timestamp],[user],eta)

> VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
>

&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&st rcommodity&"','"&strcleara
nce_port&"','"&newnum&"',

> now(),'"&struser&"',"&streta&")"
> Set recordSet=objHamDB.Execute(sqlStatement)

In addition to Roland's point, you are using an unnecessary recordset object
here. Your query is not returning records, making it extremely wasteful to
force the creation of a recordset. Instead, you should just do this:

objHamDB.Execute sqlStatement,,129

The 129 tells ADO that you are sending a sql statement as text (1) and that
you do not want it to create a recordset because the query is not returning
records (128).

Another performance drain, and potential security problem, is the use of
dynamic sql instead of a saved parameter query. If you're interested in
pursuing this, Google for posts by me containing the words "saved parameter
query".

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"

[Larry Rekow]

On Sat, 19 Jun 2004 12:39:37 -0400, "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> wrote:

>Larry Rekow wrote:

>> sqlStatement="INSERT INTO
>>

>Results(account,mbl,[container],vessel,po,pcs,commodity,clearance_port,ref,[
>timestamp],[user],eta)

>> VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
>>

>&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&s trcommodity&"','"&strcleara
>nce_port&"','"&newnum&"',

>> now(),'"&struser&"',"&streta&")"
>> Set recordSet=objHamDB.Execute(sqlStatement)

>
>In addition to Roland's point, you are using an unnecessary recordset object
>here. Your query is not returning records, making it extremely wasteful to
>force the creation of a recordset. Instead, you should just do this:
>
>objHamDB.Execute sqlStatement,,129
>
>The 129 tells ADO that you are sending a sql statement as text (1) and that
>you do not want it to create a recordset because the query is not returning
>records (128).
>
>Another performance drain, and potential security problem, is the use of
>dynamic sql instead of a saved parameter query. If you're interested in
>pursuing this, Google for posts by me containing the words "saved parameter
>query".
>
>Bob Barrows

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Thanks for your insights; I'll try this right away. But one followup
question. In this particular bit of code, I remember that I first had
to determine the next highest number for the record I'm creating, and
at the time I wrote this (lonnnnngggg time ago...maybe 3 months! heh)
I first wrote the bit about:

recordSet.Open "SELECT (max(ref)+1) as MyMax FROM Results", objHamDB
newnum=recordSet("MyMax")

so i could insert newnum as the record number

should I continue to do it this way but do a recordSet.Close as soon
as I've created the newnum? and then use the execute 129 method you've
suggested?

Larry Rekow
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

[Larry Rekow]

On Sat, 19 Jun 2004 12:39:37 -0400, "Bob Barrows [MVP]"
<reb01501@NOyahoo.SPAMcom> wrote:

>In addition to Roland's point, you are using an unnecessary recordset object
>here. Your query is not returning records, making it extremely wasteful to
>force the creation of a recordset. Instead, you should just do this:
>
>objHamDB.Execute sqlStatement,,129
>
>The 129 tells ADO that you are sending a sql statement as text (1) and that
>you do not want it to create a recordset because the query is not returning
>records (128).
>
>Another performance drain, and potential security problem, is the use of
>dynamic sql instead of a saved parameter query. If you're interested in
>pursuing this, Google for posts by me containing the words "saved parameter
>query".
>
>Bob Barrows

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++
Tried this way to execute and it works great. I did, however, use the
recordset briefly to determine the next highest number for one of the
fields (there is a reason i do not use autonumber...forget now, but it
was a valid one). but i immediately closed the recordset and then ran
the statement you suggested....thanks.

came out looking like so:

Set objHamDB=Server.CreateObject("ADODB.Connection")
objHamDB.ConnectionTimeout=60
objHamDB.Open import_entry
Set recordSet=Server.CreateObject("ADODB.Recordset")
recordSet.Open "SELECT (max(ref)+1) as MyMax FROM results", objHamDB
newnum=recordSet("MyMax")
recordSet.Close
Set recordSet=Nothing
sqlStatement="INSERT INTO
results(account,mbl,[container],vessel,po,pcs,commodity,clearance_port,ref,[timestamp],[user],eta)
VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&st rcommodity&"','"&strclearance_port&"','"&newnum&"' ,
now(),'"&struser&"',"&streta&")"
objHamDB.Execute sqlStatement,,129
objHamDB.Close
Set objHamDB=Nothing


as for the saved parameter query....whew...got my head spinning...

this could be a real timesaver for me in the future...is this a new
technique? i created this app just a few months ago and I don't
recall seeing this around then.....thanks for suggesting it.....will
test it out.

Larry Rekow
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

[Bob Barrows [MVP]]

Larry Rekow wrote:

> as for the saved parameter query....whew...got my head spinning...
>
> this could be a real timesaver for me in the future...is this a new
> technique? i created this app just a few months ago and I don't
> recall seeing this around then.....thanks for suggesting it.....will
> test it out.

I'm not sure what you mean by "new technique". Are you asking about saved
queries, themselves? Or the method I often suggest for executing them?

Access has always allowed developers to save and re-use their queries.

If you're talking about the "procedure as connection-method" technique, that
has been part of ADO since version 2.1 IIRC. It's not as well-known because
it's somewhat buried in the documentation. It's mentioned in the last
paragraph on this page:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjconnection.asp[/url]

I learned about it from Bill Vaughn's "ADO Examples and Best Practices".

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"