On Sunday 21 September 2008 23:23:00 Gabe wrote:
> Hi,
> Thanks for the pointer. I must have read over that 100 times and
> missed the contains part. I've managed to put this together:
> games =
> (session.query(models.Game).options(
> sqlalchemy.orm.eagerload('scores')).filter(
> models.Game.scores.any(models.Score.player ==
> player)).all())
>
> Unfortunately, with ~6000 games, where the specified user has
> played ~4000 of them, it takes roughly 45 seconds to issue that
> query: time curl "http://localhost:8080/chart/win_average?
> width=400&height=400&username=solongordon&playername=Solon&version=
>2" Solon played 4195 games.
> real 0m44.656s
> user 0m0.008s
> sys 0m0.008s
>
> As you can see I've attempted to eagerload the scores but that
> doesn't seem to help much. I tried to eagerload the players as
> well but I can't get that syntax to work. I tried specifying lazy
> = False in my Columns in the models, but I don't know if that takes
> effect after the database has already been created.
>
> Does anyone have advice for speeding this query up?
- try another db, e.g. postgres. sqlite is simple thing, do not
expect miracles.
- maybe, try to express your thing as a join (and not subselect) (i'm
not sql specialist so might be wrong here)
- sometimes the amount of data to be transferred via network becomes
the bottleneck. e.g. in one case of mine eagerloading 5 relations was
2x slower than otherwise.
hth
svilen
> On Sep 21, 4:38 pm, [EMAIL PROTECTED] wrote:
> > see
> > thesehttp://www.sqlalchemy.org/docs/05/ormtutorial.html#datamappi
> >ng_joins_...
> >
> > On Sunday 21 September 2008 20:04:02 Gabe wrote:
> > > Hi Everyone,
> > > I'm somewhat new to database design, so if I've set things up
> > > in an incompatible or silly manner please let me know. I have
> > > the following tables:
> > >
> > > class Player(Base):
> > > __tablename__ = "players"
> > > # Stuff about a player.
> > >
> > > class
> > > Game(Base):
> > > """A class that holds information about a single
> > > game."""
> > >
> > > __tablename__ =
> > > "games"
> > >
> > > #
> > > Columns
> > > id = Column(Integer, primary_key =
> > > True)
> > > board =
> > > Column(Binary)
> > > match_length =
> > > Column(Float)
> > > date_added = Column(DateTime, default =
> > > datetime.datetime.now)
> > >
> > > class
> > > Score(Base):
> > > """A class that holds a player's score for a
> > > game."""
> > >
> > > __tablename__ =
> > > "scores"
> > >
> > > #
> > > Columns:
> > > id = Column(Integer, primary_key =
> > > True)
> > > game_id = Column(Integer,
> > > ForeignKey("games.id"))
> > > player_id = Column(Integer,
> > > ForeignKey("players.id"))
> > > score =
> > > Column(Integer)
> > >
> > > #
> > > Relations:
> > > game = relation(Game, backref =
> > > backref("scores"))
> > > player =
> > > relation(Player)
> > >
> > > # Back
> > > References:
> > > # "game" which points to the game for this object.
> > >
> > > I'd like to figure out how to count the wins and losses for a
> > > player. I was thinking something along these lines:
> > > player = session.query(Player).filter(Player.name ==
> > > playername).one() games_containing_player =
> > > session.query(Game).filter(player in Game.scores.player).all()
> > >
> > > But that pretty obviously doesn't work. Should I have designed
> > > this set up differently? Right now the only thing I've come up
> > > with, (which seems to think the player is in every game as well
> > > which is wrong) and is really really slow, is:
> > >
> > > Get the player.
> > > player =
> > > session.query(models.Player).filter(sqlalchemy.and_(
> > > models.Player.owner == user,
> > > models.Player.name == playername,
> > > models.Player.version == version)).one()
> > > if not player:
> > > return "Not a valid player."
> > >
> > > # Compute the player's win/loss list.
> > > win_loss = []
> > > games = session.query(models.Game).all()
> > >
> > > def had_player(game):
> > > for score in game.scores:
> > > if score.player == player:
> > > return True
> > > return False
> > >
> > > filter(had_player, games)
> > > return "%s played %d games." % (player.name, len(games))
> > >
> > > Thanks for the advice,
> > > Gabe
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
