I think found the problem.
My problem is with powerTable plugin.
If i return rows only the show results is fast, but i try to use powertable the
result is slow:
powerTable = plugins.powerTable
powerTable.datasource = reg
powerTable.dtfeatures['bPaginate'] = True
powerTable.dtfeatures['bAutoWidth'] = True
powerTable.dtfeatures['bSort'] = False #Se muestra ordenado por Query
powerTable.dtfeatures['iDisplayLength'] = 50
powerTable.virtualfields = Virtual()
powerTable.headers='labels'
powerTable.showkeycolumn = False
powerTable.dtfeatures['bJQueryUI'] = request.vars.get('jqueryui',True)
powerTable.keycolumn = 'rutas.nombre'
powerTable.columns = ['rutas.nombre','ratecltes.rate','virtual.edit']
powerTable.hiddecolumns=['rutas.nombre']
table=powerTable.create()
return dict(table=table)
Any idea?
Regards
--- On Tue, 1/22/13, FERNANDO VILLARROEL <fvillarr...@yahoo.com> wrote:
> From: FERNANDO VILLARROEL <fvillarr...@yahoo.com>
> Subject: [web2py] Help Query speed
> To: web2py@googlegroups.com
> Date: Tuesday, January 22, 2013, 6:54 PM
> Dear.
>
> I have the follow problem.
>
> If i run the follows query in PgAdminIII is very fast:
>
> select rutas.id,rutas.nombre,ratecltes.rate from
> ratecltes
> join rutas on rutas.id = ratecltes.id_rutas
> where ratecltes.id_clte=26
> order by rutas.nombre
>
> But when i run the query on Web2py is very slow
>
>
> query=(db.ratecltes.id_clte==session.cliente_id)
> reg=db(query).select(db.rutas.id,db.rutas.nombre,db.ratecltes.rate,
>
> left=db.rutas.on(db.rutas.id==db.ratecltes.id_rutas),
>
> orderby=db.rutas.nombre)
>
> The query show 2287 tuples.
>
> I have PostgreSQL 8.4
>
> The explain analyse
>
>
>
> QUERY PLAN
>
>
>
>
> -------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=1264.00..1286.87 rows=2287 width=30)
> (actual time=135.604..145.228 rows=2251 loops=1)
> -> Sort
> (cost=1264.00..1269.71 rows=2287 width=30) (actual
> time=135.592..138.590 rows=2251 loops=1)
> Sort Key:
> rutas.nombre, rutas.id, ratecltes.rate
> Sort Method:
> quicksort Memory: 221kB
> -> Hash
> Join (cost=69.65..1136.39 rows=2287 width=30) (actual
> time=17.528..34.604 rows=2251 loops=1)
>
> Hash Cond: (ratecltes.id_rutas =
> rutas.id)
>
> -> Seq Scan on ratecltes
> (cost=0.00..1032.44 rows=2287 width=8) (actual
> time=9.888..19.838 rows=2251 loops=1)
>
> Filter: (id_clte = 26)
>
> -> Hash (cost=41.51..41.51
> rows=2251 width=26) (actual time=7.580..7.580 rows=2251
> loops=1)
>
> -> Seq Scan on rutas
> (cost=0.00..41.51 rows=2251 width=26) (actual
> time=0.013..3.853 rows=2251 loops=1)
> Total runtime: 148.293 ms
>
> I hope you could help me.
>
> Fernando
>
>
> --
>
>
>
>
--
