Query keeps freezing

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

I have an MSAccess 2K database with approximately 25,000 records, sometimes
the following query, freezes and I have to kill access.

SELECT DISTINCT MainSaleTable.CopyedRPList, MainSaleTable.CopyedTotalSP,
MainSaleTable.LastSaleDate
FROM MainSaleTable
WHERE (((MainSaleTable.CopyedRPList) In (SELECT [CopyedRPList] FROM
[MainSaleTable] As Tmp GROUP BY [CopyedRPList] HAVING Count(*)>1 ) And
(MainSaleTable.CopyedRPList)<>"Not yet entered"))
ORDER BY MainSaleTable.CopyedRPList;

The main field being CopyedRPList contains a text field and it appears that
when this field contains more than roughly 150 characters the problem occurs
(Remove the larger records and it works ok.)

I am trying to produce a report that displays any previous or more recent
sales than the sale currently being looked at. A subreport based on this
query is linked (master/child) via CopyedRPList. The report works fine
except when the report contains these larger entries? Running the query over
the whole 25,000 records causes the query to run to the end of the progress
bar, but then it stops and freezes.

Is there some maximum record length limit that I am somehow breaching (Field
set to 256)?
Any help would be welcome.

Thanks Andrew
 
Andrew said:
I have an MSAccess 2K database with approximately 25,000 records, sometimes
the following query, freezes and I have to kill access.

SELECT DISTINCT MainSaleTable.CopyedRPList, MainSaleTable.CopyedTotalSP,
MainSaleTable.LastSaleDate
FROM MainSaleTable
WHERE (((MainSaleTable.CopyedRPList) In (SELECT [CopyedRPList] FROM
[MainSaleTable] As Tmp GROUP BY [CopyedRPList] HAVING Count(*)>1 ) And
(MainSaleTable.CopyedRPList)<>"Not yet entered"))
ORDER BY MainSaleTable.CopyedRPList;

The main field being CopyedRPList contains a text field and it appears that
when this field contains more than roughly 150 characters the problem occurs
(Remove the larger records and it works ok.)

I am trying to produce a report that displays any previous or more recent
sales than the sale currently being looked at. A subreport based on this
query is linked (master/child) via CopyedRPList. The report works fine
except when the report contains these larger entries? Running the query over
the whole 25,000 records causes the query to run to the end of the progress
bar, but then it stops and freezes.

Is there some maximum record length limit that I am somehow breaching (Field
set to 256)?
Any help would be welcome.
Click to expand...

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

The query is probably still running, just very laboriously. Its speed
can be improved by: putting indexes on columns used in WHERE clauses and
ORDER BY clauses; improving the design of the db/tables; increasing the
PCs memory.

It's a really BAD idea to use a large string (TEXT) column in a WHERE
criteria clause - it takes longer to compare strings than numbers.

The term "any previous or more recent sales" seems to mean ALL sales
before the indicated sale and ALL sales after the indicated sale. Do
you really want that? Do you really mean something like: "all sales
within the previous 6 months before the indicated sale"?

MainSaleTable.CopyedRPList:

This column seems to be one that could be better designed. I'd use ID
numbers in the MainSaleTable and have a lookup table to hold the
descriptions of what each ID means. E.g.:

RPListCodes:
RPListID Autonumber
RPListDescription - Text - holds the descriptions

Example of data for above table:

RPListID RPListDescription
- -------- -----------------
1 Not yet entered
2 Sale pending
3 Order shipped
4 Customer cancelled

Then your In () clause search would be a lot faster, 'cuz the query
would only be looking at numbers instead of characters (long numbers are
4 bytes; a string can be greater than 4 bytes) and the search would be
faster.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGORYoechKqOuFEgEQL1XQCdEA7Bow1j4xCLxG9oIsX+7AdP1xsAn35O
W2xr0Yxi5YSi0pUW6N9D+R/g
=xyD4
-----END PGP SIGNATURE-----
 
