When to switch from SWITCH() to JOIN
- From: "D Babin" <donald.babin@xxxxxxxxxxxxx>
- Date: Fri, 29 Jul 2005 11:42:13 -0500
This is a rather complex post in which I state my concerns about the
performance of SWITCH() and JOIN against MS Access
tables.
I'm writing a utility for use with MS Access databases, generating SQL
executed via ADO. First I create a series of fact
tables for most fields (but not every field). The objective is to recreate
the source tables with new values substituted for
most of the field's original values. These new values are located in the
fact tables.
<<< Note - if the following tables don't display properly, copy into
Notepad, which doesn't use proportional fonts >>>
MAIN
KEY COL1 COL2 COL3
--------------------------
1 GM 11/05/04 NO
2 SEARS 02/13/05 YES
3 SHELL 07/21/04 YES
4 BOEING 05/05/05 NO
5 IBM 04/12/05 YES
.... could be more than 1,000,000 rows
FACT1 |FACT2 |FACT3 |
ORG1 NEW1|ORG2 NEW2|ORG3 NEW3|
-----------|-------------|----------|
BOEING VAL1|07/21/04 VAL1|NO VAL1|
GM VAL2|11/05/04 VAL2|YES VAL2|
IBM VAL3|02/13/05 VAL3| |
SEARS VAL4|04/12/05 VAL4| |
SHELL VAL5|05/05/05 VAL5| |
....Could be perhaps 100 fact tables depending on the number of columns in
MAIN
Here is my solution - recreating the original table with the substituted
values through a series of code-generated queries:
1) First I recreate the table, i.e., CREATE TABLE ...
2) Next I populate ALL of the rows and populate SOME of the fields via
INSERT
INSERT INTO NEWMAIN (
COL1,
COL2,
COL3
SELECT
MAIN.KEY,
IIF(FACT1.NEW1 IS NULL,NULL,FACT1.NEW1) AS COL1,
IIF(FACT2.NEW2 IS NULL,NULL,FACT2.NEW2) AS COL2,
SWITCH(MAIN.COL3=,NULL,NULL,MAIN.COL3=,"NO",VAL1,MAIN.COL3=,"YES",VAL2) AS
COL3
FROM (MAIN
LEFT JOIN FACT1 ON MAIN.COL1 = FACT1.ORG1)
LEFT JOIN FACT2 ON MAIN.COL2 = FACT2.ORG2;
3) Finally, if all columns were not populated, I finish populating them with
one or more UPDATE queries similar to the INSERT
query above.
Now here are my performance questions:
1) Clearly SWITCH() is superior to JOIN for tiny value sets such as 2,3,4 or
5 values. However, when does SWITCH() become
less efficient than JOIN? Will SWITCH() execute faster than JOIN for 100
values? And if I have multiple SWITCH() functions
containing 100 values each, the SQL statement will become bloated fast -
which begs the next question.
2) Does Microsoft Jet 4.0 OLEDB impose a limitation on SQL statement size -
and, if so, what is this size limitation?
3) Regarding the JOINS, is there a rule-of-thumb regarding the number of
acceptable JOINS before performance takes a nose
dive?
4) Are there performance benefits by arranging the joined tables in a
specific sequence, for example:
LEFT JOIN FACT7 ... (FACT7 contains 1,000 records)
LEFT JOIN FACT12 ... (FACT12 contains 10,000 records)
LEFT JOIN FACT9 ... (FACT9 contains 100,000 records)
LEFT JOIN FACT20 ... (FACT20 contains 1,000,000 records)
vs.
LEFT JOIN FACT20 ... (FACT20 contains 1,000,000 records)
LEFT JOIN FACT9 ... (FACT9 contains 100,000 records)
LEFT JOIN FACT12 ... (FACT12 contains 10,000 records)
LEFT JOIN FACT7 ... (FACT7 contains 1,000 records)
5) Finally, given the SQL language limitations imposed by Microsoft Access
(no user-defined functions, etc.), is there a
radically better approach than the general approach that I have outlined
above?
Best regards,
D Babin
.
- Prev by Date: Re: ADO return varchar string with space trimmed
- Next by Date: Re: ADO return varchar string with space trimmed
- Previous by thread: ADO return varchar string with space trimmed
- Next by thread: Slow Stored Procedure when run via ado, fast from query analyzer
- Index(es):
Relevant Pages
|
