Re: Question regarding OOP and database access

leodippolito@xxxxxxxxx wrote:
>
> This way, if the Wheel table changes I am ok with the sql code. I don't
> need to track anything in DB layers. If I ask for a car or truck
> object, it will return a car or truck with the new-version wheel.
>
There may be specific cases where you might want to do something like
this, but not in general. You don't want to approach software design
in a way that rules out using features of the RDBMS. In general,
prefer joins.

> The cost of this is the n-round-trips that I have to make to the DB
> layer in the business layer. I lose the power of SQL join.
>
Right, not that it always matters. But there are examples of projects
that have failed because less gifted developers made all database
access work this way.

> Another possibility I can see would be to use an O/R mapping library.
> The cost would be runtime processing.

These tools bind result sets to program variables, which does incur
runtime processing, but is useful, and the cost is small relative to
the cost of the query. Unfortunately, they often do a lot more which
is not helpful, like caching, mapping data members directly to tables,
generating SQL which can not then be tuned, etc.
>
The following approach is widely used, and works well.

One, move all of your SQL text out of your software and into
configuration files. Parameterize it with placeholders for arguments.
In Java, the simplest approach is to use property files, which support
parameterized values. Access the SQL values by name in your
application.

Two, make sure that the queries and updates in your app use names
rather than position, and that insert statements specify the column
names to receive values.

You now have a certain amount of flexibility where you can isolate
changes to the configuration file, without immediately changing your
code. You can rename columns in select statements, since you can alias
them to the old names. You can modify and fine tune the SQL statement
as long as select statements return no fewer columns. You can add
columns to select statements. Insert and update statements can be
modified to take default values for new columns, if table structures
change.

If you can find an O/R mapping tool that allows you to specify your SQL
statements in XML files, and doesn't do much more apart from binding
the results by name to data members, that would probably be the best
choice.

Regards,
Daniel Parker

.

Relevant Pages

  • Re: FTS Performance in SQL 2005
    ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... Can you post you query plans and the output of statistics IO ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Multiplicity, Change and MV
    ... and SQL Server were making enough gains in tackling some of the ... seeing significant cost savings when they use products such as U2 from ... reading the database (unless they suffer the cost of the SQL-MV ... "upgrade" to a SQL DBMS to get the missing features, ...
    (comp.databases.theory)
  • Re: Use of large field definitions for small values
    ... A lot of people who dev think single user when they are writing queries; say a query costs 10,000 logical reads thats around 78MBytes of data SQL Server needs to process; that is fine for a single concurrent user but what happens when 100 people run it and now the box has ... So, to answer your question, if your data volumes are such and you just bloat column sizes out then you will reduce your scalability threshold, you will also increase the size of backups, the size of data transmitted between server and clients (again, cost that out for the number of users you have concurrently). ... Anyway, this is academic because you should size columns acording to the data that will fit in there; for instance if you had a column that held the number of minutes in a day then we know that is finite and is between 0 and 1,440 so that will fit in smallint so that's all you need; no point in making it int or especially not bigint. ... Tony Rogerson, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: ACCELERATING STORE PROCEDURE
    ... original raw data is inserted with bcp utility, ... (possible Cartesian join) ... Steps 5 - 17 might be replaced with a SQL statement something like ... One of the rules of efficient programs is to use just SQL code (no PL/ ...
    (comp.databases.oracle.server)
  • Re: SQL Q: Proper and best way to join 3 tables?
    ... Rob Williamson wrote: ... > Basically I have an inventory table, a cost table and a comments table. ... SQL> create table cost ...
    (comp.databases.oracle.server)