Re: SQL code question

Albert W. Dorrington (awdorrin_at_ictest.delcoelect.com) wrote: [snip]
: SELECT
: '$thing' PART_TYPE,
: (count(distinct(substr(TABLEB.rowid,1,8)||substr(TABLEB.rowid,15,4))))*512
: Bytes,
: count(*) Row_Count
: FROM
: TABLEA, TABLEB
: WHERE
: test_area = '$area'
: AND
: TABLEA.master_id = TABLEB.master_id
: AND
: TABLEB.master_id BETWEEN $time1 AND $time2
: AND
: part_type = '$thing';
 

: ====

Well, if TABLEA is smaller, I'd try changing that BETWEEN clause to use TABLEA instead of TABLEB. Should rule out the same # of records. Also, if it's not a typo that first line of the select is garbage. You should just have to select part_type, unless '$thing' is an actual column name (it doesn't appear to be).

A subquery for valid master_id's might be faster than a join in this case too (i.e. select ... from tablea where master_id in (select ... from tableb ...)) It's hard for me to tell how much of a hit those substrings of ROWID are costing you, though.

Joe

--
                                   
=------------------------------------------------------------------------=
Joe Nardone               |    
joe_at_access.digex.net      |