Thanks for the considered reply. Sorry for missleading you the "query" does
run on it's own, slowly, the error occurs in the running of the report, when
these long Text "CopyedRPList" properties are included. The following
message box is presented "This action will reset the current code" and
everything freezes and won't allow you to end the code. The report is fairly
straight forward but includes a subreport, the source of which is this
query. Even for a two property report, which shows a result with a
restricted query, the report falls over. This does not happen with a 30
property report all of which have short CopyedRPList fields????

By previous sales I mean previous sales of the same property ie.

No 41 Smith Streets Sale $200,000 1/1/2003
Previous sale $150,000 13/2/2002

Properties are identified by the field CopyedRPList which is a String
something like "Lot 5 on Registered Plan 446464"
My query searches the database for all Properties with a duplicate to the
CopyedRPList ie

CopyedRPList LastSaleDate Price
Lot 5 on Registered Plan 446464 1/1/2003 $200,000
Lot 5 on Registered Plan 446464 13/2/2003 $150,000

Hence if a person produces a Sales list, they can elect to include Sales
history, using a linked sub from will show all sales included in the
database matching the current sale.

My problem is that some of the property descriptions can be very large (Some
commercial properties can contain 20 separate property descriptions)
This field CopyedRPList is the only variable that a property can be matched
on (Address more prone to errors)

I'm assuming for some reason that the resultant query Report linked by
Child/Master with a large Text field is too complex and causing an error?
The number of large Descriptions is limited and I am happy if these were
excluded somehow.

Andrew






MGFoster said:
Andrew said:
I have an MSAccess 2K database with approximately 25,000 records, sometimes
the following query, freezes and I have to kill access.

SELECT DISTINCT MainSaleTable.CopyedRPList, MainSaleTable.CopyedTotalSP,
MainSaleTable.LastSaleDate
FROM MainSaleTable
WHERE (((MainSaleTable.CopyedRPList) In (SELECT [CopyedRPList] FROM
[MainSaleTable] As Tmp GROUP BY [CopyedRPList] HAVING Count(*)>1 ) And
(MainSaleTable.CopyedRPList)<>"Not yet entered"))
ORDER BY MainSaleTable.CopyedRPList;

The main field being CopyedRPList contains a text field and it appears that
when this field contains more than roughly 150 characters the problem occurs
(Remove the larger records and it works ok.)

I am trying to produce a report that displays any previous or more recent
sales than the sale currently being looked at. A subreport based on this
query is linked (master/child) via CopyedRPList. The report works fine
except when the report contains these larger entries? Running the query over
the whole 25,000 records causes the query to run to the end of the progress
bar, but then it stops and freezes.

Is there some maximum record length limit that I am somehow breaching (Field
set to 256)?
Any help would be welcome.
Click to expand...

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

The query is probably still running, just very laboriously. Its speed
can be improved by: putting indexes on columns used in WHERE clauses and
ORDER BY clauses; improving the design of the db/tables; increasing the
PCs memory.

It's a really BAD idea to use a large string (TEXT) column in a WHERE
criteria clause - it takes longer to compare strings than numbers.

The term "any previous or more recent sales" seems to mean ALL sales
before the indicated sale and ALL sales after the indicated sale. Do
you really want that? Do you really mean something like: "all sales
within the previous 6 months before the indicated sale"?

MainSaleTable.CopyedRPList:

This column seems to be one that could be better designed. I'd use ID
numbers in the MainSaleTable and have a lookup table to hold the
descriptions of what each ID means. E.g.:

RPListCodes:
RPListID Autonumber
RPListDescription - Text - holds the descriptions

Example of data for above table:

RPListID RPListDescription
- -------- -----------------
1 Not yet entered
2 Sale pending
3 Order shipped
4 Customer cancelled

