| Author |
 Topic  |
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
 Posted - 17 August 2002 : 18:48:26
|
I added this:
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4" NAME="ADODB Type Library" -->
to the very top of config.asp and am in the process of replacing all of the numerical constatns with their ADO contants equivalents. |
 |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 17 August 2002 : 19:13:57
|
in admin_forums.asp there is the following:
drs.Open strsql, my_conn, 3, 3
Is this correct?
it would be?
drs.Open strsql, my_conn, adOpenStatic, adLockOptimistic
right? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 17 August 2002 : 19:28:52
|
I also plan to change all instances of:
my_Conn.Execute (strSql)
to this:
my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 17 August 2002 : 19:53:34
|
Yes, good decision, I think.
Don't know if you have the time to do it, but I've verified that many times the recordset is opened using adOpenStatic (or 3 as it is now), but really a adOpenForwardOnly would be perfectly enough, and surely faster and with less load on the server.
One of the pages where I've found that is topic.asp, since I had to deal with a Chili!ASP problem a few days ago. The recordset for the replies to the topic is opened as a Static recordset, but I saw nothing to require it.
As I said, don't know if there is time for this, and whatever the changes, they need careful testing, but some performance improvement could come from there as well.
If you decide to this and need some help, I can try to point out the places where I think this could be done. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 17 August 2002 : 19:59:48
|
in topic.asp when I change:
rsReplies.open strSql & strSql2 & strSql3 & strSql4, my_Conn, adOpenStatic
to:
rsReplies.open strSql & strSql2 & strSql3 & strSql4, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
I get the following error:
quote:
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 17 August 2002 : 20:09:30
|
quote:
Originally posted by RichardKinser
in admin_forums.asp there is the following:
drs.Open strsql, my_conn, 3, 3
Is this correct?
it would be?
drs.Open strsql, my_conn, adOpenStatic, adLockOptimistic
right?
drs.Open strsql, my_conn, adOpenStatic, adLockOptimistic, adCmdText
You may use
adLockReadOnly
Indicates read-only records. You cannot alter the data.
instead of
adLockOptimistic
Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 17 August 2002 : 20:09:41
|
Funny thing,
At line 270, topic.asp, Snitz 3.4RC2, I have:
set rsReplies = Server.CreateObject("ADODB.Recordset")
rsReplies.cachesize = strPageSize
rsReplies.pagesize = strPageSize
rsReplies.CursorLocation = 3
'Response.Write strSql1 & strSql2 & strSql3 & strSql4
rsReplies.open strSql1 & strSql2 & strSql3 & strSql4, my_Conn, 0, 1, &H0001
I'd say this is already using the correct values for adOpenForwardOnly and I have no problems with it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 17 August 2002 : 20:11:32
|
quote:
Originally posted by RichardKinser
in topic.asp when I change:
rsReplies.open strSql & strSql2 & strSql3 & strSql4, my_Conn, adOpenStatic
to:
rsReplies.open strSql & strSql2 & strSql3 & strSql4, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
I get the following error:
quote:
Current Recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype.
Change it to adOpenStatic
<edit>
When using server side cursor adUseForwardOnly is not supported. so to use adUseForwardOnly specify
Recordset.CursorLocation = adUseClient (Default is adUseServer)
</edit> |
Edited by - GauravBhabu on 17 August 2002 21:04:14 |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 17 August 2002 : 20:12:15
|
ruirib,
you have to look down a little further. (around line #380)
The code you have above is part of the "Jump to Last post" section. |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 17 August 2002 : 20:12:46
|
This will be helpful:
[quote]
Open Method (ADO Recordset)
Opens a cursor.
Syntax
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Parameters
Source
Optional. A Variant that evaluates to a valid Command object, an SQL statement, a table name, a stored procedure call, a URL, or the name of a file or Stream object containing a persistently stored Recordset.
ActiveConnection
Optional. Either a Variant that evaluates to a valid Connection object variable name, or a String that contains ConnectionString parameters.
CursorType
Optional. A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. The default value is adOpenForwardOnly.
adOpenDynamic (2)
Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.
adOpenForwardOnly (0)
Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.
adOpenKeyset (1)
Uses a keyset cursor. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
adOpenStatic (3)
Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
adOpenUnspecified (-1)
Does not specify the type of cursor.
LockType
Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. The default value is adLockReadOnly.
adLockBatchOptimistic (4)
Indicates optimistic batch updates. Required for batch update mode.
adLockOptimistic (3)
Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockPessimistic (2)
Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnly (1)
Indicates read-only records. You cannot alter the data.
adLockUnspecified (-1)
Does not specify a type of lock. For clones, the clone is created with the same lock type as the original.
Options
Optional. A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved. Can be one or more CommandTypeEnum or ExecuteOptionEnum values, which can be combined with a bitwise AND operator.
adCmdUnspecified (-1)
Does not specify the command type argument.
adCmdText (1)
Evaluates CommandText as a textual definition of a command or stored procedure call.
adCmdTable (2)
Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc (4)
Evaluates CommandText as a stored procedure name.
adCmdUnknown (8) Default.
Indicates that the type of command in the CommandText property is not known.
adCmdFile (256)
Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect (512)
Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect.
This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.
|
CSS and HTML4.01 Compilant Snitz Forum . ForumSquare . Rakesh Jain
It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying.
Prayer Of Forgiveness "I forgive all living beings. May all living beings forgive me!
I cherish the friendliness towards all and harbour enmity towards none." -- Aavashyaka Sutra(Translated) |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 17 August 2002 : 20:29:28
|
Execute Method (ADO Connection)
Executes the specified query, SQL statement, stored procedure, or provider-specific text.
Syntax
For a non–row-returning command string:
connection.Execute CommandText, RecordsAffected, Options
For a row-returning command string:
Set recordset = connection.Execute (CommandText, RecordsAffected, Options)
Return Value
Returns a Recordset object reference.
Parameters
CommandText
A String value that contains the SQL statement, table name, stored procedure, a URL, or provider-specific text to execute.
RecordsAffected
Optional. A Long variable to which the provider returns the number of records that the operation affected.
Options
Optional. A Long value that indicates how the provider should evaluate the CommandText argument. Can be a bitmask of one or more CommandTypeEnum or ExecuteOptionEnum values.
CommandTypeEnum
Specifies how a command argument should be interpreted.
Constant Value Description
adCmdUnspecified -1
Does not specify the command type argument.
adCmdText 1
Evaluates CommandText as a textual definition of a command or stored procedure call.
adCmdTable 2
Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
adCmdStoredProc 4
Evaluates CommandText as a stored procedure name.
adCmdUnknown 8
Default. Indicates that the type of command in the CommandText property is not known.
adCmdFile 256
Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
adCmdTableDirect 512
Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect.
This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.
ExecuteOptionEnum
Specifies how a provider should execute a command.
Constant Value Description
adAsyncExecute 0x10
Indicates that the command should execute asynchronously.
This value cannot be combined with the CommandTypeEnum value adCmdTableDirect.
adAsyncFetch 0x20
Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be retrieved asynchronously.
adAsyncFetchNonBlocking 0x40
Indicates that the main thread never blocks while retrieving. If the requested row has not been retrieved, the current row automatically moves to the end of the file.
If you open a Recordset from a Stream containing a persistently stored Recordset, adAsyncFetchNonBlocking will not have an effect; the operation will be synchronous and blocking.
adAsynchFetchNonBlocking has no effect when the adCmdTableDirect option is used to open the Recordset.
adExecuteNoRecords 0x80
Indicates that the command text is a command or stored procedure that does not return rows (for example, a command that only inserts data). If any rows are retrieved, they are discarded and not returned.
adExecuteNoRecords can only be passed as an optional parameter to the Command or Connection Execute method.
adExecuteStream 0x400
Indicates that the results of a command execution should be returned as a stream. adExecuteStream can only be passed as an optional parameter to the Command Execute method.
adExecuteRecord
Indicates that the CommandText is a command or stored procedure that returns a single row which should be returned as a Record object.
adOptionUnspecified -1
Indicates that the command is unspecified.
Note Use the ExecuteOptionEnum value adExecuteNoRecords to improve performance by minimizing internal processing.
Do not use adExecuteStream with the Execute method of a Connection object.
Do not use the CommandTypeEnum values of adCmdFile or adCmdTableDirect with Execute. These values can only be used as options with the Open and Requery methods of a Recordset. |
CSS and HTML4.01 Compilant Snitz Forum . ForumSquare . Rakesh Jain
It is difficult to IMPROVE on Perfection, There is no harm in Keep Trying.
Prayer Of Forgiveness "I forgive all living beings. May all living beings forgive me!
I cherish the friendliness towards all and harbour enmity towards none." -- Aavashyaka Sutra(Translated) |
Edited by - GauravBhabu on 17 August 2002 20:30:57 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 17 August 2002 : 20:30:23
|
quote:
Originally posted by RichardKinser
ruirib,
you have to look down a little further. (around line #380)
The code you have above is part of the "Jump to Last post" section.
Yes, I've seen it now.
And the difference between both results from the fact that at 280, the cursor is a client-side one, while at 380 there is no specification for that.
In fact I usually use client side cursors for paging, but I'm not sure what is best, speed wise: a client side forward only cursor, or a server side static cursor. Will have to test it later.
Funny thing too, cause the book I used to learn this stuff (Wrox's Profession Active Server Pages 3.0) does use client side cursors for paging. I noticed that with Snitz it's hardly used (except that jump to last reply code, but I guess it was a bit influenced by me...). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 17 August 2002 : 21:09:57
|
I added this:
rsReplies.CursorLocation = adUseClient
and don't get the error anymore.
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 17 August 2002 : 21:17:44
|
Please Note, Only a setting of adOpenStatic is supported if the CursorLocation property is set to adUseClient. If an unsupported value is set, then no error will result; the closest supported CursorType will be used instead.
So in essence when using Client side Cursor with adOpenForwardOnly, the Cursor Type used will be, most likely, adOpenKeyset adOpenStatic.
<EDIT> Corrections
|
Edited by - GauravBhabu on 17 August 2002 21:27:52 |
|
|
|
Topic |
|
|
|
Topic Locked
