Doing the unthinkable with my tables

I recently posted a question which was answered by Allen Browne and gave me
some tips to structure the tables. However, when it comes to searching the
database, performance is unacceptably slow and I am now thinking of doing
the unthinkable with my tables and wonder whether anyone has used (or still
uses) this sort of approach.

The database stores libraries and classifies the types of book they keep. So
I have the classic 3-table design: tblLibrary, tblCategory, tblLibCat, which
is the junction table showing LibID and CatCode. Additionally, the CatCodes
are masked so that
050000 = Languages
050100 = European Languages
050101 = English
050102 = French
050103 = German

This enables me to ask "who stocks books on any European language?" as LIKE
"0501??". This was going OK until I tried to find out who stocks (maths and
physics) or (computers and philosophy). This did not sound an
overly-complex question to ask but as you may be able to guess from the SQL,
my pc went on strike for the next ten minutes, protesting mainly over the OR
clause. There are, by the way, about 25,000 libraries with about 80,000
junction table records.

SELECT * FROM tblLibrary WHERE
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE "07????"))
AND
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE "09????"))
OR
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE "12????"))
AND
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE "19????"))

So what I did (shock/horror) was to create a field tblLibrary.LibSubjectList
which contained a comma-separated list of all the categories stocked e.g.
",070111,120100,120200,170602," which is just the sort of thing you don't
normally do in a relational database, although I am keeping the junction
table as being the master list so I can always re-sync the subject list.
The query now becomes:

SELECT * FROM tblLibrary WHERE
(LibSubjectList LIKE "*,07????,*" AND LibSubjectList LIKE "*,09????,*" )
OR
(LibSubjectList LIKE "*,12????,*" AND LibSubjectList LIKE "*,19????,*" )

and performance is lightning quick. In fact, I can't ask a question about
which books are held which is not answered almost immediately. There are
some drawbacks though:
1. The SubjectList is a 255-character indexed text field which means the
maximum number of subjects per library is 36. That is, 6 characters per
subject plus a trailing comma multiplied by 36 then add a comma to the
beginning of the list gives 253 characters. This limitation is acceptable.
2. I cannot enforce referential integrity. Although I can always re-sync
the subject list with the junction table and the classifications should stay
virtually unchanged throughout the life of the database, it is still
something I need to be extremely cautious about. If the two lists don't
match, all the searching goes wrong.
3. I have never used this approach before, but no other seems to give
acceptable speed and I can't turn round and say (maths and physics) or
(computers and philosophy) is too difficult a question!

Any comments anyone?

re: Doing the unthinkable with my tables

I don't know if this would help at all, but you may try re-wording your
LIKE statement. Would it make a difference if you used "07*" instead
of "07????" ?

This may keep the SQL engine from having to perform operations on
fixed-length strings and that may speed things up. Once it figures out
that the third digit is not correct, it does not need to check the
other three.

On a similar tach, you could try to parse the string before you do your
compare. Setup a few more key fields. Have one called "CatClass :
left(LctLibID,3)" and another called "CatLevel: [LctLibID]" LangBreak
your WHERE clauses up between these different key fields. One for each
logical level for your code.

Just a few thoughts....

Alex.


