Developing Applications for Performance

$sth $dbh = new DB(…); $sth = $dbh->prepare(“select * from blogsposts where username = ?”); = $dbh->prepare(“select * from where author $sth->execute(‘leon’); $posts = array(); = ?”); foreach ($sth->fetchAll() as $p) { $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; array_push($posts, $post); } foreach ($posts as $post) { $dbh2 = new DB(…); $sth_comm = $dbh2->prepare(“select * from post_comments where post_id = ?”); $sth_comm->execute($post->post_id); foreach(sth_comm->fetchAll() as $c) { $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } } @papa_fire

$dbh = new DB(…); $sth = $dbh->prepare(“select * from posts where author = ?”); $sth->execute(‘leon’); $posts = array(); foreach ($sth->fetchAll() as $p) { $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; array_push($posts, $post); } foreach ($posts as $post) { $dbh2 = new DB(…); = $dbh2->prepare(“select ** from from post_comments where where post_id post_id = ?”); $sth_comm$sth_comm = $dbh2->prepare(“select post_comments $sth_comm->execute($post->post_id); foreach(sth_comm->fetchAll() as $c) { $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } } = ?”); @papa_fire

$dbh = new DB(…); $sth = $dbh->prepare(“select * from posts where author = ?”); $sth->execute(‘leon’); $posts = array(); foreach ($sth->fetchAll() as $p) { $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; array_push($posts, $post); } foreach ($posts $post) { foreach ($posts as as $post) { $dbh2 = new DB(…); = $dbh2->prepare(“select ** from from post_comments where where post_id post_id = ?”); $sth_comm$sth_comm = $dbh2->prepare(“select post_comments $sth_comm->execute($post->post_id); foreach(sth_comm->fetchAll() as $c) { $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } } = ?”); @papa_fire

$dbh == new $dbh newDB(…); DB(…); $sth = $dbh->prepare(“select * from posts where author = ?”); $sth->execute(‘leon’); $posts = array(); foreach ($sth->fetchAll() as $p) { $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; array_push($posts, $post); } foreach ($posts as $post) { $dbh2 = newDB(…); DB(…); $dbh2 = new $sth_comm = $dbh2->prepare(“select * from post_comments where post_id = ?”); $sth_comm->execute($post->post_id); foreach(sth_comm->fetchAll() as $c) { $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } } @papa_fire

$dbh = new DB(…); $sth = $dbh->prepare(“select [list columns you need] from posts p, post_comments c where p.id = c.post_id and p.username = ? order by p.id DESC”); $sth->execute(‘leon’); $posts = array(); $post = new Post(); $active_pid = 0; foreach ($sth->fetchAll() as $p) { if ($post->id != $active_pid) { // if this row is for the post we’re working on if ($post->id) { array_push($posts, $post); } $active_pid = $post->id; // getting comments for this post $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; } $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } @papa_fire

$dbh newnew DB(…); $dbh= = DB(…); $sth = $dbh->prepare(“select [list columns you need] from posts p, post_comments c $sth = $dbh->prepare(“select [list columns you need] where p.id = c.post_id and p.username = ? from posts p, post_comments c order by p.id DESC”); where p.id = c.post_id $sth->execute(‘leon’); and p.username = ? $posts = array(); $post = new Post(); order by p.id DESC”); $active_pid = 0; foreach ($sth->fetchAll() as $p) { if ($post->id != $active_pid) { // if this row is for the post we’re working on if ($post->id) { array_push($posts, $post); } $active_pid = $post->id; // getting comments for this post $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; } $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } @papa_fire

$dbh = new DB(…); $sth = $dbh->prepare(“select [list columns you need] from posts p, post_comments c where p.id = c.post_id and p.username = ? order by p.id DESC”); $sth->execute(‘leon’); $posts = array(); $post = new Post(); $active_pid = 0; $posts = $blog->get_all_posts_with_comments(); foreach ($sth->fetchAll() as $p) { if ($post->id != $active_pid) { // if this row is for the post we’re if ($post->id) { array_push($posts, $post); } $active_pid = $post->id; // getting comments for this post $post = new Post(); $post->id = $p->[‘id’]; $post->title = $p->[‘title’]; $post->author = $p->[‘author’]; $post->body = $p->[‘body’]; } $comment = new Comment(); $comment->id = $c->[‘id’]; $comment->author = $c->[‘author’]; $comment->body = $c->[‘body’]; array_push($post->comments, $comment); } @papa_fire

select * from ( select ( bannerid, caption, client_url, image_file, sponsorid, weight from select V.bannerid, V.impressions, B.caption, B.client_url, B.image_file, s.sponsorid, s.weight, row_number() over (partition by s.sponsorid order by s.weight desc) ranking FROM ( — This level gives me a list of banners sorted by least seen,and then by highest weight select valid.bannerid, valid.totalweight, count(I.timestamp) as impressions FROM ( — This level gets me a list of banners that are valid for display select b.bannerid, — Add up the weight from 4 sources. Banner weight, and weight for each data item they match decode( decode(bitand(u.STATE_BM1,b.STATE_BM1),0,0,1) + decode(bitand(u.STATE_BM2,b.STATE_BM2),0,0,1) + decode(bitand(u.STATE_BM3,b.STATE_BM3),0,0,1), 0,0,b.STATE_WT ) + decode(bitand(u.AGE_BM,b.AGE_BM),0,0,b.AGE_WT)+ decode(bitand(u.GENDER_BM,b.GENDER_BM),0,0,b.GENDER_WT)+ b.weight as totalweight from tgif.tbl_users u, tgif.tbl_banners b, tgif.tbl_bannerstats bs where — I only care about ME! u.userid= 1 — Don’t show inactive banners and b.inactive != 1 — Only show banners that are currently running and sysdate < b.end_date and sysdate >=b.start_date — Only get the type of banner i’m looking for and b.type= 3 — Join on the total stats, and only display banners that haven’t reached their per banner maximums and b.bannerid = bs.bannerid and ( b.max_impressions IS NULL OR bs.total_impressions < b.max_impressions ) and ( b.max_clicks IS NULL OR bs.total_clicks < b.max_clicks ) and ( b.max_conversions IS NULL OR bs.total_conversions < b.max_conversions ) — Ignore any banners that don’t match their demographics (ie, male banner won’t go to females) and ( b.AGE_BM IS NULL OR b.AGE_BM = 0 OR bitand(u.AGE_BM, b.AGE_BM) != 0 ) and ( b.GENDER_BM IS NULL OR b.GENDER_BM =0 OR bitand(u.GENDER_BM, b.GENDER_BM) != 0 ) and ( b.STATE_BM1 IS NULL OR b.STATE_BM1 =0 OR bitand(u.STATE_BM1, b.STATE_BM1) != 0 ) and ( b.STATE_BM2 IS NULL OR b.STATE_BM2 =0 OR bitand(u.STATE_BM2, b.STATE_BM2) != 0 ) and ( b.STATE_BM3 IS NULL OR b.STATE_BM3 =0 OR bitand(u.STATE_BM3, b.STATE_BM3) != 0 ) — But don’t show me any banners that I have already signed up and b.bannerid NOT IN ( SELECT B.bannerid FROM tgif.tbl_bannerconversions C, tgif.tbl_banners B, tgif.tbl_sponsors sp WHERE C.USERID=1 AND C.bannerid=B.bannerid AND B.sponsorid=sp.sponsorid — unless they have a conversion interval, and that interval has expired AND ( sp.conversion_interval = 0 OR sysdate > C.timestamp+sp.conversion_interval ) ) — Don’t show me any banners that have SPONSORS that have reached their maximums and b.sponsorid NOT IN ( — I believe this would be better done using HAVING clauses, but I can’t figure it out — Take the banners for a sponsor in the bannerstats table, and get the totals per sponsor — return anything that has reached it’s maximum select sponsorid FROM ( SELECT S.sponsorid, S.max_impressions, S.max_conversions, S.max_clicks, sum(total_impressions) as imps, sum(total_conversions) as convs, sum(total_clicks) as clicks FROM tgif.tbl_sponsors S, tgif.tbl_banners B, tgif.tbl_bannerstats bs WHERE S.sponsorid=B.sponsorid AND B.bannerid=bs.bannerid GROUP BY S.Sponsorid, S.max_impressions, S.max_conversions, S.max_clicks ) exclude WHERE ( imps > max_impressions OR convs >= max_conversions OR clicks > max_clicks ) ) ) valid, tgif.tbl_bannerimpressions I where valid.bannerid=I.bannerid(+) and I.userid(+)=1 group by valid.bannerid, valid.totalweight — I want to see banners I haven’t seen yet, sorted by highest weight, so we sort by number — of times that this user has seen this particular banner, then we sort by weight order by impressions, totalweight DESC ) V, tgif.tbl_banners B, tgif.tbl_sponsors S where B.bannerid=V.bannerid and B.sponsorid=S.sponsorid and S.inactive != 1 and s.sponsorid not in ( ) valid, tgif.tbl_bannerimpressions I where valid.bannerid=I.bannerid(+) and I.userid(+)=1 group by valid.bannerid, valid.totalweight — I want to see banners I haven’t seen yet, sorted by highest weight, so we sort by number — of times that this user has seen this particular banner, then we sort by weight order by impressions, totalweight DESC ) V, tgif.tbl_banners B, tgif.tbl_sponsors S where B.bannerid=V.bannerid and B.sponsorid=S.sponsorid and S.inactive != 1 and s.sponsorid not in ( — Check the user impression cap to make sure it hasn’t been passed by the user select s.sponsorid from tgif.tbl_banners b, tgif.tbl_sponsors s, tgif.TBL_BANNERIMPRESSIONS i where s.sponsorid = b.sponsorid and b.bannerid = i.bannerid and i.timestamp >= sysdate - nvl(user_impression_cap_days,100) and userid = 1 group by s.sponsorid having count(*) >= max(nvl(user_impression_cap,1000000000)) ) — Make sure the sponsor is still in the valid table. This table is updated hourly — and contains the sponsors that have not gone over their sponsor level frequencies for — impressions/conversions/clicks and s.sponsorid in (select sponsorid from tgif.tbl_active_sponsors) ) where ranking=1 —Order the banners by sponsor weight, which is handled by the ranking —order by S.weight order by impressions, weight desc would you trust a junior with this query? ) where rownum <= 10; @papa_fire