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