Then your In () clause search would be a lot faster, 'cuz the query
would only be looking at numbers instead of characters (long numbers are
4 bytes; a string can be greater than 4 bytes) and the search would be
faster.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGORYoechKqOuFEgEQL1XQCdEA7Bow1j4xCLxG9oIsX+7AdP1xsAn35O
W2xr0Yxi5YSi0pUW6N9D+R/g
=xyD4
-----END PGP SIGNATURE-----
Click to expand...
 
Andrew said:
Thanks for the considered reply. Sorry for missleading you the "query" does
run on it's own, slowly, the error occurs in the running of the report, when
these long Text "CopyedRPList" properties are included. The following
message box is presented "This action will reset the current code" and
everything freezes and won't allow you to end the code. The report is fairly
straight forward but includes a subreport, the source of which is this
query. Even for a two property report, which shows a result with a
restricted query, the report falls over. This does not happen with a 30
property report all of which have short CopyedRPList fields????

By previous sales I mean previous sales of the same property ie.

No 41 Smith Streets Sale $200,000 1/1/2003
Previous sale $150,000 13/2/2002

Properties are identified by the field CopyedRPList which is a String
something like "Lot 5 on Registered Plan 446464"
My query searches the database for all Properties with a duplicate to the
CopyedRPList ie

CopyedRPList LastSaleDate Price
Lot 5 on Registered Plan 446464 1/1/2003 $200,000
Lot 5 on Registered Plan 446464 13/2/2003 $150,000

Hence if a person produces a Sales list, they can elect to include Sales
history, using a linked sub from will show all sales included in the
database matching the current sale.

My problem is that some of the property descriptions can be very large (Some
commercial properties can contain 20 separate property descriptions)
This field CopyedRPList is the only variable that a property can be matched
on (Address more prone to errors)

I'm assuming for some reason that the resultant query Report linked by
Child/Master with a large Text field is too complex and causing an error?
The number of large Descriptions is limited and I am happy if these were
excluded somehow.

Andrew

"MGFoster" <[email protected]> wrote in message
Click to expand...

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

"Properties are identified by the field CopyedRPList which is a String
something like 'Lot 5 on Registered Plan 446464'."

Yea, that's what I was referring to - instead of a long text string
like:

Lot 5 on Registered Plan 446464

You create a table of PropertiesHandled that would have an ID number for
the description. E.g.:

ID PropertyDesc
- ---- -------------------------------
25 Lot 5 on Registered Plan 446464
800 No 41 Smith Streets

The ID would be stored in your main table instead of the PropertyDesc
(use a ComboBox on the form that has the ID & PropertyDesc from
PropertiesHandled table - the user selects the required property
description - the ID is stored in the form's underlying table). Then in
your query you'd use the ID instead the PropertyDesc as the search
column.

I agree that preventing duplicates on text fields is hard - 'cuz
different people have different ways of describing things. Usually, I
recommend that only one person update the table - familiarity being the
best defense against duplication (and a UNIQUE index on the description
column).

To get all the previous sales of a property (you'd have to know the
property ID):

PARAMETERS [Property ID] Long;
SELECT S.*
FROM Sales As S
WHERE S.SalesDate < (SELECT Max(SalesDate)
FROM Sales
WHERE PropertyID = [Property ID])

For sub- reports/forms I've found it is easier to create a simple query
that gets ALL data & then let the master form/report filter out the
correct data. It would seem that a large query result would slow down
the processing, but it doesn't appear that way, in my experience. If
the processing time gets too long you can limit the results of the sub-
form/report's query by setting the criteria from a "criteria form."
E.g.:

PARAMETERS Forms!frmCriteria!txtPropertyID Long;
SELECT S.*
FROM Sales As S
WHERE S.SalesDate < (SELECT Max(SalesDate)
FROM Sales
WHERE PropertyID = Forms!frmCriteria!txtPropertyID )

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGOlToechKqOuFEgEQLczgCg5Gipxtaby330E85sE3jJdACEkV8An20y
1WH5dT+bdIg2O5L8AC4BHFIE
=T4mM
-----END PGP SIGNATURE-----
 
Back
Top