Re: Fastest Fetch for Client Application
- From: "Arnie" <NoOne@xxxxxxxx>
- Date: Thu, 6 Sep 2007 18:18:21 -0400
Hi stork. Comments inline with snips. I'll try to keep this reasonably
short. It sounds like you're doing pretty well with out-of-the-box
settings, but see later.
"stork" <Todd.Bandrowsky@xxxxxxxxx> wrote in message
news:1189107921.985812.324000@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm a total luddite and am writing a brand new C++ ODBC application
for numerical analysis against a SQL Server database. I'm not using
MFC or ATL or any foundation classes whatsover. It's just C++, my
little ODBC wrapper (and a thin one at that), and a few things I wrote
to do lots of multithreading.
We ended up with ODBC but probably from a different start than you. Our
apps support SQL Server 2000, Oracle 8i+ and DB2 8+. They're singly
compiled apps that support any of those DBs dynamically at runtime.
Way back when, we used Borland's BDE (Borland Database Engine) that was
properly more appropriate for DBase and Paradox. We had a LOT of code
written to use the BDE API. I designed a set of classes that more or less
provided the same API as the BDE but used ADO/OLEDB under the covers. When
we started porting to VC++ 8, I changed the 'under the covers' part to use
ODBC. We're getting very good performance with ODBC having properly tuned
it. The OLEDB with Variants solution does pretty good but the overhead of
using Variants causes a performance hit (IMHO).
Is Firehose the Fastest Read?
--------------------------------------------
I'm using a firehose connection to do this, that is, forward only
readonly cursors, row size = 1, and, lucky for me, that's the default
out of the box setting.
I think the read only forward cursor will provide the best results, assuming
as you said, you're only reading.
Is OLEDB vs ODBC overrated?
IMHO, yes. However, any new features going into the DB server will most
likely be in OLEDB, not ODBC.
---------------------------------------------
I think that there wouldn't be too much of a difference in performance
between ODBC and OLEDB, especially if they both are just speaking TDS
to the server. Is that suspicion born out? If anything, I would
think COM based APIs might be slower because the method calls are more
expensive than going through a DLL. Again, forget the asthetics of
the API, I'm just interested in reading lots of rows quickly
performance.
Agreed.
How Dead is DB-Library, and should we care?
-------------------------------------------------------------------
As an aside, just how dead is db-library anyway?
Pretty dead. I used it 15 years ago. But it was pretty fast. However,
that was in the days when I was readding thousands of rows. Now, we're
reading millions. And, it certainly isn't portable.
Firehose Bulk Fetch on Non-Prepared (SQLExecDirect Statements)?
---------------------------------------------------------------------------------------------------
Can I have the best of all worlds and do an array like fetch (rows >
1) on non-prepared statements? It seems like I could, but the docs
seem to read that doing so would turn off the firehose. Is this true?
I can't answer this with any certainty. I can say we've had good success
reading multiple rows into an array of variables (bound)but the performance
depended upon the DB engine in use. Also, for reading BLOBs, a cache (row
set) size of one had to be forced.
SQLBindCol vs SQLGetData?
-----------------------------------------
Is it faster to bind, or to get?
Is it more than 10% faster
I think you've answered your own question here.
-------------------------------------
Are any of the above options going to be more than 10% faster for read
than just out of the box bound columns, SQLFetch?
I don't use SQLExecDirect. The statement is always prepared first,
primarily due to our DB API.
I can't show you the 'under-the covers' stuff because it's propritary
(sic?), but this is anexample of the API. This will read a few columns from
a large table (5-25 million rows) and cache the column values in memory.
It's used for a fast lookup of info when processing input data files. (C++)
// myDbConn is already connected to a server
VRMSQuery q( myDbConn ); // New query object created on the stack
q.SQL->Text = "SELECT Col1, Col2, Col3 FROM MyTable WHERE Parm1 = :parm1 AND
Parm2 = :parm2";
q.LockMode( lmDirtyRead );
q.CacheSize( 100 );
q.ParamByName( "parm1" )->AsInteger = myParm1;
q.ParamByName( "parm2" )->AsString = myParm2;
for ( q.Open(); !q.Eof; q.Next() )
{
// Read the columns here
}
q.Close();
The "SELECT Col1, Col2, Col3 FROM MyTable WHERE Parm1 = :parm1 AND Parm2 =
:parm2" gets translated into:
"SELECT Col1, Col2, Col3 FROM MyTable WHERE Parm1 = ? AND Parm2 = ?"
'Under-the-covers' mapping allows translating from the parameter names to
the positional '?' characters.
This can't be made much simpler from the application's point of view.
This is probably more than you want to know but I hope it helps at least a
little.
Regards,
- Arnie
.
- References:
- Fastest Fetch for Client Application
- From: stork
- Fastest Fetch for Client Application
- Prev by Date: Fastest Fetch for Client Application
- Next by Date: MSSQL2005 64 bit + Client ODBC
- Previous by thread: Fastest Fetch for Client Application
- Next by thread: MSSQL2005 64 bit + Client ODBC
- Index(es):
Relevant Pages
|
