> We are frequently executing sql queries (every 10-20 seconds) that
> involve joins with 4-5 tables with 10 000 - 50 000 rows each. And we
> are also ordering the result with order by clause.
>
> We indexed all relevant columns.
>
> I was wondering about the best solution speed-wise (memory we have
> enough):
>
> 1. executing sql queries every time
> vs
> 2. Creating a view (can a view have a nested, joined, etc statement?)
> vs
> 3. Using a stored procedure to execute the query
> vs
> 4. Any other solution
>
> Before I start testing, I would like to ask the experienced, or
> experts who know how the engine works: which solution should yield the
> best speed?

I would go with a view, if you want to execute the SQL from different
places and if you don't need any particular procedural logic, because:

* the BLR of the query gets stored in the system table, which should
give you a bit better performance compared to a SQL statement, if
noteable anyway
* the view is accessible easier (e.g. select * from my_view and not a
somewhat huge SQL statement)
* you won't be able to change the underlaying tables structure due to
dependency checking. With a regular SQL statement, e.g. in your
application, you are in trouble at run-time
* compared to a stored procedure, an existing index on an underlaying
table can be used from when querying the view with a WHERE clause when
doing a select * from my_view where ...




--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com