The DAL is a pure python implementation so it is slower than a C implementation.
With large datasets the overhead is high so it is better to use executesql.
Often there's no other way than pulling all the records at once, but
sometimes a lazy approach to data extraction with the use of limitby
can give the best results: speed and DAL ease of use.
mic
2012/6/22 Alec Taylor <alec.tayl...@gmail.com>:
> From what I understand people use DAL or ORM layers for abstraction purposes.
>
> So a struct with pointer to functions in C will have less overhead
> than a class with member functions in C++. However working at this
> higher level of abstraction allows for some fancy time-saving
> mechanisms such as multiple inheritance, templates, encapsulation and
> subtype polymorphism.
>
> In the database world: queries would of course run much faster when
> written by hand, and stored-procedures will speed up the equivalent
> function built inn Python with the layer in-between. Does this mean we
> should just write pure SQL and stored-procedures?
>
> Yes and no. It depends on what you are setting out to do. If
> performance is your goal and your willing to sacrifice abstraction to
> achieve it, then do as much as you can database and query side.
>
> {An additional advantage of using DAL or ORM layers is the ability to
> move to a different database engine (e.g.: a NoSQL one or from sqlite
> to postgres [use one for dev one for production])/}
>
> <apologies if I went to much into philosophy here, if you have actual
> ideas on how to speed up queries without sacrificing abstraction I'm
> sure your patches will be accepted :]>
>
> On Fri, Jun 22, 2012 at 4:54 PM, pbreit <pbreitenb...@gmail.com> wrote:
>> I have an app where I pull down 5,000 records. I started with DAL and
>> db.item.ALL and it was taking around 4 seconds. I added 5 fields to the
>> select() which got it down to around 2 seconds. Then I implemented the same
>> thing with executesql and got it down to 500 ms.
>>
>> So, selecting only the fields you need is a good optimization. But going
>> with raw SQL is much better.
>>
>> --
>
> --
>
>
>
--
