|Subject||Re: [firebird-support] sql query vs view vs stored proc. performance question|
> We are frequently executing sql queries (every 10-20 seconds) thatI would go with a view, if you want to execute the SQL from different
> 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
> 1. executing sql queries every time
> 2. Creating a view (can a view have a nested, joined, etc statement?)
> 3. Using a stored procedure to execute the query
> 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?
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
* 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 ...
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and