Stefan Kowalski wrote:[color=blue]
> I recently posted a question which was answered by Allen Browne and[/color]
gave me[color=blue]
> some tips to structure the tables. However, when it comes to[/color]
searching the[color=blue]
> database, performance is unacceptably slow and I am now thinking of[/color]
doing[color=blue]
> the unthinkable with my tables and wonder whether anyone has used (or[/color]
still[color=blue]
> uses) this sort of approach.
>
> The database stores libraries and classifies the types of book they[/color]
keep. So[color=blue]
> I have the classic 3-table design: tblLibrary, tblCategory,[/color]
tblLibCat, which[color=blue]
> is the junction table showing LibID and CatCode. Additionally, the[/color]
CatCodes[color=blue]
> are masked so that
> 050000 = Languages
> 050100 = European Languages
> 050101 = English
> 050102 = French
> 050103 = German
>
> This enables me to ask "who stocks books on any European language?"[/color]
as LIKE[color=blue]
> "0501??". This was going OK until I tried to find out who stocks[/color]
(maths and[color=blue]
> physics) or (computers and philosophy). This did not sound an
> overly-complex question to ask but as you may be able to guess from[/color]
the SQL,[color=blue]
> my pc went on strike for the next ten minutes, protesting mainly over[/color]
the OR[color=blue]
> clause. There are, by the way, about 25,000 libraries with about[/color]
80,000[color=blue]
> junction table records.
>
> SELECT * FROM tblLibrary WHERE
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
"07????"))[color=blue]
> AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
"09????"))[color=blue]
> OR
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
"12????"))[color=blue]
> AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
"19????"))[color=blue]
>
> So what I did (shock/horror) was to create a field[/color]
tblLibrary.LibSubjectList[color=blue]
> which contained a comma-separated list of all the categories stocked[/color]
e.g.[color=blue]
> ",070111,120100,120200,170602," which is just the sort of thing you[/color]
don't[color=blue]
> normally do in a relational database, although I am keeping the[/color]
junction[color=blue]
> table as being the master list so I can always re-sync the subject[/color]
list.[color=blue]
> The query now becomes:
>
> SELECT * FROM tblLibrary WHERE
> (LibSubjectList LIKE "*,07????,*" AND LibSubjectList LIKE[/color]
"*,09????,*" )[color=blue]
> OR
> (LibSubjectList LIKE "*,12????,*" AND LibSubjectList LIKE[/color]
"*,19????,*" )[color=blue]
>
> and performance is lightning quick. In fact, I can't ask a question[/color]
about[color=blue]
> which books are held which is not answered almost immediately. There[/color]
are[color=blue]
> some drawbacks though:
> 1. The SubjectList is a 255-character indexed text field which means[/color]
the[color=blue]
> maximum number of subjects per library is 36. That is, 6 characters[/color]
per[color=blue]
> subject plus a trailing comma multiplied by 36 then add a comma to[/color]
the[color=blue]
> beginning of the list gives 253 characters. This limitation is[/color]
acceptable.[color=blue]
> 2. I cannot enforce referential integrity. Although I can always[/color]
re-sync[color=blue]
> the subject list with the junction table and the classifications[/color]
should stay[color=blue]
> virtually unchanged throughout the life of the database, it is still
> something I need to be extremely cautious about. If the two lists[/color]
don't[color=blue]
> match, all the searching goes wrong.
> 3. I have never used this approach before, but no other seems to[/color]
give[color=blue]
> acceptable speed and I can't turn round and say (maths and physics)[/color]
or[color=blue]
> (computers and philosophy) is too difficult a question!
>
> Any comments anyone?[/color]

re: Doing the unthinkable with my tables

"Alex" <alex-brewer@rocketmail.com> wrote in message
news:1107197551.739558.126650@f14g2000cwb.googlegr oups.com...[color=blue]
>I don't know if this would help at all, but you may try re-wording your
> LIKE statement. Would it make a difference if you used "07*" instead
> of "07????" ?
>
> This may keep the SQL engine from having to perform operations on
> fixed-length strings and that may speed things up. Once it figures out
> that the third digit is not correct, it does not need to check the
> other three.
>
> On a similar tach, you could try to parse the string before you do your
> compare. Setup a few more key fields. Have one called "CatClass :
> left(LctLibID,3)" and another called "CatLevel: [LctLibID]" LangBreak
> your WHERE clauses up between these different key fields. One for each
> logical level for your code.
>
> Just a few thoughts....
>
> Alex.[/color]



Hi Alex
Thank you for your thoughts. Unfortunately they won't quite be enough to
make speed acceptable. Even if I do all the table indexing and even if I go
for an exact match (so no lengths to worry about), the OR clause just kills
it:
SELECT *
FROM tblLibrary
WHERE (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="010101"))
AND
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="010102"))
OR
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="180000"))
AND
(LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="190000"))

