Difference in JDBC results and Query Analyzer results

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.

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:
>
> 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");

*** s/b int fieldSize = rs2.getInt("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.


Sorry, see correction above. I pasted some incorrect code. Results
remain the same...

On Sep 11, 6:50*pm, Tom Cole <tco...@gmail.com> 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:
>
> > 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");
>
> *** s/b * * * * * int fieldSize = rs2.getInt("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.
>
> Sorry, see correction above. I pasted some incorrect code. Results
> remain the same...- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I actually found what I was looking for. What I really wanted was the
maximum field length, not the maximum length of data in the row.
Therefore I used the call meta.getColumnDisplaySize(), which returned
what I was looking for.

Thanks.

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 |

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.

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?

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 possibleto
> >> 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 wildlyin
> >> 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.

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 |