Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #244 > unrolled thread

Difference in JDBC result

Back to article view | Back to comp.lang.java.databases

Contents

  Difference in JDBC result "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: Difference in JDBC re "Martin Gregorie" <martin.gregorie@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
      Re: Difference in JDBC re "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
        Re: Difference in JDBC re "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
          Re: Difference in JDBC re "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: Difference in JDBC re "Martin Gregorie" <martin.gregorie@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

#244 — Difference in JDBC result

  To: comp.lang.java.databases
We're using jTDS to connect to a backend SQL Server 2000 instance. The
connection is working fine.

What we want to do is write a utility that will calculate maximum row
size for each table in a given database. The script we're using looks
something like this:

Connection con = Connector.getConnection(); //this works fine and has
been verified...
String query = "SELECT * FROM " + table; //general query to get
metadata...table is provided as argument...
PreparedStatement st = con.prepareStatement(query);
ResultSet rs = st.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
String query2 = "SELECT MAX(LEN(?)) AS Maximum FROM " + table;
PreparedStatement st2 = con.prepareStatement(query2);
int totalSize = 0;
for (int i = 1; i <= cols; i++) {
	String colName = meta.getColumnName(i);
	st2.setString(1, colName);
	ResultSet rs2 = st2.executeQuery();
	if (rs2.next()) {
		int fieldSize = rs2.getLong("Maximum");
		totalSize += fieldSize;
		out.println("<blockquote>" + colName + ": " + fieldSize + "</
blockquote>");
	}
	rs2.close();
}
st2.close();
rs.close();
st.close();
con.close();
out.println("Maximum total row size = " + (totalSize));

When I run this application against a particular table, I get the
following results:

Getting metadata for table WF_Approval_Methods

id: 2
type: 4
name: 4
description: 11

Maximum total row size = 21

All would appear to be fine, with one big problem...When I run the
following query in Query Analyzer against the same table I get a
different value:

SELECT MAX(LEN(id)) AS Maximum FROM WF_Approval_Methods

returns a value

36.

Every other value is also incorrect. When I run the queries in
succession I get the following values:

id: 36
type: 1
name: 8
description: 34

With a total of 79, much different than my generated total of 21.

Any ideas what I'm doing wrong here?

Thanks.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [next] | [standalone]

#245 — Re: Difference in JDBC re

  To: comp.lang.java.databases
On Thu, 11 Sep 2008 15:50:27 -0700, Tom Cole wrote:

> On Sep 11, 6:45-apm, Tom Cole <tco...@gmail.com> wrote:
>> We're using jTDS to connect to a backend SQL Server 2000 instance. The
>> connection is working fine.
>>
>> What we want to do is write a utility that will calculate maximum row
>> size for each table in a given database. The script we're using looks
>> something like this:
>>
>>
>> Any ideas what I'm doing wrong here?
>>
It looks to me as if your code is calculating the amount of space needed 
hold the data in a row but is ignoring overheads introduced by the DBMS 
such as padding between columns, index structures, length indication for 
a variable length column, etc.

As the overheads are implementation specific its probably not possible to 
calculate them using information returned by JDBC and almost certainly 
impossible to write a general solution. If the information can be 
retrieved at all it is likely to be in system tables which vary wildly in 
number, content and name between database implementations.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#247 — Re: Difference in JDBC re

  To: comp.lang.java.databases
On Sep 12, 7:54 am, Martin Gregorie
<mar...@see.sig.for.address.invalid> wrote:
> On Thu, 11 Sep 2008 15:50:27 -0700, Tom Cole wrote:
> > On Sep 11, 6:45 pm, Tom Cole <tco...@gmail.com> wrote:
> >> We're using jTDS to connect to a backend SQL Server 2000 instance. The
> >> connection is working fine.
>
> >> What we want to do is write a utility that will calculate maximum row
> >> size for each table in a given database. The script we're using looks
> >> something like this:
>
> >> Any ideas what I'm doing wrong here?
>
> It looks to me as if your code is calculating the amount of space needed
> hold the data in a row but is ignoring overheads introduced by the DBMS
> such as padding between columns, index structures, length indication for
> a variable length column, etc.
>
> As the overheads are implementation specific its probably not possible to
> calculate them using information returned by JDBC and almost certainly
> impossible to write a general solution. If the information can be
> retrieved at all it is likely to be in system tables which vary wildly in
> number, content and name between database implementations.
>
> --
> martin@   | Martin Gregorie
> gregorie. | Essex, UK
> org       |

Yeah and the bigger problem I will have is that this will be deployed
on SQL and Oracle installations which also differ wildly.
Unfortunately I just don't know how to go about estimating how much a
new row will cost me in a table.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#249 — Re: Difference in JDBC re

  To: comp.lang.java.databases
Tom Cole wrote:
> On Sep 12, 7:54 am, Martin Gregorie
> <mar...@see.sig.for.address.invalid> wrote:
>> On Thu, 11 Sep 2008 15:50:27 -0700, Tom Cole wrote:
>>> On Sep 11, 6:45 pm, Tom Cole <tco...@gmail.com> wrote:
>>>> We're using jTDS to connect to a backend SQL Server 2000 instance. The
>>>> connection is working fine.
>>>> What we want to do is write a utility that will calculate maximum row
>>>> size for each table in a given database. The script we're using looks
>>>> something like this:
>>>> Any ideas what I'm doing wrong here?
>> It looks to me as if your code is calculating the amount of space needed
>> hold the data in a row but is ignoring overheads introduced by the DBMS
>> such as padding between columns, index structures, length indication for
>> a variable length column, etc.
>>
>> As the overheads are implementation specific its probably not possible to
>> calculate them using information returned by JDBC and almost certainly
>> impossible to write a general solution. If the information can be
>> retrieved at all it is likely to be in system tables which vary wildly in
>> number, content and name between database implementations.
>>
>> --
>> martin@   | Martin Gregorie
>> gregorie. | Essex, UK
>> org       |
> 
> Yeah and the bigger problem I will have is that this will be deployed
> on SQL and Oracle installations which also differ wildly.
> Unfortunately I just don't know how to go about estimating how much a
> new row will cost me in a table.

Why on earth would you want to try to estimate that?

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#251 — Re: Difference in JDBC re

  To: comp.lang.java.databases
On Sep 12, 6:55 pm, Silvio Bierman <sbier...@jambo-software.com>
wrote:
> Tom Cole wrote:
> > On Sep 12, 7:54 am, Martin Gregorie
> > <mar...@see.sig.for.address.invalid> wrote:
> >> On Thu, 11 Sep 2008 15:50:27 -0700, Tom Cole wrote:
> >>> On Sep 11, 6:45 pm, Tom Cole <tco...@gmail.com> wrote:
> >>>> We're using jTDS to connect to a backend SQL Server 2000 instance. The
> >>>> connection is working fine.
> >>>> What we want to do is write a utility that will calculate maximum row
> >>>> size for each table in a given database. The script we're using looks
> >>>> something like this:
> >>>> Any ideas what I'm doing wrong here?
> >> It looks to me as if your code is calculating the amount of space needed
> >> hold the data in a row but is ignoring overheads introduced by the DBMS
> >> such as padding between columns, index structures, length indication for
> >> a variable length column, etc.
>
> >> As the overheads are implementation specific its probably not possible to
> >> calculate them using information returned by JDBC and almost certainly
> >> impossible to write a general solution. If the information can be
> >> retrieved at all it is likely to be in system tables which vary wildly in
> >> number, content and name between database implementations.
>
> >> --
> >> martin@   | Martin Gregorie
> >> gregorie. | Essex, UK
> >> org       |
>
> > Yeah and the bigger problem I will have is that this will be deployed
> > on SQL and Oracle installations which also differ wildly.
> > Unfortunately I just don't know how to go about estimating how much a
> > new row will cost me in a table.
>
> Why on earth would you want to try to estimate that?- Hide quoted text -
>
> - Show quoted text -

When deploying an application on a client's system, they want to know
(within bounds) how much disk space/resources they will need in the
future to support an application's database. The goal is to provide
them with a worst case scenario (i.e. maximum row size for all tables
affected) for each new record. Then they would do an analysis to
determine how many new rows they would expect in a week/month/year/
decade, apply that to the formula and get at least some form of
educated guess how big these tables will be in the future.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [next] | [standalone]

#252 — Re: Difference in JDBC re

  To: comp.lang.java.databases
On Mon, 15 Sep 2008 09:49:00 -0700, Tom Cole wrote:

> 
> When deploying an application on a client's system, they want to know
> (within bounds) how much disk space/resources they will need in the
> future to support an application's database. The goal is to provide them
> with a worst case scenario (i.e. maximum row size for all tables
> affected) for each new record. Then they would do an analysis to
> determine how many new rows they would expect in a week/month/year/
> decade, apply that to the formula and get at least some form of educated
> guess how big these tables will be in the future.
>
That would indeed be a useful tool.
 
Your best move may be to parse the schema (assuming there is one written 
in DDL) and combine that with a config file containing a list of 
overheads for the various objects (tables, indexes, etc) that can be 
included in the schema. This list needs to include any padding used to 
align column types on word boundaries, so its probably a nightmare to set 
up, but at least it only needs to be done once per DBMS.

You can get most of what you need by using JDBC to read the meta-data, 
but work through the DatabaseMetaData class rather than fetching column 
names and examining them as you have  been doing. This can get most of 
the stuff you need in terms index and table definitions, but still won't 
tell you about: 

- per-row overheads in tables and indexes

- any padding used to align numeric columns on word boundaries

- data representation overheads such as length words or null terminators

In short, you'll still need the DBMS-specific config file I mentioned 
above.

Of course, you could always just load a representative data set into the 
database, measure the space occupied and scale that to match the client's 
data volume, but that's probably to write as a shell script rather than 
in Java.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.databases

csiph-web