Trying to run this query, then scroll to the bottom record takes 5 minutes
of locked pc, whereas the query using tblLibrary.Subject list is almost
instant. I think this is the approach I will take, but I wonder whether
anyone else currently uses this method. It is a problem which must occur in
other types of databases and although
http://www.mvps.org/access/queries/qry0016.htm uses a 'helper table' to find
all of X having required Y, it does not address the OR issue.
Perhaps someone has a database with a similar 3-table structure, eg people
and skills. So you ask who knows (Access and VBA) OR (VB and SQL Server)
Somebody out there must be solving these types of queries.


[color=blue]
>
> Stefan Kowalski wrote:[color=green]
>> I recently posted a question which was answered by Allen Browne and[/color]
> gave me[color=green]
>> some tips to structure the tables. However, when it comes to[/color]
> searching the[color=green]
>> database, performance is unacceptably slow and I am now thinking of[/color]
> doing[color=green]
>> the unthinkable with my tables and wonder whether anyone has used (or[/color]
> still[color=green]
>> uses) this sort of approach.
>>
>> The database stores libraries and classifies the types of book they[/color]
> keep. So[color=green]
>> I have the classic 3-table design: tblLibrary, tblCategory,[/color]
> tblLibCat, which[color=green]
>> is the junction table showing LibID and CatCode. Additionally, the[/color]
> CatCodes[color=green]
>> are masked so that
>> 050000 = Languages
>> 050100 = European Languages
>> 050101 = English
>> 050102 = French
>> 050103 = German
>>
>> This enables me to ask "who stocks books on any European language?"[/color]
> as LIKE[color=green]
>> "0501??". This was going OK until I tried to find out who stocks[/color]
> (maths and[color=green]
>> physics) or (computers and philosophy). This did not sound an
>> overly-complex question to ask but as you may be able to guess from[/color]
> the SQL,[color=green]
>> my pc went on strike for the next ten minutes, protesting mainly over[/color]
> the OR[color=green]
>> clause. There are, by the way, about 25,000 libraries with about[/color]
> 80,000[color=green]
>> junction table records.
>>
>> SELECT * FROM tblLibrary WHERE
>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> "07????"))[color=green]
>> AND
>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> "09????"))[color=green]
>> OR
>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> "12????"))[color=green]
>> AND
>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> "19????"))[color=green]
>>
>> So what I did (shock/horror) was to create a field[/color]
> tblLibrary.LibSubjectList[color=green]
>> which contained a comma-separated list of all the categories stocked[/color]
> e.g.[color=green]
>> ",070111,120100,120200,170602," which is just the sort of thing you[/color]
> don't[color=green]
>> normally do in a relational database, although I am keeping the[/color]
> junction[color=green]
>> table as being the master list so I can always re-sync the subject[/color]
> list.[color=green]
>> The query now becomes:
>>
>> SELECT * FROM tblLibrary WHERE
>> (LibSubjectList LIKE "*,07????,*" AND LibSubjectList LIKE[/color]
> "*,09????,*" )[color=green]
>> OR
>> (LibSubjectList LIKE "*,12????,*" AND LibSubjectList LIKE[/color]
> "*,19????,*" )[color=green]
>>
>> and performance is lightning quick. In fact, I can't ask a question[/color]
> about[color=green]
>> which books are held which is not answered almost immediately. There[/color]
> are[color=green]
>> some drawbacks though:
>> 1. The SubjectList is a 255-character indexed text field which means[/color]
> the[color=green]
>> maximum number of subjects per library is 36. That is, 6 characters[/color]
> per[color=green]
>> subject plus a trailing comma multiplied by 36 then add a comma to[/color]
> the[color=green]
>> beginning of the list gives 253 characters. This limitation is[/color]
> acceptable.[color=green]
>> 2. I cannot enforce referential integrity. Although I can always[/color]
> re-sync[color=green]
>> the subject list with the junction table and the classifications[/color]
> should stay[color=green]
>> virtually unchanged throughout the life of the database, it is still
>> something I need to be extremely cautious about. If the two lists[/color]
> don't[color=green]
>> match, all the searching goes wrong.
>> 3. I have never used this approach before, but no other seems to[/color]
> give[color=green]
>> acceptable speed and I can't turn round and say (maths and physics)[/color]
> or[color=green]
>> (computers and philosophy) is too difficult a question!
>>
>> Any comments anyone?[/color]
>[/color]

