On Tue, Oct 30, 2012 at 12:19 AM, trskopo <trskopo@...> 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]