query speed

G

Guest

I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.

Thanks in advance,
geebee
 
G

Guest

What indexes are in Tbl_MasterPop, Tbl_Archive, and 1_Stg_WBStatus_Flag? Need
to see both the index names and fields indexed. What are the primary keys for
those tables?

If you want to keep out duplicates in Tbl_Archive, just make the combination
of the loan acct #, date, and status a unique index, maybe even the PK, and
Access will prevent it but let the non-duplicate records get through. I'm
assuming that you are talking pure Access and not linked to any other types
of databases.

Now if you really want to speed things up, don't do it at all! Putting
'archived' records in another table is almost always a mistake. You'll need
to create a slow Union query if you ever need to see both the current and
archived records at the same time. If your problem is slow performance,
possibly some indexing my help.

Also it looks like your current structure might not be properly normalized
as there seems to be a lot of derived data there like Balances and Next
Payment Dates.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.

Thanks in advance,
geebee
Click to expand...
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If there aren't any rows in tbl_archive, just load the data w/o the LEFT
JOIN to tbl_archive. This is for the first load, for future loads use
the LEFT JOIN to tbl_archive. I've found that when there aren't any
data in the target table & I'm using a criteria that trys to avoid
duplicates (like your query), the 1st query always runs slowly (or not
at all). Also, be sure to create indexes on tbl_archive and the source
tables for the JOIN columns and the WHERE columns.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRJGlzoechKqOuFEgEQLddwCfS81aCcVQMlHUA4IQVV78beMOR5AAoIBA
wR2AY3Y3x9XbtorxPHvqn+QH
=nP1d
-----END PGP SIGNATURE-----

I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.
Click to expand...
 
G

Guest

I have made sure that the loan acct # is indexed in tbl_masterpop and
tbl_archive. And that the popenterdt is indexed in tbl_masterpop and that
sate is indexed in tbl_archive, and that status is indexed in tbl_archive.
Why the union query instead of the append query? We want to make sure that
we capture the status of the loan acct # as it changes over time. Do you
have any better suggestion as to tracking the status change? How would this
query look?

seeming incompetent and lazy,
geebee


Jerry Whittle said:
What indexes are in Tbl_MasterPop, Tbl_Archive, and 1_Stg_WBStatus_Flag? Need
to see both the index names and fields indexed. What are the primary keys for
those tables?

If you want to keep out duplicates in Tbl_Archive, just make the combination
of the loan acct #, date, and status a unique index, maybe even the PK, and
Access will prevent it but let the non-duplicate records get through. I'm
assuming that you are talking pure Access and not linked to any other types
of databases.

Now if you really want to speed things up, don't do it at all! Putting
'archived' records in another table is almost always a mistake. You'll need
to create a slow Union query if you ever need to see both the current and
archived records at the same time. If your problem is slow performance,
possibly some indexing my help.

Also it looks like your current structure might not be properly normalized
as there seems to be a lot of derived data there like Balances and Next
Payment Dates.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.

Thanks in advance,
geebee
Click to expand...
Click to expand...
 
G

Guest

