Find Unmatched Bogs Down

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I import from Excel that is up to 19000 rows. What I
have done is concatenated some of the fields in the table to make them
unique. I have also done it with the query that I'm comparing the table to
in the database. When I attempt to run the 19000 records, it bogs down. It
runs fine with only a thousand or so, but only dies when I run a ton. Are
there any ways around this to make it run smoother?

Thanks.
 
Do you have an index on the the relevant fields?

When you say you have concatenated the fields, do you mean in the query? If
so, you should try dropping the concatenation and joining on the individual
fields to find the unmatched records.

Generically, the query would look like this.
SELECT Table1.*
FROM Table1 LEFT JOIN TableXL
On Table1.FieldA = TableXL.Field2
AND Table1.FieldB = TableXL.Field3
AND Table1.FieldC = TableXL.Field4
WHERE TableXL.Field2 IS NULL

Can you post the SQL you are using?
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
SELECT ConcatQuery.Plant, ConcatQuery.[Pet Consign Code],
ConcatQuery.[Allocation Channel], ConcatQuery.NewFPG, ConcatQuery.PIDX,
ConcatQuery.[Pet Prdt Cd], ConcatQuery.[Delvry Mat1], ConcatQuery.[Price
group], ConcatQuery.Description

FROM ConcatQuery LEFT JOIN ConcatTableNewCustomer ON ConcatQuery.Concat =
ConcatTableNewCustomer.Primary
WHERE (((ConcatTableNewCustomer.Primary) Is Null));


I basically run two separate queries that concatenate fields from a
preexisting query and another table. This is necessary to find the unmatched
due to the need for a primary key. So this new query looks at both of the
concats to determine what records are not there. If I looked towards
multiple fields, it would give me the wrong results because most of the
fields are the same, its the combination of all the fields that make the
records unique.
 
You should be using a query that has the table and the pre-existing query as
sources. You then join on all the fields that you are concatenating
seperately. Your method should work; however, it cannot use any indexes
since none exist on the fields Concat and Primary (and as far as I know,
none can be built unless you dump the records to another table.)

If you are still stuck, I suggest you post the sql of ConcatQuery and
ConcatTableNewCustomer.


Brian said:
SELECT ConcatQuery.Plant, ConcatQuery.[Pet Consign Code],
ConcatQuery.[Allocation Channel], ConcatQuery.NewFPG, ConcatQuery.PIDX,
ConcatQuery.[Pet Prdt Cd], ConcatQuery.[Delvry Mat1], ConcatQuery.[Price
group], ConcatQuery.Description

FROM ConcatQuery LEFT JOIN ConcatTableNewCustomer ON ConcatQuery.Concat =
ConcatTableNewCustomer.Primary
WHERE (((ConcatTableNewCustomer.Primary) Is Null));


I basically run two separate queries that concatenate fields from a
preexisting query and another table. This is necessary to find the
unmatched
due to the need for a primary key. So this new query looks at both of the
concats to determine what records are not there. If I looked towards
multiple fields, it would give me the wrong results because most of the
fields are the same, its the combination of all the fields that make the
records unique.

John Spencer said:
Do you have an index on the the relevant fields?

When you say you have concatenated the fields, do you mean in the query?
If
so, you should try dropping the concatenation and joining on the
individual
fields to find the unmatched records.

Generically, the query would look like this.
SELECT Table1.*
FROM Table1 LEFT JOIN TableXL
On Table1.FieldA = TableXL.Field2
AND Table1.FieldB = TableXL.Field3
AND Table1.FieldC = TableXL.Field4
WHERE TableXL.Field2 IS NULL

Can you post the SQL you are using?
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
Click to expand...
Click to expand...
 
Back
Top