pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of Views
2+ messages / 2 participants
[nested] [flat]
* Performance of Views
@ 2005-02-28 17:31 Steffen Boehme <Steffen@boemm.de>
2005-03-03 15:04 ` Re: Performance of Views Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 2+ messages in thread
From: Steffen Boehme @ 2005-02-28 17:31 UTC (permalink / raw)
To: pgsql-sql
Hello there,
i have a short question ...
I have a few tables (at the moment "only" 3 for testing), over which
will by made a query in this form:
SELECT
a.orderitem_id,
a.transaction_id,
a.order_id,
a.shop_id,
a.quantity,
a.price,
b.affiliate_id,
c."type"
FROM
ss_order_orderitems a
LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id,
ss_shops c
WHERE
(a.order_id = b.order_id OR b.order_id IS NULL) AND
a.shop_id = c.shop_id;
The query will get later a few more conditions ...
Now is the problem, that the performance is not realy good ... and i
think about the best solution for such a query and found three possibilitys:
1. a simple select over the three tables (one of them contains 160000
entrys in the moment and it's growing) in the form like above (the db is
mysql 4.1.x)
I think this solution is not very perfomant ...
2. move the data to a postgresql-db with the same structur and create a
view, wich makes the same query ...
Is the performance for the same query different between a simple select
and a view!?
If so, i can forget the view ...
3. put the data with the above query in one big table ...
I know, thats no good db-structur, but i don't know how i could make it
better ...
The main-question at the moment iss ...
Is the performance of the View-Method better then the first Method on
the existing tables!?
I hope of a view hints ...
Thanks
Steffen
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Performance of Views
2005-02-28 17:31 Performance of Views Steffen Boehme <Steffen@boemm.de>
@ 2005-03-03 15:04 ` Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 2+ messages in thread
From: Christoph Haller @ 2005-03-03 15:04 UTC (permalink / raw)
To: Steffen Boehme <Steffen@boemm.de>; +Cc: pgsql-sql
There is no difference in run-time between a VIEW
and executing the underlying SELECT because it's the same.
You may move your question to the [PERFORMANCE] list
for hints to speed up your query.
Regards, Christoph
Steffen Boehme wrote:
>
> Hello there,
>
> i have a short question ...
>
> I have a few tables (at the moment "only" 3 for testing), over which
> will by made a query in this form:
>
> SELECT
> a.orderitem_id,
> a.transaction_id,
> a.order_id,
> a.shop_id,
> a.quantity,
> a.price,
> b.affiliate_id,
> c."type"
> FROM
> ss_order_orderitems a
> LEFT JOIN ss_order_affiliate_tracking b ON a.order_id = b.order_id,
> ss_shops c
> WHERE
> (a.order_id = b.order_id OR b.order_id IS NULL) AND
> a.shop_id = c.shop_id;
>
> The query will get later a few more conditions ...
>
> Now is the problem, that the performance is not realy good ... and i
> think about the best solution for such a query and found three possibilitys:
>
> 1. a simple select over the three tables (one of them contains 160000
> entrys in the moment and it's growing) in the form like above (the db is
> mysql 4.1.x)
> I think this solution is not very perfomant ...
>
> 2. move the data to a postgresql-db with the same structur and create a
> view, wich makes the same query ...
> Is the performance for the same query different between a simple select
> and a view!?
> If so, i can forget the view ...
>
> 3. put the data with the above query in one big table ...
> I know, thats no good db-structur, but i don't know how i could make it
> better ...
>
> The main-question at the moment iss ...
> Is the performance of the View-Method better then the first Method on
> the existing tables!?
>
> I hope of a view hints ...
>
> Thanks
> Steffen
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-03 15:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-28 17:31 Performance of Views Steffen Boehme <Steffen@boemm.de>
2005-03-03 15:04 ` Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox