Malcom... thanks for all the info :)
I didn't paste all of my model code into the sample... each of those
tables actually has Foreign Keys to a few other things..which also
contains foreign keys...
The page that was slowing down was a page where I basically had a drop
down showing Companies ( company_id,company_name ) ... then a drop
down of Offices (office_id, office_name) ... to allow the user to
select a company and then a related office.. to run a report on.
Since we have about 2000 companies, and then multiple offices for
each... and I was trying to load them all to display ( I have
javascript to switch the office combo contents ) ... it was making the
page load take about 60 seconds.... because it was trying to load
everything for each... when I changed it to the code I pasted... it
cut it down to 5 seconds :)
Granted...I know our db guy still has to do some optimization...I
think he is missing some indexes... but anyways.... I'm using
QuerySets in most spots in my site... I've just modified it to
use .values for the couple of pages where I'm filling combo boxes like
this...and I noticed the page loads were extremely slow..
On Mar 10, 12:07 am, Malcolm Tredinnick <[EMAIL PROTECTED]>
wrote:
> On Sat, 2007-03-10 at 03:03 +0000, [EMAIL PROTECTED] wrote:
> > Is there a way to load a related object by still using .values()?
>
> [... example snipped ...]
>
> > And for company I only want to
> > load .values('company_name','company_id') , but for each company in
> > the result set...I want to be able to call company.office_set.all in
> > the template and get the office_id and office_name.
>
> > Is it possible to do this with .values()?
>
> I think you are trying to push .values() too far and need a solution as
> in your later post.
>
> I did want to point out something here, though: unless you are doing all
> these conversions to values based on lots of timings and particular
> features of your real-life data set, I suspect you are focusing a lot of
> optimisation attention in a place that won't give huge returns.
>
> The reasoning is this: firstly, and most simply, at the Python level,
> there is some difference between creating a dictionary with three or
> four keys and an object with eight attributes (empty model object
> creation is more expensive than empty dictionary creation), but it's not
> a huge difference and unless you're doing this thousands of times each
> time through your function, I suspect you won't notice it very much.
>
> Secondly, at the database level, a generally accurate rule of thumb is
> that most of the time spent executing an SQL query will be used on table
> joins, filtering (WHERE and HAVING clauses), grouping and ordering. The
> actual number of columns you select from the result set does not have a
> huge effect on the time taken. You can tilt the numbers to the bad side
> by using some very complex derived columns (complex calculation in the
> columns, for example) and you can sometimes get lucky and save time if
> all of your columns are in a single index that is already being used in
> the query execution, so the database only has to read the index table to
> get the data tuple. But both these cases are relatively uncommon over
> the universe of all possible real-life queries.
>
> Once the database server has worked out the potential results set, it
> will still read the full row from disk, because the data you are
> selecting will typically be spread out amongst the columns in each row
> (not contiguous) and because disks read a fixed block size (a few K)
> each time, it doesn't just read the columns you want. So you aren't
> saving on disk reads or anything by only selecting a few columns. Of
> course, nothing beats timing and testing, but try it out: run the same
> queries a few tens of thousand times with differing columns in the
> SELECT clause each time and you'll see not a lot of variation.
>
> My point here is just to say that switching from retrieving full
> QuerySets -- which retrieves all the columns and creates objects with
> the columns as attributes -- and a .values() list -- which only
> retrieves some columns and then puts the results into a dictionary -- is
> going to save you some time, but not an enormously large amount, in
> comparison to the time spent on database query execution and moving the
> data over a network (if your database is remote). So if you are wanting
> to do anything complex with queries, don't try to jam it into .values(),
> just go with the QuerySet concept.
>
> All that being said, it's entirely invalidated in specific cases where
> timing tests show that you are getting lots of improvements. "Best in a
> general sense" often loses to adjustments for specific cases. I am only
> guessing at why you are doing this work, so please don't be offended if
> you've already done the timing and are seeing real improvements in your
> current approach.
>
> Regards,
> Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