Is 1_Stg_WBStatus_Flag].histflag indexed?
[1_Stg_WBStatus_Flag].[Loan Acct #] indexed? Both are in the Where Clause
either as part of the join of criteria.

Any indexes in Tbl_Archive will slow down inserts. If you don't need them,
get rid of them.

The idea of the Union query is for afterwards when you need to compare
records in tbl_Archive and Tbl_MasterPop as you'll be moving records from one
to the other.

As far as tracking a status change, it could be a simple as a Yes/No or Text
field in the same table or a linked table just for status changes.



--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
I have made sure that the loan acct # is indexed in tbl_masterpop and
tbl_archive. And that the popenterdt is indexed in tbl_masterpop and that
sate is indexed in tbl_archive, and that status is indexed in tbl_archive.
Why the union query instead of the append query? We want to make sure that
we capture the status of the loan acct # as it changes over time. Do you
have any better suggestion as to tracking the status change? How would this
query look?

seeming incompetent and lazy,
geebee


Jerry Whittle said:
What indexes are in Tbl_MasterPop, Tbl_Archive, and 1_Stg_WBStatus_Flag? Need
to see both the index names and fields indexed. What are the primary keys for
those tables?

If you want to keep out duplicates in Tbl_Archive, just make the combination
of the loan acct #, date, and status a unique index, maybe even the PK, and
Access will prevent it but let the non-duplicate records get through. I'm
assuming that you are talking pure Access and not linked to any other types
of databases.

Now if you really want to speed things up, don't do it at all! Putting
'archived' records in another table is almost always a mistake. You'll need
to create a slow Union query if you ever need to see both the current and
archived records at the same time. If your problem is slow performance,
possibly some indexing my help.

Also it looks like your current structure might not be properly normalized
as there seems to be a lot of derived data there like Balances and Next
Payment Dates.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

1_Stg_WBStatus_Flag is actually a query, in which there is a histflag, so
there is no need for a union query (I dont think) because the flag is
determined within a query that the query below references to determine
whether or not to append records to tbl_archive.

i made sure to take off the indexes in tbl_archive. I will see how it runs.
last time i tried to run it (before taking out the indexes in tbl_archive),
the size of the backend db actually increased to over 2GB, and it didnt even
have any records in it to begin with before the query!



Jerry Whittle said:
Is 1_Stg_WBStatus_Flag].histflag indexed?
[1_Stg_WBStatus_Flag].[Loan Acct #] indexed? Both are in the Where Clause
either as part of the join of criteria.

Any indexes in Tbl_Archive will slow down inserts. If you don't need them,
get rid of them.

The idea of the Union query is for afterwards when you need to compare
records in tbl_Archive and Tbl_MasterPop as you'll be moving records from one
to the other.

As far as tracking a status change, it could be a simple as a Yes/No or Text
field in the same table or a linked table just for status changes.



--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


geebee said:
I have made sure that the loan acct # is indexed in tbl_masterpop and
tbl_archive. And that the popenterdt is indexed in tbl_masterpop and that
sate is indexed in tbl_archive, and that status is indexed in tbl_archive.
Why the union query instead of the append query? We want to make sure that
we capture the status of the loan acct # as it changes over time. Do you
have any better suggestion as to tracking the status change? How would this
query look?

seeming incompetent and lazy,
geebee


Jerry Whittle said:
What indexes are in Tbl_MasterPop, Tbl_Archive, and 1_Stg_WBStatus_Flag? Need
to see both the index names and fields indexed. What are the primary keys for
those tables?

If you want to keep out duplicates in Tbl_Archive, just make the combination
of the loan acct #, date, and status a unique index, maybe even the PK, and
Access will prevent it but let the non-duplicate records get through. I'm
assuming that you are talking pure Access and not linked to any other types
of databases.

Now if you really want to speed things up, don't do it at all! Putting
'archived' records in another table is almost always a mistake. You'll need
to create a slow Union query if you ever need to see both the current and
archived records at the same time. If your problem is slow performance,
possibly some indexing my help.

Also it looks like your current structure might not be properly normalized
as there seems to be a lot of derived data there like Balances and Next
Payment Dates.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following wuery:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE ((([1_Stg_WBStatus_Flag].histflag)=1));

The only problem is that the query is working VERY slow. It takes 12 hours
to complete, and even then it is not complete. I just have to shut it down.
When I add "top 1000" to the SELECT part of the query, it works faster.

Ideally, I want this query to prevent records from being appended to
tbl_archive if the record is already there for a loan acct # for that date
and with that status is already there, but it is even slower, and I am not
even sure if the syntax is even right to achieve this, as I got multiple
same-records appended:

INSERT INTO Tbl_Archive ( [Loan Acct #], [Date], PopEnterDt, PrevStatus,
Status, [Short Name], [Loan Status], [Contract Date], [Mat date], [Next Pmt
Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made], [# Reg Pmts
Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last Pmt Amt],
[Amended?], [Amended This Mo?], [DEL Group], [Days Delinquent], [Tot Past Due
Pmts $], [Principal Balance], [Val Reserve Dollar Amt], NETBAL, [Ext?],
[Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth of Orig], [Active BK
Flag Date], [Active BK Flag], [Inventory Status], [Charge Off Principal],
[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date], [Skip
Completion Date], [Skip Completion Sts], [Redeemed Date], [Entered Date],
[Repo Ordered Date], [Active Inactive Flag] ) IN
'C:\DelqPopRes\Temp\tbl_archive.mdb'
SELECT Tbl_MasterPop.[Loan Acct #], CDate([Datex]) AS Expr1,
Tbl_MasterPop.PopEnterDt, Tbl_MasterPop.PrevStatus, Tbl_MasterPop.Status,
Tbl_MasterPop.[Short Name], Tbl_MasterPop.[Loan Status],
Tbl_MasterPop.[Contract Date], Tbl_MasterPop.[Mat date], Tbl_MasterPop.[Next
Pmt Due Date], Tbl_MasterPop.[Payoff Date], Tbl_MasterPop.[Paid Off?],
Tbl_MasterPop.[Nbr of Reg Pmts Made], Tbl_MasterPop.[# Reg Pmts Remaining],
Tbl_MasterPop.[Pmt Scd Monthly Pmt Amt], Tbl_MasterPop.[Last Pmt Date],
Tbl_MasterPop.[Last Pmt Amt], Tbl_MasterPop.[Amended?],
Tbl_MasterPop.[Amended This Mo?], Tbl_MasterPop.[DEL Group],
Tbl_MasterPop.[Days Delinquent], Tbl_MasterPop.[Tot Past Due Pmts $],
Tbl_MasterPop.[Principal Balance], Tbl_MasterPop.[Val Reserve Dollar Amt],
Tbl_MasterPop.NETBAL, Tbl_MasterPop.[Ext?], Tbl_MasterPop.[Extended This
Mo?], Tbl_MasterPop.[Payoff Dollar Amt], Tbl_MasterPop.[Flat Void Mth of
Orig], Tbl_MasterPop.[Active BK Flag Date], Tbl_MasterPop.[Active BK Flag],
Tbl_MasterPop.[Inventory Status], Tbl_MasterPop.[Charge Off Principal],
Tbl_MasterPop.[Chg Off Type], Tbl_MasterPop.[CHG OFF Date],
Tbl_MasterPop.[Cure Letter Date], Tbl_MasterPop.[Skip Active Date],
Tbl_MasterPop.[Skip Completion Date], Tbl_MasterPop.[Skip Completion Sts],
Tbl_MasterPop.[Redeemed Date], Tbl_MasterPop.[Entered Date],
Tbl_MasterPop.[Repo Ordered Date], Tbl_MasterPop.[Active Inactive Flag]
FROM (1_Stg_WBStatus_Flag INNER JOIN Tbl_MasterPop ON
[1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_MasterPop.[Loan Acct #]) LEFT JOIN
Tbl_Archive ON [1_Stg_WBStatus_Flag].[Loan Acct #] = Tbl_Archive.[Loan Acct #]
WHERE (((Tbl_Archive.Date) Is Null) AND (([1_Stg_WBStatus_Flag].histflag)=1)
AND ((Tbl_Archive.[Loan Acct #]) Is Null));

There are only 280,000 records in tbl_masterpop, and 0 records in tbl_archive.

What should I do? Someone please help, as I have just been sitting idly at
my desk, seeming incompetent for a week now.

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

join type not supported 5
delete query 1
duplicate records 3
unwanted duplicate loan account numbers 1
DUPLICATE QUERY results 1
Query Error 2
combine 2 access queries 3
runtime error 3001 invalid argument error message 2

Top