| 12.0 |
Food
for Thought.
Some things to bear in mind about the design of the search pages
and the database.
On the original results page provided with the tutorial I have
displayed the variable values of WhereKeywordsString
and sqlString in order
to illustrate how the SELECT statements are retrieving the results.
Open the originalsearch.asp page in your browser and type of
the in the Keywords field and select Any Words from the
Search Type list. Click Submit.
You should see the following SELECT statement at the bottom
of the page:
SELECT DISTINCT AlbumID, ArtistName, AlbumTitle, Genre
FROM QryDisplayFields
WHERE AlbumID
IN (1,2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,25)
ORDER BY ArtistName ASC
As you can see the string held within the sqlString
variable is getting quite lengthy. Our database contains just
25 albums, imagine what might happen if our database contained
25,000 albums. As mentioned earlier a variable can contain a
String of up to approximately 2 billion characters in length,
so our variable may well cope with the length of the string
but we may have a bit of a performance issue.
The tutorial is a demonstration of a method of searching across
multiple tables and columns within a database but consideration
should be given to the structure of the database. The method
demonstrated here, may not be suitable for very large databases.
In the demonstration we have split the search to first look
for matching Text then for further criteria. We could have performed
the search in a single step, buy adding the CombinedFields
columns to the QryDisplayFields
Query then searching it as a column within that Query.
Try it. Open Access and open QryDisplayFields
in Design View, right click and select Show Table from the list.
Click on the Queries Tab in the Show Tables dialogue box and
add the QryCombinedFields
Query to the Query Designer window.
Double Click on CombinedFields
to add it to the columns in the lower half of the Query Designer
window.
Click on the red exclamation mark in the main Access toolbar
to run the Query.
The Query runs and shows all the fields we need for our Results
page, however note the number of rows returned, 898, our Query
returns a row for every track on an album and another for every
Format on which it is available. Our original 25 row Albums
table has grown into an 898 row monster of a Query. There is
nothing wrong with looking through 898 rows, many large databases
have tables containing thousands of rows. However our Query
is multiplying the number of rows in the Albums table by a factor
of roughly 36. Extrapolate this for 25,000 albums and we would
be searching through 900,000 rows.
Examine the FormatID and the CombinedFields rows within the
Query for AlbumID 1. The Track listing is repeated four times,
it is reproduced for each Format on which the Album is available.
When our search looks for text matches it will look through
the same text four times, which is woefully inefficient. Hence
we split the Search into two distinct operations.
There is still some redundancy wihin our search, the Artist
Name and Album Title are repeated at the start of the CombinedFields
column, a compromise to simplify the VBScript search function.
However our QryCombinedFields is only 307 rows, 1 for every
track, which we cannot improve upon, this compromise is far
more acceptable.
Imagine if our list of albums were much larger, there might
be perhaps at the most twenty or thirty albums by the same artist,
which isn't too bad, but there might be hundreds of albums with
Various Artists in the ArtistName column, introducing further
redundancy. When designing Queries it is important to take into
account the information held within the database fields when
considering efficiency.
Close the Query but don't save the changes.
Whenever we include a One to Many relationship within a Query
we will always see this multiplying of the original number of
tabe rows on the One side of a relationship by a factor equivalent
to the number of table rows on the Many side of the relationship.
If this all sounds like I've demonstrated how to produce some
search pages then explained why you shouldn't use them, this
is only a cautionary note. When designing a web site there will
nearly always be some issues which require a compromise between
functionality and performance or efficiency.
Check out www.amazon.co.uk,
you will notice that you cannot find albums by searching for
them by track name unless you use the advanced search feature,
where the track search is kept separate from the Artist Name
And Album Title. |
