MathGroup Archive 2002

[Date Index] [Thread Index] [Author Index]

Search the Archive

Re: odbc vs jdbc for database access in Mathematica

  • To: mathgroup at smc.vnet.net
  • Subject: [mg38061] Re: [mg38039] odbc vs jdbc for database access in Mathematica
  • From: "Werner Schuster" <Schuster at deltasoft.at>
  • Date: Thu, 28 Nov 2002 14:08:15 -0500 (EST)
  • Reply-to: <Schuster at deltasoft.at>
  • Sender: owner-wri-mathgroup at wolfram.com

> the code works MUCH slower than the equivalent odbc-based one on NT. I
> retrieve A LOT of data so this is really annoying. So I have two
> questions:

One problem might be the line
  dbUrl = "jdbc:sybase:Tds:"<>$DBServer<>":"<>$DBPort<>"/"<>database,
especially the DBPort & DBServer might be the problem; 
if that communication goes over the network system, that might
be one reason of the slow speed, especially if the ODBC access
works without going over the network);


> 1. Is it one of the Mathematica functions that make it slow (the
> conversion of the result of the sql query into Mathematica table) or
> is it some fundamental java-related problem (JLink, jdbc etc)?

One problem (that I can think of) would be that you make a call
to a Java method for each row; if you have many rows & many colums,
that results in many calls from Mathematica into Java; 

The problem with that is, that a call from Mathematica to Java 
causes a lot of work (converting arguments, calls with MathLink to start
the Java methods, Java code accepts the Mathlink call, again Mathlink
calls to return result, converting arguments back to Mathematica types).
As you can see, that is a lot of stuff happening each time you fetch 
*one* item from a Result Set. Now imagine doing that for a result 
set with hundreds or thousands of items (and this is *after* the 
jdbc-driver has done its job).

Now, one solution I can think of (and I don't promise that it will
work or be faster than your solution), would be to make as few calls into Java as possible, and transfer as much data with each call. 

You can fetch a whole column (at once) from the ResultSet (method getArray(String colName) ).
Thus you would get an array for each column. This is not 
possible for a row, since they are likely to contain different types,
and an Java array can only have one type. 
ie. if you have an table with n columns, you would only have to 
make n calls into Java, where each call transfers the whole column data
in one go, and put that in your Mathematica result array/table. 

Again, that is just the first idea that popped into my head, I haven't
tried it; 

murphee 
-- 
Werner Schuster (murphee)
Student of Technical Mathemathics and
SoftwareEngineering and KnowledgeManagement


  • Prev by Date: Find all complex root in a region
  • Next by Date: Re: copying cells in Mathematica 4.2
  • Previous by thread: odbc vs jdbc for database access in Mathematica
  • Next by thread: Plot with multiple (> 2) horizontal axes