| Subject | Re: [firebird-support] Slow left join with stored procedure |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-08-07T22:28:53Z |
At 12:48 AM 8/08/2010, you wrote:
Use SSPs for the (usually rare) occasions when they can be used to output data *directly to the client* that is impossible to get directly from tables. That seems to be not the case here, since you have two naturally linked tables.
At least from the small amount of info you provide, you should be able to get the set you want by natural means that enable the engine to use indexes. If you like derived tables, then use one on the right-hand side if you want, but use a natural aggregate query to form the set, never a SSP. If you want to use that same right-hand set for other queries, consider encapsulating the natural query in a view so that it is a reusable object.
Tip: also, in your example, you are not paying sufficient attention to the correct use of table and alias identifiers. It is clearly documented in the release notes that the bad habits people acquired in this regard using Fb 1.5 and InterBase would bite you in v.2+. Go back and review the migration guide and the DML chapter of the V.2 release notes. I mention this because I saw your recent post in the IBObjects list, describing a problem that happens when you have overlooked old queries in your applications that are made invalid by the enforcement of the rules by the newer engine versions.
./heLen
>I have two tables. One contains a list of orders and other items with each order. I created a stored procedure that returns me the total value of items per order number and the key field (orderno), the order number. To return all order data and total items use this:For performance, it is all wrong. You are trying to join your left-hand set to a right-hand set that is a derived table drawn from a stored procedure. Just because this is technically possible, it does not mean it is sensible to do.
>
>Select
>Ordl.OrderNo,
>Ordl.ClientName,
>Ordl.Age,
>
>Itens.TotalValue
>
>from
>OrderList Ordl
>left join ( Select OrderNo, TotalValue from MyStoredProcedure ) Itens
>on ( Itens.OrderNo=Ordl.OrderNo )
>
>
>This is very slow. I don't know if I do something wrong.
>How to improve performance?For any retrieval with SQL, you will get best performance by keeping things as simple as possible. Designing intermediate sets that have to be manufactured by calling a selectable SP repeatedly is *never* better than obtaining the output directly from real data. The engine has no indexes to work with when you try to use SSP output in joins or subqueries.
Use SSPs for the (usually rare) occasions when they can be used to output data *directly to the client* that is impossible to get directly from tables. That seems to be not the case here, since you have two naturally linked tables.
At least from the small amount of info you provide, you should be able to get the set you want by natural means that enable the engine to use indexes. If you like derived tables, then use one on the right-hand side if you want, but use a natural aggregate query to form the set, never a SSP. If you want to use that same right-hand set for other queries, consider encapsulating the natural query in a view so that it is a reusable object.
Tip: also, in your example, you are not paying sufficient attention to the correct use of table and alias identifiers. It is clearly documented in the release notes that the bad habits people acquired in this regard using Fb 1.5 and InterBase would bite you in v.2+. Go back and review the migration guide and the DML chapter of the V.2 release notes. I mention this because I saw your recent post in the IBObjects list, describing a problem that happens when you have overlooked old queries in your applications that are made invalid by the enforcement of the rules by the newer engine versions.
./heLen