| Database application design [message #201455] |
Sat, 04 November 2006 07:41  |
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 #201553 is a reply to message #201542] |
Sun, 05 November 2006 18:50  |
rleishman
Messages: 3728
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 |
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  ). 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 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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 #201847 is a reply to message #201845] |
Tue, 07 November 2006 01:13 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.