Subject: | Re: Not quie sure how to do this
| Date: | Sun, 19 Jun 2016 07:21:01 -0600
| From: | Liz McGuire <liz@paradoxcommunity.com>
| Newsgroups: | pnews.paradox-client_server
|
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
|