Home » Other » General » Database application design
Database application design [message #201455] Sat, 04 November 2006 07:41 Go to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
Hi!

I have a bit of a philosophical question. Recently I was asked to outline an interface between database and application in a new (small) project. I came across an interesting problem which could be solved in a number of ways but not a single one of those ways is without potential problems.

We have to implement a user friendly functionality - some sort of user result set customization. User can see data in a grid implemented by application GUI. That grid can have as much as 100 columns (e.g. C1, C2, C3,..,C100) but user can have all the columns displayed or just the columns that he/she desires (e.g. C1, C4, C83). In other words user can select any number of columns and any combination. Column selection will be implemented by GUI using checkboxes.

The way I see it I have 2 options:

1) Application can concatenate the columns selected by the user and send them into an oracle stored procedure as IN parameter. For example:

PROCEDURE test1 (p_selected_columns IN VARCHAR2,
                 p_refcursor_o      OUT sys_refcursor)
IS
BEGIN
  ...
  OPEN p_refcursor FOR
    'SELECT ' || p_selected_columns || ' FROM x';

END test1;


It's an "elegant solution" but it has its downsides
a) SQL injection
b) Always hard parsing when called

2.) Always return all the columns user can see and let application hide the unselected columns. BTW., this solution looks nice but I'm not fond of the idea of having always to return 100 columns an then hide 99 of them. Overhead is the word that comes to mind. You see, in order to return all 100 columns there is a number of tables to be joined - in general less columns equals less tables to be joined.

Is there a third way? Please share your thoughts. What would you do (or maybe you already did it yourself) if you would have to implement this kind of functionality? I'm particularly interested in your approach if you would have:
a) A backwater warehouse like subsystem with only 3-5 user concurrently working
b) A heavy duty transaction system with 100's of users

Thanks!
Re: Database application design [message #201542 is a reply to message #201455] Sun, 05 November 2006 16:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I would consider using a database package that returns 100 columns but through other logic I would have stored the column numbers to be returned, the order in which they are to be returned, and even their possible sort sequence. I would only return the columns required and therefore you would know that if three (3) columns were 'in play' that the information would be in the first three columns returned. You would also consider doing all the data type translations in the package.

David
Re: Database application design [message #201553 is a reply to message #201542] Sun, 05 November 2006 18:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think if you benchmark the dynamic SQL concept, you will find that the hard-parse is not a problem.

The times you want to worry about parsing are when:
- Executing SQL inside a loop - you don't want to parse for each iteration.
- An application has hundreds of cursors and hundreds of users. You don't want every user to have "their own" cursors.

Yours will only be done once per call by a user, so the number of re-parses is limited to the number of requests that can be made by your user community. Also, its not a strategy that will be deployed across every interface in the system - its linked to one requirement.

Surely this could not exceed more than a few hundred parses in a five-minute period. In terms of parsing, this is nothing.

Ross Leishman
Re: Database application design [message #201608 is a reply to message #201455] Mon, 06 November 2006 01:19 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
Quote:

I would only return the columns required and therefore you would know that if three (3) columns were 'in play' that the information would be in the first three columns returned.



I think I get the idea behind this approach but I don't quite understand as how this solves the problem.

Let's do a practical test just to elaborate (as how I probably don't understand anything Embarassed). Let's say we have a table X with columns A, B, C, D, E to return. User wants to display columns C and E. Columns inside ref cursor are numbered A1, A2, A3, A4, A5. The way you are suggesting, C should be returned as A1 and E as A2. I would still have to select all 5 columns in this case and still do a hard-parse won't I? The only thing this does is limit the data transfer out of the database.

Quote:

The times you want to worry about parsing are when:
- Executing SQL inside a loop - you don't want to parse for each iteration.
- An application has hundreds of cursors and hundreds of users. You don't want every user to have "their own" cursors.


Yours will only be done once per call by a user, so the number of re-parses is limited to the number of requests that can be made by your user community.



Just like you said it. True, there will be only few hundreds of hard-parses in my particular case. But there is a "political" or "religious" problem going with that. Working with java developers (or .NET, to be fair) always issues a fight. If I would allow this kind of approach to development I would probably get "You did this in project X and it didn't bother you back then so why it is bothering you in project Y? Were you wrong back then? We could do everything in java if you don't know how to." This here is a snowball and I don't have the energy or time to stop an avalanche in the future when building a transactional database application (been there...).


Re: Database application design [message #201798 is a reply to message #201608] Mon, 06 November 2006 16:29 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Okay - referring to my posts only - my understanding is that the structure of the returned record in Oracle Forms is 'fixed', that is, if you want 100 columns, sometime, you always have to have 100 columns defined in the call.

Let's use an example where 5 is the maximum and 2 is the current number of columns being returned. You define 5 columns in your Forms block A1 to A5 (for example). If the 2 columns required were the 'second' and 'fifth' fields of a table and you returned them as the 2nd and 5th column then you would have to 'know' which columns to process in the Form. But if you return them as columns '1' and '2' then you only need to know that you are processing two columns and you can disable and hide the other three columns that exist in the Form. I anticipate that the users will only wish to see the columns in which they are interested (hence the hiding of the other three) and they may wish to see them in the order of table field 5 then table field 2. By doing ALL the type changing and column ordering in the package in the database you will save significantly in total resources and execution time.

I don't see why there would have to be much in the way of parsing in Form as there will be little SQL executed in the Form.

David

[Updated on: Mon, 06 November 2006 16:30]

Report message to a moderator

Re: Database application design [message #201845 is a reply to message #201455] Tue, 07 November 2006 01:06 Go to previous messageGo to next message
sdecman
Messages: 20
Registered: July 2005
Location: Ljubljana, Slovenia
Junior Member
Quote:

Okay - referring to my posts only - my understanding is that the structure of the returned record in Oracle Forms is 'fixed', that is, if you want 100 columns, sometime, you always have to have 100 columns defined in the call.


This is what I wanted to hear Thumbs Up .

If I have 100 columns I always have to return 100 columns and let application hide the rest. We have three-tier architecture and the technology is Oracle and .NET. We don't use Forms, however your approach of having columns numbered might still be usable - depending on what .NET programmers find easier to implement. My job is only to outline the general interface between Oracle and .NET and being an Oracle Developer/DBA I know (big time...) how application developers can literally bring database to a complete stop and then blame the database.

That's why I always see problems from database's side of view - that is what is best for the database putting in second plan how application manipulates the data.


Nevertheless, thanks for all your effort.
Re: Database application design [message #201847 is a reply to message #201845] Tue, 07 November 2006 01:13 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Just remember that all but one of those columns can be 'null', and as it is returned as a record structure, only the pointer is actually moved.

David
Previous Topic: mimeheader_encode
Next Topic: Help me please!!!
Goto Forum:
  


Current Time: Tue Dec 06 08:14:38 CST 2016

Total time taken to generate the page: 0.17680 seconds