Re: Query keeps freezing
From: MGFoster (me_at_privacy.com)
Date: 03/26/04
- Next message: MGFoster: "Re: Updating a Linked ODBC table"
- Previous message: bj: "Updating a Linked ODBC table"
- In reply to: Andrew: "Re: Query keeps freezing"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 26 Mar 2004 03:36:28 GMT
Andrew wrote:
> 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" <me@privacy.com> wrote in message
<SNIP previous post>
>>
>>-----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-----
-----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-----
- Next message: MGFoster: "Re: Updating a Linked ODBC table"
- Previous message: bj: "Updating a Linked ODBC table"
- In reply to: Andrew: "Re: Query keeps freezing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
