Re: [firebird-support] Stored Proc vs View, which one has better performance?
On Tue, Oct 30, 2012 at 12:19 AM, trskopo trsk...@yahoo.com wrote: I want to select from that table, all records that has sum(qty) 0 group by id_cst and id_gd. I have 2 options to do that : 1) create a view with a ddl like this : create view tmp (id_cst, id_gd, qty) as select id_cst,id_gd,sum(qty) from Tbl1 group by id_cst,id_gd then select that view with ddl : select * from tmp where qty 0 2) create a stored procedure that returns all records which qty 0 Between those options, with one has a better performance? Have you considered just select id_cst,id_gd,sum(qty) qtySum from Tbl1 group by id_cst,id_gd having qtySum 0 There's a slight benefit to the view or stored procedure because it doesn't have to be recompiled and optimized so often, but it's a pretty simple statement and you'd have to use it a lot to amortize the time you spent creating the view or procedure. As for whether the view or the procedure would be faster - in theory they should be about the same, but only you can demonstrate the difference between theory and practice. This should be easy to test and testing it on your actual data will give the most reliable results. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] Stored Proc vs View, which one has better performance?
Hi all, I have a table, let say Tbl1, which structure something like this id_cst int, id_gd int, qty int. I want to select from that table, all records that has sum(qty) 0 group by id_cst and id_gd. I have 2 options to do that : 1) create a view with a ddl like this : create view tmp (id_cst, id_gd, qty) as select id_cst,id_gd,sum(qty) from Tbl1 group by id_cst,id_gd then select that view with ddl : select * from tmp where qty 0 2) create a stored procedure that returns all records which qty 0 Between those options, with one has a better performance? Thanks and regards, Sugi.
RE: [firebird-support] Stored Proc vs View, which one has better performance?
Sugi, I have a table, let say Tbl1, which structure something like this id_cst int, id_gd int, qty int. I want to select from that table, all records that has sum(qty) 0 group by id_cst and id_gd. I have 2 options to do that : 1) create a view with a ddl like this : create view tmp (id_cst, id_gd, qty) as select id_cst,id_gd,sum(qty) from Tbl1 group by id_cst,id_gd then select that view with ddl : select * from tmp where qty 0 2) create a stored procedure that returns all records which qty 0 Between those options, with one has a better performance? The performance should be almost identical -- not worth worrying about. There would/could be other reasons for choosing one approach versus the other, but performance for the problem you outlined is not one of them. Depending on the number entries in Tbl1, I would argue that neither approach would be best. A temporary table where the current total_qty would be maintained would be a better approach, but there are issues about potential transaction deadlocks which you would need to work through, but they are workable. Sean
