Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Is it possible to run SQL select in Oracle and SQL Server to get a large recordset and import it straight into an array, looking to avoid using rownext

by sundialsvc4 (Monsignor)
on Nov 25, 2013 at 16:50 UTC ( #1064244=note: print w/ replies, xml ) Need Help??


in reply to Is it possible to run SQL select in Oracle and SQL Server to get a large recordset and import it straight into an array, looking to avoid using rownext

Also ... what immediately pops into my head is this:

“Gee, ’6000 to 8000 rows’ isn’t that much data ... not at all.   Especially not for Oracle.   Therefore, why is it ‘taking forever?’   Perhaps this is the true root-cause of the problem.   I smell a dead mouse somewhere ...

Sure, there are DBI routines that will “fetch an array,” but if there is some kind of bottleneck going on here, those would probably take forever, too.   Consider therefore that there might be something to do with your algorithm, with the exact way that you are connecting to Oracle and so-on, which might be causing an extreme but altogether-unjustified slowdown.

Great example of this (from another context):   I was using SQLite basically for the first time, and it was dog slow.   Knowing SQLite’s reputation for speed, I didn’t know what I was doing wrong ... until I saw that disk I/O was going through-the-roof for this process ... especially writes.   It turns out that, with this system, you basically must use transactions, even for reads.   So I made this change, and whoosh!!   No amount of “diddling with my code” would have made any difference at all.   Perhaps a similar thing is going on here ... and, mind you, it could in this case very well have something to do with transactions, isolation-levels / repeatable-read, and so forth.   Oracle, like all pieces of complex software, “makes certain assumptions,” such that you might not know that they are happening but that might have a profound impact on your software’s performance.


Comment on Re: Is it possible to run SQL select in Oracle and SQL Server to get a large recordset and import it straight into an array, looking to avoid using rownext
Re^2: Is it possible to run SQL select in Oracle and SQL Server to get a large recordset and import it straight into an array, looking to avoid using rownext
by afoken (Parson) on Nov 26, 2013 at 19:28 UTC

    Right. A simple SELECT without a complex WHERE and no LOBs returning "just" 8000 rows should happen almost instantly on Oracle. If not, start by issuing the same SQL statement in SQLplus or similar, if that is slow, consult the Oracle admin.

    About fifteen years ago, I filled an Oracle table with a few thousand rows of simple address data (id, name, street, town, phone, email), the following "SELECT * FROM $table" several minutes. My Oracle guru just muttered "primary key?", and of course, my CREATE TABLE lacked the PRIMARY KEY on the id column. So Oracle had do to a full table scan for each record I selected. Adding the primary key to the ID column reduced the time required to a few milliseconds.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1064244]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (7)
As of 2014-08-23 11:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (173 votes), past polls