Precision Searching

From All n One's bxp software Wixi

Jump to: navigation, search

1 Background

On large database tables like searching can cause massive speed overhead for no appreciable useful functionality. Like matching is what gives us fuzzy searching.

A quick example to explain. If you have an id field with for example 8 digits or more in it. You have 1000 records. You enter in 123456 as your search criteria, the database server will search for every combo of that Id, e.g. 123456XX, X123456X and XX123456. So that's 3 different combinations on 1000 = 3000 searches. Fuzzy is helpful when searching names, but not really necessary when searching Id fields. Instead you should consider making the search an "exact" search. This means that the Id number is found exactly or not at all.

Here are some record statistics from bxp

  • Database: 1.2 million customer records
  • Timing (with fuzzy searching): 16.2 seconds average
  • Timing (with precision searching): 2 seconds average
  • Timing (with precision searching and custom index): 0.59 seconds average

2 Database query

In the background the system creates a database query to do the lookup. In a normal fuzzy search the query will have in the where clause "Where strCDA_X_field_0_0 like '%yourValue%'"

After turning on precision searching the query will now read as "Where strCDA_X_field_0_0 = 'yourValue'"

The % wildcard charcters are removed and the like statement is changed to a precision match.

3 Enabling Precision Searching

To enable precision searching for any client:

1. Main Menu > Database Management > Field Mapping > Select the campaign > identify the full field name of the column e.g strCDA_X_field_0_0

2. Main Menu > Database Management > Database – Primary Management > Campaign – Edit > Select the campaign > Advanced Options > Precision Search Fields

3. In this box put the field identified in item 1.

Multiple fields may be made precise using this method, simply add multiple fields separated by commas to this same box.

N.B. A precision match means that case sensitivity is also adhered to. i.e. "bxp Software" is not the same as "bxp software"

4 Indexes

An index is a special feature of a database which has the ability to vastly increase the speed of searches at the cost of space. Imagine some data.

id first_name last_name primary interest company_name address city
1 Emma Lacey Product 1 Alan D Rosenburg Cpa Pc 14 Taylor St St. Stephens Ward
2 Evan Zigomalas Product 1 Cap Gemini America 5 Binney St Abbey Ward
3 France Andrade Product 1 Elliott, John W Esq 8 Moor Place East Southbourne and Tuckton W
4 Ulysses Mcwalters Product 1 Mcmahan, Ben L 505 Exeter Rd Hawerby cum Beesby
5 Tyisha Veness Product 1 Champagne Room 5396 Forth Street Greets Green and Lyng Ward
6 Eric Rampy Product 1 Thompson, Michael C Esq 9472 Lind St Desborough
7 Marg Grasmick Product 1 Wrangle Hill Auto Auct & Slvg 7457 Cowl St #70 Bargate Ward
8 Laquita Hisaw Product 1 In Communications Inc 20 Gloucester Pl #96 Chirton Ward
9 Lura Manzella Product 1 Bizerba Usa Inc 929 Augustine St Staple Hill Ward

To search by the surname is a difficult process of checking each one. An index would create an ordered list. Andrade, Grasmick, Hisaw, Lacey, Manzella, Mcwalters, Rampy, Veness, Zigomalas. This would make searching far easier, but the duplication of data is a significant overhead as a mini dataset is created.

id last_name
3 Andrade
7 Grasmick
8 Hisaw
1 Lacey
9 Manzella
4 Mcwalters
6 Rampy
5 Veness
2 Zigomalas

Indexes can also be grouped, e.g. firstname and surname. Indexes for large data sets are limited to the primary searching fields and the most commonly grouped fields.

It is often sensible to create an index on an "Id" field or an identification field that contains values from another system. Indexing these fields is always useful for speed.

bxp has numerous default indexes added but in custom forms, there is no way to know which field to add indexes to.

If you need to enable a custom index, please contact your business development manager to have the field indexed for you. (