the database discussion from "providing views with vfs&

[distantvoices]

That's a virtual file system.

For me, a view is a virtual table in any RDBMS which permits to give out special data without having the user know the pecularities behind (complicated select with outer join and four other tables involved) - in Oracle or Informix, Views which hide away sophisticated select statements are far more performant than the statement issued directly from the requesting application for the views are kinda precompiled.

But, I Have to stress this again, views have nothing to do with file system abstraction. I ask you not to mix up database terms with file system stuff. Else no one knows what the heck we are talking about.

Design a virtual file system which hooks in the underlying implementations without bothering with implementation specifics (which belong to the fs drivers).

Take a pencil, a rubber and a sheet of paper and do some design work. That's where engineering happens mostatime. In /dev/brain and with pen and paper.

Stay safe & sorry for the rant. I'm in a mood for it today.

PS: I know it's not the place to show off with something, but recently I've managed to get a C on a maths exam.

[Candy]

That's a virtual file system.

For me, a view is a virtual table in any RDBMS which permits to give out special data without having the user know the pecularities behind (complicated select with outer join and four other tables involved) - in Oracle or Informix, Views which hide away sophisticated select statements are far more performant than the statement issued directly from the requesting application for the views are kinda precompiled.

If and only if you're selecting on the full table. If you are selecting on a view, you're doing more work than would be required.

Or I've missed a setting in oracle 8i.

[distantvoices]

YOu are of course right, it's shooting a midge with a cannon if you use a view to fetch data out of one single table with no other pecularities.

But in case you have f. ex. 3 tables involved where each of them are connected with n:m-subtables(you know what I mean, these small things you need to resolve a N:M relation) and you are in urgent need to avoid a cross-product result set so the where clause is biiig and sophisticated to no end,
I rather have a view do the dirty work and then pick what I need with a select on that view. It is proven to be far more performant than the "I issue a sophisticated select directly" way.

I reckon that's because oracle or informix like a divide-and-conquer approach more, eh?

No, honestly, at work I'm dealing with informix & oracle on a daily base, so that's where this comes from. Have done quite some tests with *really* bastard select statements.

stay safe

btw: It's oracle 10i here.

[Candy]

But in case you have f. ex. 3 tables involved where each of them are connected with n:m-subtables(you know what I mean, these small things you need to resolve a N:M relation) and you are in urgent need to avoid a cross-product result set so the where clause is biiig and sophisticated to no end,
I rather have a view do the dirty work and then pick what I need with a select on that view. It is proven to be far more performant than the "I issue a sophisticated select directly" way.

I've had a few selects on a:

View, which was on a table, a table and a view, which was on a table, a view (on three tables) and another view, which was on another two tables.

The views-first-select-later performed in 21 minutes on a cluster. The select-first-screw-the-views did so in 7 seconds.

The select was, when fully printed out, more than two pages long.

I reckon that's because oracle or informix like a divide-and-conquer approach more, eh?

No, honestly, at work I'm dealing with informix & oracle on a daily base, so that's where this comes from. Have done quite some tests with *really* bastard select statements.

btw: It's oracle 10i here.

Ah... that can quite explain the bastardous performance of ours . The views were on a few tens of thousands of lines in each table, the end view was a hundred thousand and the selected portion was only 20000 of those. It did involve one mathematical expression between rows, so that might slow it down more than normal. Also, the table was high in change.

We changed it to sum it up a little more so that the output was more meaningful in its own right. Knowing the exact state of each second isn't useful when trying to get a generic picture.

After that, we had to postprocess it with php on a server, which took around 9 minutes beforehand... afterwards, it was instantaneous since it only got a few hundred rows.

[distantvoices]

The biggest showstopper in a fine and dandy select can also be if you use an OR expression to test two conditions on one field in the same table - that OR is maximizing the work the db has to do in order to achieve the desired result - it takes way longer than your average select, so it might be better to issue two successive selects, put the results in a temp table and fetch from there.

That select you mention - wow, what a bastard. *rofl* Exactly the right thing to debug. Aren't there by any chance some cross product results hidden in the dark?

BTW: I suppose that we split the database discussion to a thread in the programming section? We *might* frighten some of the noobs to no end with that stuff I reckon.