VB Call SQL SP eat ram each second.

mammouth
mammouth used Ask the Experts™
on
Here is a part of code i have in a vb application:

Dim SP As ADODB.Command
Set SP = New ADODB.Command
SP.CommandTimeout = 3600
With SP
      .ActiveConnection = Db
      .CommandType = adCmdStoredProc
      .CommandText = "MySP"
      .Execute
End With
Set SP = Nothing

This SP run for 4 hour before it end, this is normal because database is really big. But when i compile the vb app. in a .exe and run it, every second, the application eat +0,004 Mb Ram?

How can i stop the ram usage to increase each second?

Thankx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
From the help file on execute:

Using the Execute method on a Command object executes the query specified in the CommandText property of the object. If the CommandText property specifies a row-returning query, any results the execution generates are stored in a new Recordset object. If the command is not a row-returning query, the provider returns a closed Recordset object. Some application languages allow you to ignore this return value if no Recordset is desired.

If it is filling a recordset (whether you use it or not), I suspect this is where the memory is going. Have the stored proc return no rows.  If it must return rows, I don't know if you'll be able to stop the memory munch unless you resort to something like creating a table to contain the records, then after the 4 hours, reading that table in.

Author

Commented:
The SP return no recordset, there is a way to specify to do not use the CommandText to catch a return from the SP
Dirk HaestProject manager

Commented:
Why don't you try this

.CommandType = adExecuteNoRecords

(Indicates CommandText 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. Always combined with adCmdText or adCmdStoredProc)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dirk HaestProject manager

Commented:
The adExecuteNoRecords constant improves performance by minimizing internal processing. This constant never stands alone; it is always combined with adCmdText or adCmdStoredProc (for example, adCmdText+adExecuteNoRecords). An error results if adExecuteNoRecords is used with the Recordset.Open method, or a Command object used by that method.


so try .commandtype = adCmdStoredProc+adExecuteNoRecords

Author

Commented:
i tried commandtype = adCmdStoredProc+adExecuteNoRecords, my vb app only contain this part of code and ram usage increase by +0,004 ea. second. If this program run over 24hour, it take lot of juice.

Here is all of my vb application, all the rest where putted in comment

Private Sub Form_Load()
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection

    Db.CommandTimeout = 3600
    Db.Open "Driver={SQL Server};SERVER=xxx.xxx.xxx.xxx;UID=xxxxx;PWD=xxxxx;DATABASE=xxxxx"

        Dim SP As ADODB.Command
        Set SP = New ADODB.Command
            SP.ActiveConnection = Db
            SP.CommandType = adCmdStoredProc + adExecuteNoRecord
            SP.CommandText = "WebSelection"
            SP.Execute

        Set SP = Nothing

    Db.Close
    Set Db = Nothing

Please Help!!!

Thankx
Commented:
Set the command timeout to zero
You have it set for 3600 seconds which is only 1 hour
0(zero) means that it will not timeout
The problem appears to be in your stored procedure
Use SQLOLEDB, MSDAORA, or OraOLEDB as your database provider
Do this for you connection: DB.CursorLocation = adUseServer
Please do not forget to set your connection object to nothing at the end

It is really hard for me to help you unless I see your stored procedure.  Is it programmed in t-SQL?

Well good luck and I hope this helps!
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- points to eli_el
Please leave any comments here within the
next seven days.
grade changed since no comment was left from asker regarding the reason for the B grade.

DigitalXtreme
CS Moderator

Commented:
Hi All.
Me too tried this way but It dont been.
This procedure dont return records.
But I am receiving error this line.
CmdUpdate.CommandType = adCmdStoredProc + adExecuteNoRecords


<%
My Codes:
Set CmdUpdate = Server.CreateObject("ADODB.Command")
CmdUpdate.CommandText = "WEB_UPDATEUSER"
CmdUpdate.CommandType = adCmdStoredProc + adExecuteNoRecords
Set Prm1 = CmdUpdate.CreateParameter("SAHIS_NO", adInteger, adParamInput)
    Prm1.Value = SAHIS_NO
Set Prm2 = CmdUpdate.CreateParameter("ADI", adVarChar, adParamInput, 26, Trim(Request("ADI")))
Set Prm3 = CmdUpdate.CreateParameter("SOYADI", adVarChar, adParamInput, 26, Trim(Request("SOYADI")))
Set Prm4 = CmdUpdate.CreateParameter("EMAIL", adVarChar, adParamInput, 50, Trim(Request("EMAIL")))
Set Prm5 = CmdUpdate.CreateParameter("WEB_PIN", adVarChar, adParamInput, 24, Trim(Request("WEB_PIN")))
Set Prm6 = CmdUpdate.CreateParameter("WEB_PASS", adVarChar, adParamInput, 24, Trim(Request("WEBPASS")))
Set Prm7 = CmdUpdate.CreateParameter("WEB_YETKILERI", adVarChar, adParamInput, 30, YETKILER)
with CmdUpdate.Parameters
.Append prm1
.Append prm2
.Append prm3
.Append prm4
.Append prm5
.Append prm6
.Append prm7
end with
CmdUpdate.ActiveConnection = conn
cmdupdate.execute
%>

Commented:
I am still having this issue.  Is anyone else having trouble with this?  I am running a simple update query for each live quote that comes in from a feed.  There are many updates occuring at the same time b/c this is a live market feed.  The RAM usage just grows and grows.  Here is my connection an update code:

CONNECTION(called once in the begining):

 sConnection = "Provider={SQLOLEDB};Server=xxxxxx" & _
 ";UID=xx;PWD=xxx;APP=Server_App;Database=QUOTES"
 Set cnUPD = New ADODB.Connection
 cnUPD.ConnectionString = sConnection
 cnUPD.CursorLocation = adUseServer
 cnUPD.ConnectionTimeout = 15
 cnUPD.CommandTimeout = 0
 cnUPD.Open



UPDATE:

sSQL = "sp_updatesymbol"
Set cmEquity = New ADODB.Command
Set prSymbol = cmEquity.CreateParameter("sSymbol", adVarChar, adParamInput, 25, sFutSym)
cmEquity.Parameters.Append prSymbol
Set prAsk = cmEquity.CreateParameter("sask", adVarChar, adParamInput, 15, sAsk)
cmEquity.Parameters.Append prAsk
Set prBid = cmEquity.CreateParameter("sbid", adVarChar, adParamInput, 15, sBid)
cmEquity.Parameters.Append prBid
Set prLast = cmEquity.CreateParameter("slast", adVarChar, adParamInput, 15, sLast)

cmEquity.Parameters.Append prLast
cmEquity.ActiveConnection = cnUPD
cmEquity.CommandText = sSQL
cmEquity.CommandType = adCmdStoredProc
cmEquity.Execute , , adExecuteNoRecords
set cmEquity = nothing
mammouth,

Is PalJoey a duplicate account?

PalJoey,

I'm curious what led you to this question and what did you mean by: "I am still having this issue."?

Please explain. Thank you

DigitalXtreme
CS Moderator

Commented:
I found this issue through a Google search. "I am still having this issue." implies I have tried the solutions suggested in the thread and the problem still exists.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial