[Gllug] A programming question. long.

[Alex Hudson]

On Sun, Jul 08, 2001 at 08:42:05PM +0100, Bruce Richardson wrote:
> > I didn't say it made the database faster (although in some cases it
> > would), it makes the system perform better. If you want a sorted result,
> > for example, you don't have the result set returned to you and sort it
> > yourself, you ask the db to do it - it's faster. If you want to see rows
> > which aren't being 'dealt with' (in your example), you use a where
> > clause, you don't have the whole set returned to you. Etcetera.
> 
> I missed that - that's a basic misunderstanding of database servers and
> triggers/views.

Nope.

> You don't need triggers for sorting and it certainly doesn't boost
> performance.

I don't see where in that passage I mention sorting via triggers. I mention
sorting because it is an algorithm more efficiently performed locally by the
db. I'm sorry, you can't argue with that, it's accepted fact. I'm sure you
know what indexes are...

> By passing your SQL statement to the server you pass the load to the
> server.

Yes? So? That's _precisely_ my point - by distributing load to those points
in a system which perform the work the most efficiently, you make the system
perform better. I don't see what you're arguing against?

> Placing a WHERE clause in the SQL statement passed to the server is
> completely sufficient.  Creating a view based on a SELECT...WHERE
> statement offers no performance gain.

I think you'll find it does. A view / stored proc / trigger are candidates
for optimization, because the database has a priori knowledge - by using the
same stored proc many times, the database is relieved of the need to keep
recompiling the SQL at the very least. By creating a view (presumably with
the intention of using it more than once..) you give the database a priori
knowledge, which it uses to increase the efficiency of its response.

> The reason to create such a view would be to restrict the records
> returned to the client.

That's your reason for creating a view, it doesn't follow that is their
raison d'etre. To me, using a view as an access control suggests the data
should be separated into two or more tables - whether or not this is
actually the case depends on context, but it certainly raises the question
in my mind.

Cheers,

Alex.


-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug