Select Query too slow 2

[MA04]

Hi all,

My select query is too slow i need a way to speed up the process. Please bare with me while reading through.
The query uses table:
dbo_uk_homes
with fields:
POSTCODE(text)
PRMF(memo)
STRD(text)
STR(text)
LOCDD(text)
LOCD(text)
PTN(text)
CNT(text)
WCD(text)

Now the query has a criteria for POSTCODE field this is a textbox called: EnterPostcode on form called: MA_SEARCH. The user enters a postcode and the query is run.

The query manipulates the field involved like so:
PRMF holds the first line of an address, but for each postcode Field all addresses are listed in one memo field they are divided by semicoloms ";".

The query i have splits each address by the semicolons making them into seperate records with the same postcode. The code then adds fields STRD and STR to the seperate records of the PRMF they are divided by commas, except if the value of PRMF is less than 4 then comma is missed out in that record the part connecting PRMF with STRD OR STR, also if any of the fields are null then it adds on the next field e.g. PRMF, STRD, STR or PRMF, STR OR PRMF, STRD The alias of Expr1 is given to this.

The code also combines fields LOCDD and LOCD together seperated by comma if both are present. This is given alias Expr2

The code also combines fields PTN and CNT together seperated by comma if both are present. This is given alias Expr3

All fields query creates:
POSTCODE
PRMF, STRD, STR AS Expr1
LOCDD, LOCD AS Expr2
CNT, PTN AS Expr3
WCD

This is what slows down process A table name Index is used to store the length of every character in the query. The table index has one field called: num Which at present holds 900 records, which are all numbered from 1 through to 900 each record representing a character in the query result.

This is the query:

SELECT I1.POSTCODE, getA1A2A3(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),[Strd],[STR]) AS Expr1, IIf(IsNull([I1].[Locd]),[I1].[Locdd],[I1].[Locd] & IIf(IsNull([Locdd]),"",", " & [Locdd])) AS Expr2, IIf(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr3, [I1].[WCD]
FROM dbo_UK_homes AS I1, integers AS S1, integers AS S2
WHERE (((I1.POSTCODE)=[Forms]![ma_search2]![EnterPostcode]) AND ((IIf(IIf(Len(Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1))>0,Len(Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1)),0)<4,Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1) & " " & [I1].[Strd] & [I1].[STR],Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1) & IIf(IsNull([Strd]),"",", " & [Strd]) & IIf(IsNull([Strd]) Or Not IsNull([Locdd] & [Locd]),IIf(IsNull([STR]),'',', ' & [I1].[STR])))) Like "*" & IIf([Forms]![Ma_search2]![txN_searcht1]="","",[Forms]![Ma_search2]![N_search]) & "*") AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));

txN_searcht1 above is another textbox for optional search entry. It calls a module where: getA1A2A3 is called
This is the module:

Public Function getA1A2A3(A1 As String, A2, A3) As String
If IsNull(A2) And IsNull(A3) Then getA1A2A3 = A1: Exit Function
Dim S As String
S = A1 & IIf(Len(A1) < 4, " ", ", ")
If IsNull(A3) Then
  S = S & A2
Else
  S = S & IIf(IsNull(A2), "", A2 & ", ") & A3
End If
getA1A2A3 = S
End Function

Here is an e.g of code in action:
POSTCODE(TEXT)
OP11 9UV
PRMF(memo)
Mossbank Primary School;Northlea Auto Point;Post Office;The Old School Centre;1;2;3
STRD(text)
(NULL)
STR(text)
Jenson Park
LOCDD(text)
Pentwith
LOCD(text)
Mile Haven
PTN(text)
Chester
CNT(text)
(NULL)
WCD(text)
866YT

This will result:
Postcode... Expr1................................................ Expr2... and so on
OP11 9UV Mossbank Primary School, Jenson Park Pentwith, Mile Haven
OP11 9UV Northlea Auto Point, Jenson Park Pentwith, Mile Haven

the rest will be similar the numbers at the end will be like so:

OP11 9UV 1 Jenson Park Pentwith, Mile Haven
OP11 9UV 2 Jenson Park Pentwith, Mile Haven
OP11 9UV 3 Jenson Park Pentwith, Mile Haven

i.e. no comma.

The main thing that is slowing it down is table: index the greater the amount of records it holds the slower the query runs any way to speed up process?

Any help very much appreciated, thank in advance,
M-.

 

[MA04]

Sorry Slight error the table that stores character length is called: integers and not index as above.

The problem arose when a user tried to search through a record that had a memo field with seperate addresses adding up to 831 characters under one postcode, in order to display all these entries i had to increase the number of records in the integer table to more than 831 records. The number of records has slowed down the whole process, i really need a way to speed this process up or an alternative way to doing this.

Any help very much appreciated, thanks in advance,

M-.

 

[MA04]

Hi all,

Still not figured a way to speed up process, but had an idea but could not get it to work.

The only way with the present code to speed up query is to reduce the number of records in the integers table. Now i can reduce the record amount from 900 to say 300, speeding up process significantly, but with disadvantage of not displaying all the records. Now the idea is say the user enters a postcode and cannot find record he/she wants but knows the first line of address name. The user can then enter a wildcard entry of this in the optional element the textbox to enter an optional part in this case N_search. Now i was hoping that this will display the searched part, even though this did not display first time around. At the moment this optional part only narrows a search on addresses that initially fit within chracter length of say 300 in this example.

for e.g. say record below was the one user wanted to search through
POSTCODE(TEXT)
OP11 9UV
PRMF(memo)
Mossbank Primary School;Northlea Auto Point;Post Office;The Old School Centre;1;2;3
STRD(text)
(NULL)
STR(text)
Jenson Park
LOCDD(text)
Pentwith
LOCD(text)
Mile Haven

so user enters postcode in textbox: Enterpostcode = OP11 9UV
The following is then displayed:
Postcode... Expr1................................................ Expr2...
OP11 9UV Mossbank Primary School, Jenson Park Pentwith, Mile
OP11 9UV Northlea Auto Point, Jenson Park Pentwith, Mile Haven Haven


But user wants 3 Jenson Park, so enters: 3 * in textbox: N_search and the following is displayed:

OP11 9UV 3 Jenson Park Pentwith, Mile Haven Haven

At present it will display nothing as initially did not fit. Anyway of getting it to work this way? i.e. if optional bit entered the query should filter for that bit so no need to store all character lengths within table, just the one searched for.[/color red]

The optional bit is in blue

SELECT I1.POSTCODE, getA1A2A3(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),[Strd],[STR]) AS Expr1, IIf(IsNull([I1].[Locd]),[I1].[Locdd],[I1].[Locd] & IIf(IsNull([Locdd]),"",", " & [Locdd])) AS Expr2, IIf(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr3, [I1].[WCD]
FROM dbo_UK_homes AS I1, integers AS S1, integers AS S2
WHERE (((I1.POSTCODE)=[Forms]![ma_search2]![EnterPostcode]) AND ((IIf(IIf(Len(Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1))>0,Len(Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1)),0)<4,Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1) & " " & [I1].[Strd] & [I1].[STR],Mid$(';' & [I1].[PRMF] & ';',[S1].[num]+1,[S2].[num]-[S1].[num]-1) & IIf(IsNull([Strd]),"",", " & [Strd]) & IIf(IsNull([Strd]) Or Not IsNull([Locdd] & [Locd]),IIf(IsNull([STR]),'',', ' & [I1].[STR])))) [COLOR=blue]Like "*" & IIf([Forms]![Ma_search2]![N_search]="","",[Forms]![Ma_search2]![N_search]) & "*")[/color blue]AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));

Thanks in advance for any help,
M-.

 

[MA04]

Hi all,

I have taken away the optional bit of the criteria textbox: N_search [/color brown]which has condensed code quite a bit. Still works but exludes extra option for user.

Here is what it looks like now:

SELECT I1.POSTCODE, getA1A2A3(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),[Strd],[STR]) AS Expr1, IIf(IsNull([I1].[Locd]),[I1].[Locdd],[I1].[Locd] & IIf(IsNull([Locdd]),"",", " & [Locdd])) AS Expr2, IIf(IsNull([I1].[PTN]),[I1].[CNT],[I1].[PTN] & IIf(IsNull([CNT]),"",", " & [CNT])) AS Expr3, [I1].[WCD]
FROM dbo_UK_homes AS I1, integers AS S1, integers AS S2
[COLOR=green]WHERE (((I1.POSTCODE)=[Forms]![ma_search2]![EnterPostcode]) AND ((Mid$(";" & [I1].[PRMF] & ";",[S1].[num],1))=';') AND ((Mid$(";" & [I1].[PRMF] & ";",[S2].[num],1))=';') AND (([S1].[num]+1)<[S2].[num]) AND ((InStr(1,Mid$(";" & [I1].[PRMF] & ";",[S1].[num]+1,[S2].[num]-[S1].[num]-1),";"))=0));[/color green]

Now i was hoping to add the optional element again:
Like "*" & IIf([Forms]![Ma_search2]![txN_searcht1]="","",[Forms]![Ma_search2]![N_search]) & "*"[/color blue]
i.e. user has choice whether to narrow search or not.

But what i was hoping for was when user enters optional bit, the query only filters by postcode and wildcard entry so the table: integers [/color red]only uses up records for characters for the filtered search and not all records that would be produced if wildcard was not used.

Hope that makes sense, if not i will be happy to clarify. Would this be possible?

Any help very much appreciated, thanks in advance,
M-.

 

[BNPMike]

I would just make a general comment here. Relational databases are supposed to be normalised to First Normal Form. Yours isn't. I think your problem demonstrates that you then need to fight the system to get things done.

 