re: Doing the unthinkable with my tables

Ok, so you have three tables. tblLibrary with key field LibID,
tblCategory with key field LctLatCode, and tblLibCat with no key field,
but multiple listings for LibID and LctLatCode. Why can't you just
join these in the normal manner and eliminate the sub-queries all
together?

Something like this (I probably don't have the field names correct,
though):

SELECT tblLibrary.*, tblLibCat.LctCatCode
FROM (tblLibrary INNER JOIN tblLibCat ON tblLibrary.LibID =
tblLibCat.LibID) INNER JOIN tblCategory ON tblLibCat.LctCatCode =
tblCategory.LctCatCode
WHERE (((tblLibCat.LctCatCode)="*190000")) OR
(((tblLibCat.LctCatCode)="*180000")) OR
(((tblLibCat.LctCatCode)="*010102")) OR
(((tblLibCat.LctCatCode)="*010101"));

This eliminates the sub-queries and lets the joins take care of it. I
have never used a sub-query and don't really see the need to as long as
you can just join the tables together into a view.

I bet it is the sub-queries, not the OR statements that is slowing you
down. If you have 1000 records and you have subqueries, then you will
probably have 1000 * 1000 accesses to the database when you run the
query. The join should eliminate most of those at the fore-front.

Alex.

Stefan Kowalski wrote:[color=blue]
> "Alex" <alex-brewer@rocketmail.com> wrote in message
> news:1107197551.739558.126650@f14g2000cwb.googlegr oups.com...[color=green]
> >I don't know if this would help at all, but you may try re-wording[/color][/color]
your[color=blue][color=green]
> > LIKE statement. Would it make a difference if you used "07*"[/color][/color]
instead[color=blue][color=green]
> > of "07????" ?
> >
> > This may keep the SQL engine from having to perform operations on
> > fixed-length strings and that may speed things up. Once it figures[/color][/color]
out[color=blue][color=green]
> > that the third digit is not correct, it does not need to check the
> > other three.
> >
> > On a similar tach, you could try to parse the string before you do[/color][/color]
your[color=blue][color=green]
> > compare. Setup a few more key fields. Have one called "CatClass :
> > left(LctLibID,3)" and another called "CatLevel: [LctLibID]"[/color][/color]
LangBreak[color=blue][color=green]
> > your WHERE clauses up between these different key fields. One for[/color][/color]
each[color=blue][color=green]
> > logical level for your code.
> >
> > Just a few thoughts....
> >
> > Alex.[/color]
>
>
>
> Hi Alex
> Thank you for your thoughts. Unfortunately they won't quite be[/color]
enough to[color=blue]
> make speed acceptable. Even if I do all the table indexing and even[/color]
if I go[color=blue]
> for an exact match (so no lengths to worry about), the OR clause just[/color]
kills[color=blue]
> it:
> SELECT *
> FROM tblLibrary
> WHERE (LibID IN (SELECT LctLibID FROM tblLibCat WHERE[/color]
LctCatCode="010101"))[color=blue]
> AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="010102"))
> OR
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="180000"))
> AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode="190000"))
>
> Trying to run this query, then scroll to the bottom record takes 5[/color]
minutes[color=blue]
> of locked pc, whereas the query using tblLibrary.Subject list is[/color]
almost[color=blue]
> instant. I think this is the approach I will take, but I wonder[/color]
whether[color=blue]
> anyone else currently uses this method. It is a problem which must[/color]
occur in[color=blue]
> other types of databases and although
> http://www.mvps.org/access/queries/qry0016.htm uses a 'helper table'[/color]
to find[color=blue]
> all of X having required Y, it does not address the OR issue.
> Perhaps someone has a database with a similar 3-table structure, eg[/color]
people[color=blue]
> and skills. So you ask who knows (Access and VBA) OR (VB and SQL[/color]
Server)[color=blue]
> Somebody out there must be solving these types of queries.
>
>
>[color=green]
> >
> > Stefan Kowalski wrote:[color=darkred]
> >> I recently posted a question which was answered by Allen Browne[/color][/color][/color]
and[color=blue][color=green]
> > gave me[color=darkred]
> >> some tips to structure the tables. However, when it comes to[/color]
> > searching the[color=darkred]
> >> database, performance is unacceptably slow and I am now thinking[/color][/color][/color]
of[color=blue][color=green]
> > doing[color=darkred]
> >> the unthinkable with my tables and wonder whether anyone has used[/color][/color][/color]
(or[color=blue][color=green]
> > still[color=darkred]
> >> uses) this sort of approach.
> >>
> >> The database stores libraries and classifies the types of book[/color][/color][/color]
they[color=blue][color=green]
> > keep. So[color=darkred]
> >> I have the classic 3-table design: tblLibrary, tblCategory,[/color]
> > tblLibCat, which[color=darkred]
> >> is the junction table showing LibID and CatCode. Additionally,[/color][/color][/color]
the[color=blue][color=green]
> > CatCodes[color=darkred]
> >> are masked so that
> >> 050000 = Languages
> >> 050100 = European Languages
> >> 050101 = English
> >> 050102 = French
> >> 050103 = German
> >>
> >> This enables me to ask "who stocks books on any European[/color][/color][/color]
language?"[color=blue][color=green]
> > as LIKE[color=darkred]
> >> "0501??". This was going OK until I tried to find out who stocks[/color]
> > (maths and[color=darkred]
> >> physics) or (computers and philosophy). This did not sound an
> >> overly-complex question to ask but as you may be able to guess[/color][/color][/color]
from[color=blue][color=green]
> > the SQL,[color=darkred]
> >> my pc went on strike for the next ten minutes, protesting mainly[/color][/color][/color]
over[color=blue][color=green]
> > the OR[color=darkred]
> >> clause. There are, by the way, about 25,000 libraries with about[/color]
> > 80,000[color=darkred]
> >> junction table records.
> >>
> >> SELECT * FROM tblLibrary WHERE
> >> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> > "07????"))[color=darkred]
> >> AND
> >> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> > "09????"))[color=darkred]
> >> OR
> >> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> > "12????"))[color=darkred]
> >> AND
> >> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode LIKE[/color]
> > "19????"))[color=darkred]
> >>
> >> So what I did (shock/horror) was to create a field[/color]
> > tblLibrary.LibSubjectList[color=darkred]
> >> which contained a comma-separated list of all the categories[/color][/color][/color]
stocked[color=blue][color=green]
> > e.g.[color=darkred]
> >> ",070111,120100,120200,170602," which is just the sort of thing[/color][/color][/color]
you[color=blue][color=green]
> > don't[color=darkred]
> >> normally do in a relational database, although I am keeping the[/color]
> > junction[color=darkred]
> >> table as being the master list so I can always re-sync the subject[/color]
> > list.[color=darkred]
> >> The query now becomes:
> >>
> >> SELECT * FROM tblLibrary WHERE
> >> (LibSubjectList LIKE "*,07????,*" AND LibSubjectList LIKE[/color]
> > "*,09????,*" )[color=darkred]
> >> OR
> >> (LibSubjectList LIKE "*,12????,*" AND LibSubjectList LIKE[/color]
> > "*,19????,*" )[color=darkred]
> >>
> >> and performance is lightning quick. In fact, I can't ask a[/color][/color][/color]
question[color=blue][color=green]
> > about[color=darkred]
> >> which books are held which is not answered almost immediately.[/color][/color][/color]
There[color=blue][color=green]
> > are[color=darkred]
> >> some drawbacks though:
> >> 1. The SubjectList is a 255-character indexed text field which[/color][/color][/color]
means[color=blue][color=green]
> > the[color=darkred]
> >> maximum number of subjects per library is 36. That is, 6[/color][/color][/color]
characters[color=blue][color=green]
> > per[color=darkred]
> >> subject plus a trailing comma multiplied by 36 then add a comma to[/color]
> > the[color=darkred]
> >> beginning of the list gives 253 characters. This limitation is[/color]
> > acceptable.[color=darkred]
> >> 2. I cannot enforce referential integrity. Although I can[/color][/color][/color]
always[color=blue][color=green]
> > re-sync[color=darkred]
> >> the subject list with the junction table and the classifications[/color]
> > should stay[color=darkred]
> >> virtually unchanged throughout the life of the database, it is[/color][/color][/color]
still[color=blue][color=green][color=darkred]
> >> something I need to be extremely cautious about. If the two lists[/color]
> > don't[color=darkred]
> >> match, all the searching goes wrong.
> >> 3. I have never used this approach before, but no other seems to[/color]
> > give[color=darkred]
> >> acceptable speed and I can't turn round and say (maths and[/color][/color][/color]
physics)[color=blue][color=green]
> > or[color=darkred]
> >> (computers and philosophy) is too difficult a question!
> >>
> >> Any comments anyone?[/color]
> >[/color][/color]

