Re: Performance Query

[prev] [thread] [next] [Date index for 2004/09/07]

> My unerstanding is that it will be split into something like=20
> this (pesudocode):
> <pre>
> while(SELECT id, name, group_id, type_id, access_id FROM USER){
>    SELECT name FROM GROUP  g WHERE g.id=3Dgroup_id
>    SELECT name FROM TYPE   t WHERE t.id=3Dtype_id
>    SELECT name FROM ACCESS a WHERE a.id=3Daccess_id
> }
> </pre>


You would presumably have multiple users in each group - once you've =
retrieved group N you wouldn't need to retrieve that again. Ditto =
access. So at most you're going to have DISTINCT group_id + DISTINCT =
access_id + DISTINCT type_id queries there.

This is actually going to reduce the total amount of data you transfer =
from the DB - the most common bottleneck. Taking your 1,000 rows, and =
say you had 100  GROUP, TYPE and ACCESS rows. I'll also assume your =
names are max 20 characters.

Using the single join query you return:

1000 rows * (2 + 20 + 20 + 20 + 20) bytes =3D 82Kb from the DB in one =
query.

Using CDBI you'd return:

1000 rows * (2 + 20 + 2 + 2 + 2) bytes =3D 28Kb for the first query, =
then 100 rows * 20bytes =3D 2Kb for the others =3D 34Kb total in 301 =
queries.

OK this is way over-simplistic - and doesn't take into account the =
overhead of your DB protocol. You're the only person that can =
benchmark/profile your real-world situation though.


Finally although you are going to generally perform more queries using =
CDBI, you're performing simple SELECT queries based on the PK of a =
single table. These queries will be exceedingly fast to run - and won't =
have any near the impact of lots of users all running more complex =
queries joining multiple tables.


Now even if after that you find that some queries in your application =
would be better outside of CDBI, then you can add methods into your =
classes that perform the exact queries you want. That way you can still =
use CDBI where appropiate, and share single DB connections etc.


Carl