SQL code question

From: Albert W. Dorrington <awdorrin_at_ictest.delcoelect.com>
Date: 1995/08/11
Message-ID: <40fug8$s5l_at_kocrsv08.delcoelect.com>#1/1


Hi,

        I'm fairly new to coding SQL, but do understand the basics. However, I've recently written a segment of SQL which seems to take a lot longer to execute than I would think it should. I'm hoping that someone on here might be able to point out a more efficient way of writing this query.

The query:


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';


TABLEA is mostly a 'table of contents' for the data, while TABLEB contains data of a more specific nature.

The 'master_id' column (which is common to both tables) is a Unix 'seconds' timestamp (you know, the number of seconds since Jan 1, 1970) and it does have an index applied.

The columns 'test_area' and 'part_type' are both in TABLEA.

Of course, $thing, $area, $time1 and $time2 are variables, that would be substitued.

Basically the query is trying to determine (estimate) how many bytes of data a given part type, from a certain test area, is taking up in TABLEB over a certain time interval.

Any advice or suggestions would be greatly appreciated.

Thanks,

-- 
Al Dorrington                                      
awdorrin_at_ictest.delcoelect.com                     Database Admin
Delco Electronics - IC CIM                         Unix  Sysadmin
Kokomo, Indiana, USA                               Phone: 317.451.9655
Received on Fri Aug 11 1995 - 00:00:00 CEST

Original text of this message