Re: Yes, this is probably a stupid question but...
Date: Sat, 12 Apr 2008 17:01:00 -0700 (PDT)
On Apr 12, 4:44 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> Pat <pat.ca..._at_service-now.com> wrote in news:0653963d-1d32-4e85-9bae-
> > Question I have is:
> > Short of constructing a summary table (which is probably a non starter
> > in this environment), is there anything I could to to speed up the
> > return time of a count query like the above?
> Ready, Fire, Aim!
> If you don't know specifically where the time is being spent,
> you'd only be guessing what change is most optimal.
> Run your query with SQL_TRACE=TRUE & run the results thru tkprof.
> Post the results & EXPLAIN PLAN back here is you still need more asisstance
I'm working over a remote desktop at the moment so I can't cut and paste the plan so I'll transcribe:
select count(*) as recordcount from (a inner join b on a.sys_id = b.sys_id) where a.active = 1
Table has aprox 180k rows, of which 151k have active = true
_SORT AGGREGATE __NESTED LOOPS ___INDEX RANGE SCAN (index on a.active, a.sys_id) ___INDEX UNIQUE SCAN (index on b.sys_id)
Total Query time: 2.39 seconds
I've got a covering index on the primary table and an index on the child table's join condition, so there's no actual table reads going on here e.g. it's just an index join. I have current stats on the table (as of last night). From what I can see this is not an absurdly bad optimizer plan, but then I suspect there's something I'm not seeing, hence my post here. Received on Sat Apr 12 2008 - 19:01:00 CDT