ODBC performance

Hello all,

I'm trying to get a fair amount of data written to a form that statisticians
are willing to manipulate.  My first thought was an Access database, but,
I'm beginning to reconsider that decision.  Extrapolating/guessing, it could
take a few days to do the ultimate export all of the data.

Faced with those rough numbers, I finally decided to take the time to try
prepared statements.  Things got better, but, not nearly as much as I
expected; the saving is only about 25%.  Ok, turning a four day run into a 3
day run isn't bad, but, I was hoping to get four days down to 4 hours :)

Profiling inserts of only 5000 records shows that time spent actually
executing the prepared statements is a little longer than for unprepared
statements (not really all that surprising I guess), but, it saves a lot of
time by not allocating a statement for each record.

Anybody want to challenge those results and/or the 25% reduction in time?
My first thought was that I hadn't really prepared, or that I was preparing
for every record, etc., but, the profiler doesn't appear to be picking up on
anything like that.  In the prepared case, it spends 92% of its time
executing, and 7% closing the cursor; there isn't much left over for
removeable inefficiency.

Dumb question: Setting the #values: for a parameterized statement invokes
#close, which closes the cursor.  Is that necessary?  I have no clue, but,
if there's a way to keep the cursor and achieve the same result, it might
help performance????

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Bill,

> I'm trying to get a fair amount of data written to a form that
statisticians
> are willing to manipulate.  My first thought was an Access database, but,
> I'm beginning to reconsider that decision.  Extrapolating/guessing, it
could
> take a few days to do the ultimate export all of the data.
>
> Faced with those rough numbers, I finally decided to take the time to try
> prepared statements.  Things got better, but, not nearly as much as I
> expected; the saving is only about 25%.  Ok, turning a four day run into a
3
> day run isn't bad, but, I was hoping to get four days down to 4 hours :)

I've never used Access but with a "real" database I'd expect to be able to
get something of the order of a terabyte loaded in that time...

Assuming that you don't really have anything like a TB to load, and that the
slow transfer rate isn't because of some more-or-less simple bug somewhere,
then it might be worth your while dumping the data out to file, in CSV
format, from Dolphin, and then slurping the whole lot up into Access in one
go.

BTW, if you do have a respectable bulk of data (a few GB, say) and you're
running on a Windows that supports it, then it might be worth putting the
intermediate CSV file in a compressed directory -- the bottleneck for this
kind of bulk data work can quite often be the disk IO, and compression
reduces that.

    -- chris

"Bill Schwab" <[hidden email]> wrote in message
news:a3v06b$1b55ef$[hidden email]...
> Hello all,
> [re: Insert performance into an access database]
> ...
My (limited) experience is that inserting into an Access database is indeed
slow. For better performance a good choice would be the Microsoft Database
Engine (MSDE). This is essentially a cut-down version of the SQLServer
database engine, and it is free.

http://msdn.microsoft.com/vstudio/msde

It would be interesting to know what sort of performance boost you get if
you try it.

>...
> Dumb question: Setting the #values: for a parameterized statement invokes
> #close, which closes the cursor.  Is that necessary?  I have no clue, but,
> if there's a way to keep the cursor and achieve the same result, it might
> help performance????

I think the idea is that the cursor be "closed" (as opposed to completely
dropped), because the updating of the parameters requires that it be
re-executed to get a new result set. Closing the cursor does not mean that
the statement needs to be re-prepared, it just throws away the previous
results.

Regards

Blair

Don't know if they are compatible, but have you tried MySQL or the
PostGRE databases?  I believe both are public domain.

Larry
--
[hidden email]

"Bill Schwab" <[hidden email]> wrote (with possible
editing):

Blair,

> My (limited) experience is that inserting into an Access database is
indeed
> slow. For better performance a good choice would be the Microsoft Database
> Engine (MSDE). This is essentially a cut-down version of the SQLServer
> database engine, and it is free.
>
> http://msdn.microsoft.com/vstudio/msde
>
> It would be interesting to know what sort of performance boost you get if
> you try it.

Thanks for the link.  It's definitely something that I would like to try,
but, for good or bad I solved the immediate problem by: (1) dumping the data
to a couple of text files; (2) dumping the job of taking the data rest of
the way on a graduate student :)   Most of my database updates occur just a
few records at a time, so speed usually isn't a big problem.


> I think the idea is that the cursor be "closed" (as opposed to completely
> dropped), because the updating of the parameters requires that it be
> re-executed to get a new result set. Closing the cursor does not mean that
> the statement needs to be re-prepared, it just throws away the previous
> results.

Fair enough.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Larry,

> Don't know if they are compatible, but have you tried MySQL or the
> PostGRE databases?  I believe both are public domain.

No, but, I would like to try one or both.  Licensing as well as a good
connection from Squeak would be deciding factors.  Right now, Access and
ODBC are largely meeting my needs, and there's a possible move to Oracle
looming for instituational reasons.

I mentioned this mostly because I was expecting to see something like two
orders of magnitude rather than not quite a factor of two gain from prepared
statements.  I'm still not convinced I did it right.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

On Fri, 8 Feb 2002 12:55:19 -0000,
Blair McGlashan <[hidden email]> wrote:
and if you are using odbc already and are concerned about speed
(a contradiction? ;), you could also check out mysql from
www.mysql.com which is GPL and verrrrry fast, too.

