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:
>

False error when trying to return data in datagrid

VB.NET 2003 / SQLS2K

The Stored Procedure returns records within Query Analyzer.
But when the Stored Procedure is called by ADO.NET ~ it produced the
following error message.

--------
Exception Message: Failed to enable constraints. One or more rows
contain values violating non-null, unique, or foreign-key constraints.
--------

--------
Exception Source: System.Data
--------

If I click OK past the error messages I will get data filling the
datagrid. However not as I would like to see it.

Even though it returns the proper data rows and includes all the
columns I asked for, it also returns plenty of columns I didn't ask for
(all the columns of the main table) and all those columns are filled
with "null"

In addition each row header contains a red exclaimation mark whch when
hovered over reads;

"Column 'cmEditedBy' does not allow DBNull.Values."

An interesting thing about this column 'cmEditedBy' is that there is
noting wrong with it and all rows for that column contain data.

I believe this error is a mistake! But it probably indicates some other
problem. How should I track its cause?

M O R E ...
Below is the code in the data layer, the stored procedure, and the data
returned within query analyzer.

\\
'DataAdapter
Friend daView041CmptCyln As New SqlDataAdapter

'SqlCommand
Private daView041CmptCyln_CmdSel As New SqlCommand

'Add the command
daView041CmptCyln.SelectCommand = daView041CmptCyln_CmdSel

'Select
With daView041CmptCyln_CmdSel
.CommandType = CommandType.StoredProcedure
.CommandText = "usp_View_041Cmpt_ByJobCyln"
.Connection = sqlConn
With daView041CmptCyln_CmdSel.Parameters
.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.Int, _
4, ParameterDirection.ReturnValue, False, CType(0,
Byte), _
CType(0, Byte), "", DataRowVersion.Current, Nothing))
'Criteria
.Add("@.fkJob", SqlDbType.Text).Value = _
"48c64a55-874d-40d0-addc-7245f5d9c118"
'.Add("@.fkJob", SqlDbType.Text).Value = f050View.jobID
End With
End With
//

\\
ALTER PROCEDURE usp_View_041Cmpt_ByJobCyln
(@.fkJob char(36))
AS SET NOCOUNT ON;

SELECT
JobNumber,
DeviceName,
ComponentName,

Description,
Quan,
Bore,
Stroke,
Rod,
Seconds,
CylPSI,
PosA,
PosB,
PosC,
PosD,
PosE,
HomeIsRet,
RetIsRetrac,
POChecks,
Regulated,
FlowControl,
PortSize,
LoadMass

FROM tbl040cmpt
INNER JOIN tbl030Devi ON fkDevice = pkDeviceId
INNER JOIN tbl020Proc ON fkProcess = pkProcessId
INNER JOIN tbl010Job ON fkJob = pkjobId
INNER JOIN lkp202ComponentType ON fkComponenttype = pkComponentTypeId
INNER JOIN lkp201DeviceType ON fkDeviceType = pkDeviceTypeId
INNER JOIN lkp101PortSize on cmSmallint05 = pkPortSizeId

WHERE
(fkJob = @.fkJob)
--fkJob = '48c64a55-874d-40d0-addc-7245f5d9c118'
AND fkComponentType = 2

GO
//

(note - columns are wrapped)
\\
F1111Clip DriverCylinderClip Driver_2 - Top -
Cylinder91.2502.250.8752.250NULL01101110011/8 NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_222.1002.0001.0001.234NULL11000110011/8
NPTNULL
F1111Clip
DriverCylinderBottom92.1002.0001.0001.000NULL11010110011/4
NPTNULL
F1111Punch MechCylinderPunch Mech_1 -
Cylinder_122.1002.0001.0001.000NULL01000110011/8
NPTNULL
F1111DegateCylinderDegate 1 -
Cylinder21.1882.500.8751.000NULL11000110011/8 NPTNULL
F1111Clip DriverCylinderClip Driver 1 -
Bottom11.1801.250.8751.000NULL00011110011/4 NPTNULL
//dbuchanan (dbuchanan52@.hotmail.com) writes:
> VB.NET 2003 / SQLS2K
> The Stored Procedure returns records within Query Analyzer.
> But when the Stored Procedure is called by ADO.NET ~ it produced the
> following error message.
> --------
> Exception Message: Failed to enable constraints. One or more rows
> contain values violating non-null, unique, or foreign-key constraints.
> --------

Nah, it sounds as if that message is produced by .Net Framework. The
stored procedure pleads innocense.

> Even though it returns the proper data rows and includes all the
> columns I asked for, it also returns plenty of columns I didn't ask for
> (all the columns of the main table) and all those columns are filled
> with "null"
> In addition each row header contains a red exclaimation mark whch when
> hovered over reads;
> "Column 'cmEditedBy' does not allow DBNull.Values."

Well, that column is not in the result set, so obviously when you try
to populated the DataSet, NULL values is all you get. And apparently
they are not permitted.

I don't have that much experience of ADO .Net, but it sounds to me that
you have run some wizard that has constructed your dataset, and you then
have not been careful which columns to include. (Personally, if I were
to work with data sets, I would probably construct them manually.) Or is
there some thought behind of including columns that are not reported by the
query?

While not relevant to your problem, permit me to point an issue of style
with your query: you table includes six tables, no column is prefixed
with any alias (or the table name). This makes it very difficult for
anyone who looks at query to tell which table, the columns are coming
from. This also mean that if the DBA adds, say, "Description" to one
more table, the procedure will no longer compile because that column name
is now ambiguous.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp