Showing posts with label recordsets. Show all posts
Showing posts with label recordsets. Show all posts

Thursday, March 22, 2012

Connection pooling and recordsets

Hi,
I am trying to speed up database operations of an application. The
app is separated from the SQL Server database by a WAN and they talk
through ODBC.
Operations are as slow as cold molasses.
Today I am looking at the option of enabling connection pooling. But
from what I read, I am pessimistic about whether it helps in my case.
Could some of you guys confirm my understanding?
The application opens 3 databases and keeps them open for the duration
of the program, closing them just before exiting. Meanwhile
recordsets are opened, data is retrieved, and recordsets are closed by
the dozen. It is these recordset operations that are taking their
sweet time.
Based on this scenario, am I correct to say that connection pooling
will play a a negligible effect on the application's performance?
(Because only 3 connections are done).
Thanks for any help.
-fds
I'd say you were correct. If you're keeping connections open for the
duration, then they'll never get closed and returned to the pool.
Connection pooling helps in distributed applications where connections
are continuously opened and closed, not where they're just held open
all the time. You also need to have identical connection strings and
security credentials to derive any benefit from connection pooling.
Remember that no matter what you do, you can only go as fast as the
wire permits. In your case, you may derive more benefit from
techniques that minimize the amount of data that travels over the WAN
on each call.
--Mary
On 5 Nov 2004 08:27:21 -0800, francisds@.hotmail.com (Francis DS)
wrote:

>Hi,
>I am trying to speed up database operations of an application. The
>app is separated from the SQL Server database by a WAN and they talk
>through ODBC.
>Operations are as slow as cold molasses.
>Today I am looking at the option of enabling connection pooling. But
>from what I read, I am pessimistic about whether it helps in my case.
>Could some of you guys confirm my understanding?
>The application opens 3 databases and keeps them open for the duration
>of the program, closing them just before exiting. Meanwhile
>recordsets are opened, data is retrieved, and recordsets are closed by
>the dozen. It is these recordset operations that are taking their
>sweet time.
>Based on this scenario, am I correct to say that connection pooling
>will play a a negligible effect on the application's performance?
>(Because only 3 connections are done).
>Thanks for any help.
>-fds

Connection pooling and recordsets

Hi,
I am trying to speed up database operations of an application. The
app is separated from the SQL Server database by a WAN and they talk
through ODBC.
Operations are as slow as cold molasses.
Today I am looking at the option of enabling connection pooling. But
from what I read, I am pessimistic about whether it helps in my case.
Could some of you guys confirm my understanding?
The application opens 3 databases and keeps them open for the duration
of the program, closing them just before exiting. Meanwhile
recordsets are opened, data is retrieved, and recordsets are closed by
the dozen. It is these recordset operations that are taking their
sweet time.
Based on this scenario, am I correct to say that connection pooling
will play a a negligible effect on the application's performance?
(Because only 3 connections are done).
Thanks for any help.
-fdsI'd say you were correct. If you're keeping connections open for the
duration, then they'll never get closed and returned to the pool.
Connection pooling helps in distributed applications where connections
are continuously opened and closed, not where they're just held open
all the time. You also need to have identical connection strings and
security credentials to derive any benefit from connection pooling.
Remember that no matter what you do, you can only go as fast as the
wire permits. In your case, you may derive more benefit from
techniques that minimize the amount of data that travels over the WAN
on each call.
--Mary
On 5 Nov 2004 08:27:21 -0800, francisds@.hotmail.com (Francis DS)
wrote:

>Hi,
>I am trying to speed up database operations of an application. The
>app is separated from the SQL Server database by a WAN and they talk
>through ODBC.
>Operations are as slow as cold molasses.
>Today I am looking at the option of enabling connection pooling. But
>from what I read, I am pessimistic about whether it helps in my case.
>Could some of you guys confirm my understanding?
>The application opens 3 databases and keeps them open for the duration
>of the program, closing them just before exiting. Meanwhile
>recordsets are opened, data is retrieved, and recordsets are closed by
>the dozen. It is these recordset operations that are taking their
>sweet time.
>Based on this scenario, am I correct to say that connection pooling
>will play a a negligible effect on the application's performance?
>(Because only 3 connections are done).
>Thanks for any help.
>-fds

Friday, February 10, 2012

Connecting to SQL Server 2005 from Classic ASP

I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.

The ASP code looks like this:

strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = strConnect

conn.Open

Set objCommand = Server.CreateObject("ADODB.Command")

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Set objRs = Server.CreateObject("ADODB.Recordset")

With objCommand

.ActiveConnection = conn

.CommandText = "sp_GetShowList"

.CommandType = adCmdStoredProc

If Len(strBeginDate) And IsDate(strBeginDate) Then

.Parameters.Append .CreateParameter("@.i_DateStart",adDate,adParamInput,,strBeginDate)

If Len(strEndDate) And IsDate(strEndDate) Then

.Parameters.Append .CreateParameter("@.i_DateEnd",adDate,adParamInput,,strEndDate)

End If

End If

End With

Set objRs = objCommand.Execute

arrEvents = objRs.GetRows()

objRs.NextRecordset

arrSched = objRs.GetRows()

The error message below occurs at the 2nd GetRows() command.

Operation is not allowed when the object is closed.

Can anyone tell me what I might be doing wrong?

-Dan

You don't assign the second recordset returned by NextRecordset to the recordset variable

Try

Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs = objRs.NextRecordset
arrSched = objRs.GetRows()

|||

That was so obvious I feel like an idiot. Thanks for the response!

-Dan