s.
--
Stefan Schmiedl
EDV-Beratung, Programmierung, Schulung
Loreleystr. 5, 94315 Straubing, Germany
Tel. (0 94 21) 74 01 06
Public Key: http://xss.de/stefan.public

shhhh ... I can't hear my code!

"Chris Uppal" <[hidden email]> writes:

If you want to handle a lot of data quickly, why not take a look
at MySQL for Windows?  This has an ODBC driver, and is very, very
fast indeed.  You can even use Access simply as a front-end to MySQL.
As its ODBC, Dolphin should work fine with it.

I have installed MySQL for this reason.

Steve Zara
>
>    -- chris
>
>
>

"Bill Schwab" <[hidden email]> writes:

>Larry,
>
>> Don't know if they are compatible, but have you tried MySQL or the
>> PostGRE databases?  I believe both are public domain.
>
>No, but, I would like to try one or both.  Licensing as well as a good
>connection from Squeak would be deciding factors.  Right now, Access and
>ODBC are largely meeting my needs, and there's a possible move to Oracle
>looming for instituational reasons.

Oracle will definitely handle anything you throw at it!  My suggestion
would be a switch to MySQL before Oracle, as Access is non-standard in
many ways (like SQL syntax), so this would allow migration to something
more standard (and highly compatible with Oracle).

"A SERFer" <[hidden email]> wrote in message
news:[hidden email]...
> ...
> Oracle will definitely handle anything you throw at it!
>...

Unless you want to do anything esoteric like stepping backwards in (or
randomly indexing into) a result set :-)

Regards

Blair

Blair McGlashan wrote:
Not to start a "your database can't do X" chain, but dumping a table
to a file is also something which in Oracle requires programming, and
most databases support that, even if in some cases it's not a terribly
good idea to try.  My point is that each database has _something_
it doesn't do or doesn't do well and, as we know, popularity is
no sure measure of quality or true success.  Moreover, it's possible
to configure and use Oracle in a manner which makes it perform
as sluggishly as anything, although you really have to try.... (:-)}

[snip]

--
---------------------------------------------------------------------
 Jan Theodore Galkowski                           [hidden email]
 The Smalltalk Idiom                     http://www.algebraist.com/
*********************************************************************
             "Smalltalk?  Yes, it's really that slick."
---------------------------------------------------------------------
Want to know more?  Check out
           http://www.dnsmith.com/SmallFAQ/
           http://www.object-arts.com/DolphinWhitePaper.htm
           http://st-www.cs.uiuc.edu/users/johnson/smalltalk/
*********************************************************************

"Jan Theodore Galkowski" <[hidden email]> wrote in message
news:[hidden email]...
My comment was intended to be sarcastic. Personally I am no fan of Oracle,
because from a development perspective I have always found it limited, and
the tools are poor (the GUI's, when they exist, are woeful, or at least they
were when I last looked). I am sure that highly-paid DBAs love it (after all
it is why they are highly paid), but it does not make life easy for
programmers.

Regards

Blair

From: http://otn.oracle.com/tech/oci/htdocs/OCI9i_NFO.html

OCI Enhancements
A number of enhancements to OCI improve performance and ease development of
applications.  These enhancements include scrollable cursors, connection
pooling, and improved globalization support.
Scrollable Cursors
Cursors are used in Oracle to manage result sets from SQL operations.
Traditionally, OCI has supported only a forward scrolling cursor.  With a
forward scrolling cursor, records are fetched in a set order, and the
application cannot move backward to access a record.  A scrollable cursor,
however, can move forward and backward, and can seek any desired record in
the cursor.  Such operations are common in applications that present results
sets in scrolling windows.  With a scrollable cursor, application developers
do not need to create and manage their own buffer for the records.
--
James Foster
Fargo, ND
james at foster dot net



"Blair McGlashan" <[hidden email]> wrote in message
news:a43g22$1c2ats$[hidden email]...

Blair McGlashan wrote:
Blair, yes, I agree.  Their console level, non-GUI interface reminds
me of
a DEC RSX-11 I used to use....  The Windows based stuff is somewhat
better
but they are intended to be used by themselves and don't integrate
easily
with other applications. In fact, most programming support they offer
tends
to be "closed world":  Yes, they have Java, but you should use
theirs.  Yes,
they support scripting, but you really should write in PL/SQL.

The SQL they support is okay and has a couple of nice things, notably
TRUNCATE TABLE and CREATE TABLE FOO AS SELECT ....  Their UPDATE
statement
is baroque.  Of course, Informix and Pervasive both only support the
same
baroque thing, and I've used those a lot. DBAs like Oracle because its
easier to administer in several respects, like storage, etc.  However
their user setup and permissions are a lot of work to do without using
scripts or tools.

The high end of the database world is very specific by feature.  For
quite
a while Informix had some very nice concurrent system setups.  I don't
know,
really, how things are changing now that they've been bought by IBM.

 --Jan

[snip]

--
---------------------------------------------------------------------
 Jan Theodore Galkowski                           [hidden email]
 The Smalltalk Idiom                     http://www.algebraist.com/
*********************************************************************
             "Smalltalk?  Yes, it's really that slick."
---------------------------------------------------------------------
Want to know more?  Check out
           http://www.dnsmith.com/SmallFAQ/
           http://www.object-arts.com/DolphinWhitePaper.htm
           http://st-www.cs.uiuc.edu/users/johnson/smalltalk/
*********************************************************************