IN queries, as it turns out are slow in MS SQL Server.  I learned that 
somewhere in this video, which I thought was very good, if long:

https://www.youtube.com/watch?v=XUCxQkFoqpw

Instead, try this (I'm going to drop the UPPER - you don't need it 
unless your collation is case sensitive, which is NOT the default):

SELECT *
FROM adb_address_detail d
WHERE EXISTS (SELECT adb_street.street_uid
               FROM adb_street s
               WHERE s.adb_street.street_uid = 
d.adb_address_detail.street_uid
               AND s.adb_street.street_name like 'THEOD%')

That may or may not speed things up.  There's also a join, which is 
still better than an IN, I think:

SELECT d.*
FROM adb_street s
      INNER JOIN adb_address_detail d
            ON d.adb_address_detail.street_uid = s.adb_street.street_uid
WHERE s.adb_street.street_name like 'THEOD%'

...and that video may have other ideas for you.

You could try an index with street_uid first and street_name second, to 
see if that will speed matters - I'm just not sure how MS SQL Server 
uses indexes, so I don't know if they key and a separate index on 
street_name will work, or if both need to be in one index, or what. 
This is why I keep telling the bosses we need a DBA.

Liz


On 19 Jun 2016 02:10, Tom Krieg wrote:
> 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