re: Doing the unthinkable with my tables

"Stefan Kowalski" <a@b.com> wrote in
news:ctnj11$gs3$1@sparta.btinternet.com:
[color=blue]
> Hi Alex
> Thank you for your thoughts. Unfortunately they won't quite
> be enough to make speed acceptable. Even if I do all the
> table indexing and even if I go for an exact match (so no
> lengths to worry about), the OR clause just kills it:
> SELECT *
> FROM tblLibrary
> WHERE (LibID IN (SELECT LctLibID FROM tblLibCat WHERE
> LctCatCode="010101")) AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE
> LctCatCode="010102")) OR
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE
> LctCatCode="180000")) AND
> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE
> LctCatCode="190000"))
>[/color]

I'm too tired to see if this will work, in that it involves
nesting IN statements, but

SELECT *
FROM tblLibrary
WHERE (LibID IN (SELECT LctLibID FROM tblLibCat WHERE
LctCatCode IN ("010101","010102","180000","190000"))

might do.

Bob Q

[color=blue]
> Trying to run this query, then scroll to the bottom record
> takes 5 minutes of locked pc, whereas the query using
> tblLibrary.Subject list is almost instant. I think this is
> the approach I will take, but I wonder whether anyone else
> currently uses this method. It is a problem which must occur
> in other types of databases and although
> http://www.mvps.org/access/queries/qry0016.htm uses a 'helper
> table' to find all of X having required Y, it does not address
> the OR issue. Perhaps someone has a database with a similar
> 3-table structure, eg people and skills. So you ask who knows
> (Access and VBA) OR (VB and SQL Server) Somebody out there
> must be solving these types of queries.
>
>
>[color=green]
>>
>> Stefan Kowalski wrote:[color=darkred]
>>> I recently posted a question which was answered by Allen
>>> Browne and[/color]
>> gave me[color=darkred]
>>> some tips to structure the tables. However, when it comes
>>> to[/color]
>> searching the[color=darkred]
>>> database, performance is unacceptably slow and I am now
>>> thinking of[/color]
>> doing[color=darkred]
>>> the unthinkable with my tables and wonder whether anyone has
>>> used (or[/color]
>> still[color=darkred]
>>> uses) this sort of approach.
>>>
>>> The database stores libraries and classifies the types of
>>> book they[/color]
>> keep. So[color=darkred]
>>> I have the classic 3-table design: tblLibrary, tblCategory,[/color]
>> tblLibCat, which[color=darkred]
>>> is the junction table showing LibID and CatCode.
>>> Additionally, the[/color]
>> CatCodes[color=darkred]
>>> are masked so that
>>> 050000 = Languages
>>> 050100 = European Languages
>>> 050101 = English
>>> 050102 = French
>>> 050103 = German
>>>
>>> This enables me to ask "who stocks books on any European
>>> language?"[/color]
>> as LIKE[color=darkred]
>>> "0501??". This was going OK until I tried to find out who
>>> stocks[/color]
>> (maths and[color=darkred]
>>> physics) or (computers and philosophy). This did not sound
>>> an overly-complex question to ask but as you may be able to
>>> guess from[/color]
>> the SQL,[color=darkred]
>>> my pc went on strike for the next ten minutes, protesting
>>> mainly over[/color]
>> the OR[color=darkred]
>>> clause. There are, by the way, about 25,000 libraries with
>>> about[/color]
>> 80,000[color=darkred]
>>> junction table records.
>>>
>>> SELECT * FROM tblLibrary WHERE
>>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode
>>> LIKE[/color]
>> "07????"))[color=darkred]
>>> AND
>>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode
>>> LIKE[/color]
>> "09????"))[color=darkred]
>>> OR
>>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode
>>> LIKE[/color]
>> "12????"))[color=darkred]
>>> AND
>>> (LibID IN (SELECT LctLibID FROM tblLibCat WHERE LctCatCode
>>> LIKE[/color]
>> "19????"))[color=darkred]
>>>
>>> So what I did (shock/horror) was to create a field[/color]
>> tblLibrary.LibSubjectList[color=darkred]
>>> which contained a comma-separated list of all the categories
>>> stocked[/color]
>> e.g.[color=darkred]
>>> ",070111,120100,120200,170602," which is just the sort of
>>> thing you[/color]
>> don't[color=darkred]
>>> normally do in a relational database, although I am keeping
>>> the[/color]
>> junction[color=darkred]
>>> table as being the master list so I can always re-sync the
>>> subject[/color]
>> list.[color=darkred]
>>> The query now becomes:
>>>
>>> SELECT * FROM tblLibrary WHERE
>>> (LibSubjectList LIKE "*,07????,*" AND LibSubjectList LIKE[/color]
>> "*,09????,*" )[color=darkred]
>>> OR
>>> (LibSubjectList LIKE "*,12????,*" AND LibSubjectList LIKE[/color]
>> "*,19????,*" )[color=darkred]
>>>
>>> and performance is lightning quick. In fact, I can't ask a
>>> question[/color]
>> about[color=darkred]
>>> which books are held which is not answered almost
>>> immediately. There[/color]
>> are[color=darkred]
>>> some drawbacks though:
>>> 1. The SubjectList is a 255-character indexed text field
>>> which means[/color]
>> the[color=darkred]
>>> maximum number of subjects per library is 36. That is, 6
>>> characters[/color]
>> per[color=darkred]
>>> subject plus a trailing comma multiplied by 36 then add a
>>> comma to[/color]
>> the[color=darkred]
>>> beginning of the list gives 253 characters. This limitation
>>> is[/color]
>> acceptable.[color=darkred]
>>> 2. I cannot enforce referential integrity. Although I can
>>> always[/color]
>> re-sync[color=darkred]
>>> the subject list with the junction table and the
>>> classifications[/color]
>> should stay[color=darkred]
>>> virtually unchanged throughout the life of the database, it
>>> is still something I need to be extremely cautious about.
>>> If the two lists[/color]
>> don't[color=darkred]
>>> match, all the searching goes wrong.
>>> 3. I have never used this approach before, but no other
>>> seems to[/color]
>> give[color=darkred]
>>> acceptable speed and I can't turn round and say (maths and
>>> physics)[/color]
>> or[color=darkred]
>>> (computers and philosophy) is too difficult a question!
>>>
>>> Any comments anyone?[/color]
>>[/color]
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.