Duplicate Records 2

[Ernestw]

I have a table with legitimate and illegitimate records. I want to run a query based on one field, serial_number, that will show the serial number and a count of the records of interest.

The following SQL works, but it takes several minutes.

SELECT DISTINCT sn, count(*)
FROM ":server:assembly.RECORD"
WHERE OTHER_DESC LIKE '%SUBASY%' OR ASY_CONTROL_NO IN ('ab1','bc1')
GROUP BY sn
having count(*) > 1

Can a Paradox query handle the requirement?

TIA,

Ernie

 

[Snah]

Ernie,

try this:

Query
ANSWER: RIV:ANSWER.DB

Table.db | SN |
| Check calc count ALL, count all>1 |

EndQuery

regards
hans

 

[footpad]

Ernie,

What happens if you break this into two separate queries? Wildcard queries are notoriously slow, regardless of the server because they essentially perform a table scan. In many cases, separating an individual query into separate ones can reduce the amount of work.

For example, limiting the number of records that need to be searched using the wildcard can frequently improve performance.

Does SUBASY actually appear in multiple places in the field? If so, it may be worth while to take a second look at the way that other description is structured. Perhaps a HasSubassembly flag field or a detail containing descriptive records describing the contents more consistently would be better, for it would let you perform direct matches, rather than wildcard matches.

Also, do you have a secondary indexes on sn, OTHER_DESC, and ASY_CONTROL? That could also speed things dramatically.

Is this run against a Paradox table or one stored on a different server? That mnay be a factor, as well.
Hope this helps...

-- Lance

 

[Ernestw]

Hans and Lance,

I can now identify the potential records of interest in about 10 seconds; a vast improvement over the SQL.

Yes, I need to do it in two steps. The initial query from Hans will build my list of potential serial numbers, and I can add the other requirements in a second query.

Thank you both. This is a great resource.

Ernie