fastest way to open a query?

Hi.I am writing a program in c++ with ado which has to write 2-3 times
per second in a same field.That`s because i need speed.i have to use
sql unfortunately because our webmaster will take data from here.if i
had chance to choose i would choose berkeley db.Anyway here is a piece
of my code.how should i change the open parameters or should i try
something else?
char query[100];
sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
keyValue); //example keyValue = USDGBP
bstr_t bstrQuery(query);
try {
hr = m_pRecSet->Open(_variant_t(bstrQuery),
vNull,
adOpenForwardOnly,
adLockOptimistic,
adCmdText);
if (!m_pRecSet->GetadoEOF()) {
m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
m_pRecSet->Update(vNull, vNull);
m_pRecSet->Close();
}
}
catch( _com_error &e ) {
TRACE( "Error:%08lx.\n", e.Error());
TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
}Try Select EXCHANGE_ID, MARKET_DATA_BID, MARKET_DATA_ASK FROM ...
That will reduce the amount of data being prepared and should speed it up
marginally.
<ozgecolak@.gmail.com> wrote in message
news:1129299784.233691.240390@.f14g2000cwb.googlegroups.com...
> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||Why bring a recordset down to the client at all? You are not reading the
values. You are making a minuimum of one trip to the server (to get a
recordset) and a maximum of two trips (to update the value if it exists).
Get rid of the recordset and limit this to a maximum of one trip to the
server. The two inefficiencies I see here are using inline sql and the
biggie of using a recordset to perform an update.
I would recommend:
1) Use a stored proc. Let SQl Server compile the execution plan saving it
from having to do it on the fly.
CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
AS
SET NOCOUNT ON
UPDATE MarketDate
SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
WHERE EXCHANGE_ID = @.ID
GO
2) Just call the ->Execute method on a connection or command object to run
the proc. Pass along the adExecuteNoRecords enum value to make sure that it
is as efficient as possible.
// Create and Configure the Command Object
pCom.CreateInstance(__uuidof(Command));
pCom->ActiveConnection = pConn;
pCom->CommandType = adCmdStoredProc ;
pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
// Append Parameters
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency,
adParamInput, 8, _variant_t(bidValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency,
adParamInput, 8, _variant_t(askValue)));
pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
adParamInput, 10, s));
// Execute the Command
pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
Even if you do not have permission on the database to create a proc, at
least get away from the recordset and pass your inline sql in the
pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
HTH,
John Scragg
"ozgecolak@.gmail.com" wrote:

> Hi.I am writing a program in c++ with ado which has to write 2-3 times
> per second in a same field.That`s because i need speed.i have to use
> sql unfortunately because our webmaster will take data from here.if i
> had chance to choose i would choose berkeley db.Anyway here is a piece
> of my code.how should i change the open parameters or should i try
> something else?
> char query[100];
> sprintf(query, "SELECT * FROM MarketData WHERE EXCHANGE_ID = '%s'",
> keyValue); //example keyValue = USDGBP
> bstr_t bstrQuery(query);
> try {
> hr = m_pRecSet->Open(_variant_t(bstrQuery),
> vNull,
> adOpenForwardOnly,
> adLockOptimistic,
> adCmdText);
> if (!m_pRecSet->GetadoEOF()) {
> m_pRecSet->PutCollect("MARKET_DATA_BID", bidValue);
> m_pRecSet->PutCollect("MARKET_DATA_ASK", askValue);
> m_pRecSet->Update(vNull, vNull);
> m_pRecSet->Close();
> }
> }
> catch( _com_error &e ) {
> TRACE( "Error:%08lx.\n", e.Error());
> TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
> TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
> TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
> }
>|||thanks.now it`s faster but not as fast as i need.
John Scragg yazdi:
> Why bring a recordset down to the client at all? You are not reading the
> values. You are making a minuimum of one trip to the server (to get a
> recordset) and a maximum of two trips (to update the value if it exists).
> Get rid of the recordset and limit this to a maximum of one trip to the
> server. The two inefficiencies I see here are using inline sql and the
> biggie of using a recordset to perform an update.
> I would recommend:
> 1) Use a stored proc. Let SQl Server compile the execution plan saving it
> from having to do it on the fly.
> CREATE PROC sp_UpdateTicker @.Bid smallmoney, @.ask smallmoney, @.ID char(10)
> AS
> SET NOCOUNT ON
> UPDATE MarketDate
> SET MARKET_DATA_BID = @.Bid, MARKET_DATA_ASK = @.Ask
> WHERE EXCHANGE_ID = @.ID
> GO
> 2) Just call the ->Execute method on a connection or command object to run
> the proc. Pass along the adExecuteNoRecords enum value to make sure that
it
> is as efficient as possible.
> // Create and Configure the Command Object
> pCom.CreateInstance(__uuidof(Command));
> pCom->ActiveConnection = pConn;
> pCom->CommandType = adCmdStoredProc ;
> pCom->CommandText = _bstr_t("dbo.sp_UpdateTicker ");
> // Append Parameters
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Bid"), adCurrency
,
> adParamInput, 8, _variant_t(bidValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.Ask"), adCurrency
,
> adParamInput, 8, _variant_t(askValue)));
> pCom->Parameters->Append(pCom->CreateParameter(_bstr_t("@.ID"), adChar,
> adParamInput, 10, s));
> // Execute the Command
> pCom->Execute(NULL, NULL, adCmdStoredProc, adExecuteNoRecords);
> Even if you do not have permission on the database to create a proc, at
> least get away from the recordset and pass your inline sql in the
> pCom->Execute(NULL, NULL, adCmdText, adExecuteNoRecords);
> HTH,
> John Scragg
>
> "ozgecolak@.gmail.com" wrote:
>|||I think you need to test the individual parts. This code takes only
miliseconds to run for me.
Here is a tip. If you do this inside a loop. Declare the command, set its
properties and create all parameters OUTSIDE of the loop. Then just
repeatedly assign the bid, ask and ID values to the parameters inside the
loop and call the execute inside the loop. This will save you any repeated
object creation overhead.
HTH
John
"ozgecolak@.gmail.com" wrote:

> thanks.now it`s faster but not as fast as i need.
> John Scragg yazdi:
>