Hi Tom,

I cannot tell you how many times I have had the normalize/denormalize debate 
over the past 20+ years.

So without this meaning to start yet another round of discussion (where I 
will never agree), denormalize the data, your Users will thank you.

FWIW
Leslie.

"Tom Krieg" <"tom krieg AT outlook.com"> wrote in message 
news:57665386$1@pnews.thedbcommunity.com...
>I have a query
>
> SELECT adb_street.street_uid FROM adb_street
> WHERE upper(adb_street.street_name) like 'THEOD%'
>
> Table adb_street has about 1 million records, index on street name. 
> Street_uid is the PK. This query takes about 200ms and returns 45 rows.
>
> I have another query
>
> SELECT * FROM adb_address_detail
> WHERE adb_address_detail.street_uid IN
>   (SELECT adb_street.street_uid FROM adb_street
>    WHERE upper(adb_street.street_name) like 'THEOD%')
>
> Table adb_address_detail has 14 million records and has an index on 
> street_uid. This query takes a long time (10 - 30 seconds, depending on 
> which way a butterfly in Amazonia flaps its wings).
>
> Is there any way I can make this query faster?
>
> The database has been normalised to within an inch of its life by an Aus 
> government department. Theoretically according to all the rules (Denn 
> Santoro would have approved) but in real life not so practical. The second 
> query is the pseudocode for the following ...
>
> SELECT <LIST OF FIELDS ... >
> FROM adb_address_detail a
>   JOIN adb_street s ON a.street_uid = s.street_uid
>   JOIN adb_locality lo ON a.locality_uid = lo.locality_uid
>   JOIN adb_address_geocode ag ON a.address_detail_uid = 
> ag.address_detail_uid
>   JOIN adb_state t ON lo.state_uid = t.state_uid
>   LEFT JOIN __flat_type ft ON a.flat_type_code = ft.code
>   LEFT JOIN __level_type lt ON a.level_type_code = lt.code
>   LEFT JOIN __street_suffix ss ON s.street_suffix_code = ss.code
>   LEFT JOIN __street_class sc ON s.street_class_code = sc.code
>   LEFT JOIN __street_type st ON s.street_type_code = st.code
>   LEFT JOIN __geocode_type gt ON ag.geocode_type_code = gt.code
> WHERE a.street_uid IN
>   (SELECT adb_street.street_uid FROM adb_street
>    WHERE upper(adb_street.street_name) like 'HEREF%');
>
> (Those tables starting with __ are reference tables, i.e. providing a full 
> description for a code, for e.g. St = Street or URHSE = Urban House, RUHSE 
> = Rural House. These tables consist of 2 fields.)
>
> If it takes this long to do a query, what's going to happen when I have to 
> do geometry calculations on the geocodes, like select all the addresses 
> within a certain distance of a selected address? Would it be better to 
> create some denormalised tables for queries? (The database is updated 
> every 6 months in bulk so I wouldn't need to worry about user changes).
>
> TIA
> -- 
> To send me an email, remove the spaces.