[MA04]

Thanks for that earthandfire, will do if need help. cmmrfrds i don't think i can do that for this in this occasion thanks for the suggestion though,

M-.

 

[MA04]

Thanks for the link earthandfire, will do if need further help, cmmrfrds i don't think i can do that for this on this occasion, thanks for the idea though,

M-.

 

[earthandfire]

cmmrfrds, the original PAF data is normalised, however the data that MA04 has been given to work with is a somewhat flattened version of it.

 

[MA04]

The work database is in a mess and they need to start clearing things up. Although in this ocasion the table is an imported on, that is from an imported file. The table holds all registered addresses in the uk, which is why it is in this format i.e. for each postcode all addresses are under. I have created a form that will allow users to view specific addresses when a customer rings in and append it to the work address table with user details.

The only problem with my process at present is its too slow if i wish to display certain memo fields with a large number of entries i have to increase the size of my integer table, which thewill slow down the whole proess even postcodes with small number of addresses.

Again any ideas, comments, help appreciated,
M-.

 

[Golom]

You may be misinterpreting the meaning of the Integers.Num fields in your query. It is not related to the number of records in the table but rather to the length of the string in the field I1.PRMF plus 2. You need only enough integers to reach that "length + 2" value. If you have more then it will cause a lot of meaningless processing and cost you processing time.

You might try an additional WHERE clause of the form

WHERE ... existing clauses ... 
AND S1.Num <= Len(I1.PRMF) + 2
AND S2.Num <= Len(I1.PRMF) + 2

 

[earthandfire]

I use the RM PAF File indexing each table on the key fields

 

[MA04]

Thanks for that Golom, That does seem to speed up process for the smaller length searches though still slow larger length searches. Though not sure i understand code that you sent and its purpose, can you explain.

Thanks,
M-.

 

[MA04]

We also use the RM PAF File. Though i only have access to the imported table, which is in format above.

 

[earthandfire]

Is there no way that you can get the actual PAF data? It is so much easier to work with, and with indexing, very fast.

 

[MA04]

Just wondering how would i achieve what i want if i had the actual data, may be able to puruade access to it then.

 

[cmmrfrds]

Sometimes it is necessary to preprocess the data to make it acceptable for reporting. Typically, applications are set up for OLTP so are not optimized for reporting.

For example, could you run a process that prepares the data each morning and stores it in a separate table. You could report from this table or use it as an index into other tables.

 

[earthandfire]

The PAF Digest, available at:

http://www.royalmail.com/portal/rm/content1?catId=400085&mediaId=1400098

is probably your best way forward, it gives a detailed explanation of every aspect of PAF and how to interpret the data.

If you need any help once you've read it let me know.

 

[Golom]

The code that you are using was originally developed by a guy named Joe Celko who wrote SQL for Smarties. In that book Joe published a lot of SQL for doing things like parsing substrings from a string (i.e. this example), computing statistics, etc. ... anyway ... here's what the code does.

In the SELECT Clause
(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1),[Strd],[STR]) AS Expr1
Parses out the sub-string starting at S1.num+1 for S2.Num-S1.num-1 characters.

The real power is in the WHERE clause
Select only those substrings where
- The string starts with your delimiter (";" in your case)
- The substring ends with the delimiter
- The substring does not have any embedded delimiters (i.e. between the start and the end)
- The start position for the substring is before the end position.

The whole approach is rather process intensive. For example, a string of 30 characters with ";" prepended and appended will generate 900 possible substrings. If there are only 2 strings inside it such as
";abcdefg...;xyz123;
then the where clause will eliminate 898 of them leaving you with the two strings shown.

That's why the best application of this technique is to build a normalized version of your data using a make-table and then do further processing on the normalized version of the table. As you have discovered, it has (and will always have) performance problems if you attempt to use it in a production environment.

The code I gave you just restricts the number of possible combinations that are tested to only those that may produce a useful result. For example, if the string length is 30 but you have 100 integers in the Integers table then it will process 100 * 100 combinations where only 30 * 30 could possibly produce a result ... the others being beyond the end of the string.

 

[earthandfire]

MA04

Just another thought. I don't knoww whether or not you are aware of this but a postcode, as well as referring to more than one building in a street, can also refer to more than one street (although RM are gradually issuing new postcodes to circumvent this problem).

This means that your search for a postcode could, in theory at least, return more than one record each with multiple building/organisation/number fields, so any solution you use based on your current datasource would have to bear that in mind.

 

[MA04]

Hi guys,

Golom thank you for your explanation of the code and earthandfire thank you for that insight i was not completely aware that a single postcode can have different streets, i will certainly bear this in mind while going through code. This has given me more impotence to get hold of the original data which like earthandfire has stated is in a normalized state as such, this shows one of the greater reasons why relational and normalized tables are quite essential in access. Thanks for all the help guys,

M-.