By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,098 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,098 IT Pros & Developers. It's quick & easy.

Doing the unthinkable with my tables

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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:
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?


Nov 13 '05 #2

P: n/a

"Alex" <al*********@rocketmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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.
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.

Stefan Kowalski wrote:
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?

Nov 13 '05 #3

P: n/a
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:
"Alex" <al*********@rocketmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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.
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.

Stefan Kowalski wrote:
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?


Nov 13 '05 #4

P: n/a
"Stefan Kowalski" <a@b.com> wrote in
news:ct**********@sparta.btinternet.com:
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"))

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

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.

Stefan Kowalski wrote:
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?



--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.