Query Timeout issue

  • Thread starter Thread starter Tony Doyle
  • Start date Start date
T

Tony Doyle

All,

I have a table with 15 lookup values in. The lookup values all related to
the same table, therefore I have 15 Left Joins (tried it with Inner, no
difference). There are 1590 records in the main table, if I change my select
so that it brings back all records where caseid<=1590, it takes approx 2
secs. If I remove the where clause, it takes over 30 minutes (its still
running).

Can anyone shed anylight on this...

Td
 
Here you go...

Basicallym, I have stored all lookup values in one table, and then in my
main table we only have to lookup the data in one table instead of one table
for evey drop down list.

Td

SELECT tblBenefitCaseDetailsInformation.lngCaseDetailsInformationID,
tblBenefitCaseDetailsInformation.lngBenefitCaseId,
tblBenefitCase.sFirstName, tblBenefitCase.sLastName,
tblBenefitCase.sNINumber, tblLookupPickListValues.sValues AS TechPreviewID,
tblBenefitCaseDetailsInformation.dteTechPreviewCompletionDate,
tblBenefitCaseDetailsInformation.sCustAddress,
tblLookupPickListValues_1.sValues AS CustAddrCorrect,
tblLookupPickListValues_2.sValues AS Abandoned,
tblLookupPickListValues_3.sValues AS AccResult,
tblLookupPickListValues_4.sValues AS CommonSample,
tblBenefitCaseDetailsInformation.dteMonday,
tblBenefitCaseDetailsInformation.lngWeekID,
tblLookupPickListValues_5.sValues AS ClientGroup,
tblLookupPickListValues_6.sValues AS StatSymbol,
tblLookupPickListValues.sValues AS CaseHandling,
tblBenefitCaseDetailsInformation.lngPeriodID,
tblBenefitCaseDetailsInformation.dteCurrentClaim,
tblBenefitCaseDetailsInformation.dteEntStartDate,
tblLookupPickListValues_8.sValues AS MethodOfPayment,
tblLookupPickListValues_9.sValues AS MaritalStatus,
tblLookupPickListValues_10.sValues AS NoOfDep,
tblLookupPickListValues_11.sValues AS AgeOfYoung,
tblLookupPickListValues_12.sValues AS Housing,
tblLookupPickListValues_13.sValues AS OtherBenefit,
tblLookupPickListValues_14.sValues AS OtherIncome,
tblLookupPickListValues_15.sValues AS RD18NIRS,
tblLookupPickListValues_16.sValues AS CSAInvolvement,
tblLookupPickListValues_17.sValues AS AbandonedPoint,
tblLookupPickListValues_20.sValues AS DepInvolvement,
tblLookupPickListValues_22.sValues AS IBCategory,
tblLookupPickListValues_23.sValues AS WFI,
tblLookupPickListValues_21.sValues AS 5VC,
tblLookupPickListValues_25.sValues AS CurrActivityCode,
IIf([lngPostalSigner]=1,"Yes","No") AS PostalSigner,
IIf([lngUnderEntJSA]=1,"Yes","No") AS UnderEntJSA,
IIf([lngHardNPay]=1,"Yes","No") AS HardNPay,
IIf([lngSancSuspDisa]=1,"Yes","No") AS SancSuspDisa,
IIf([lngSDP]=1,"Yes","No") AS SDP, IIf([lngCapital]=1,"Yes","No") AS
Capital, IIf([lngClericalComp]=1,"Yes","No") AS ClericalComp,
IIf([lngNewTaxCredit]=1,"Yes","No") AS NewTaxCredit,
IIf([lngJointClaim]=1,"Yes","No") AS JointClaim,
IIf([lngFinalEarnings]=1,"Yes","No") AS FinalEarnings,
IIf([lngEmpZone]=1,"Yes","No") AS EmpZone,
IIf([lngCarersAllowance]=1,"Yes","No") AS CarersAllowance,
IIf([lngTransitionAddition]=1,"Yes","No") AS TransitiionAddition,
IIf([lngCaseOver14Months]=1,"Yes","No") AS CaseOver14Months,
IIf([lngWFIInterest]=2,"Yes","No") AS WFIInterest,
IIf([lngRD18NIRS]=1,"Yes","No") AS RD18NIRSYesNo,
tblBenefitCaseDetailsInformation.dteRecordEntered
FROM tblBenefitCase INNER JOIN
(((((((((((((((((((((((tblBenefitCaseDetailsInformation LEFT JOIN
tblLookupPickListValues ON tblBenefitCaseDetailsInformation.lngTechPreviewID
= tblLookupPickListValues.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_1 ON
tblBenefitCaseDetailsInformation.lngCustAddressCorrectID =
tblLookupPickListValues_1.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_2 ON
tblBenefitCaseDetailsInformation.lngAbandonedYesNoID =
tblLookupPickListValues_2.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_3 ON
tblBenefitCaseDetailsInformation.lngAccResultID =
tblLookupPickListValues_3.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_4 ON
tblBenefitCaseDetailsInformation.lngCommonSampleID =
tblLookupPickListValues_4.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_5 ON
tblBenefitCaseDetailsInformation.lngClientGroupID =
tblLookupPickListValues_5.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_6 ON
tblBenefitCaseDetailsInformation.lngStatSymbolID =
tblLookupPickListValues_6.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_7 ON
tblBenefitCaseDetailsInformation.lngCaseHandlingID =
tblLookupPickListValues_7.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_8 ON
tblBenefitCaseDetailsInformation.lngMethodOfPaymentID =
tblLookupPickListValues_8.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_9 ON
tblBenefitCaseDetailsInformation.lngMaritalStatusID =
tblLookupPickListValues_9.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_10 ON
tblBenefitCaseDetailsInformation.lngNoOfDepID =
tblLookupPickListValues_10.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_11 ON
tblBenefitCaseDetailsInformation.lngAgeOfYoungID =
tblLookupPickListValues_11.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_12 ON
tblBenefitCaseDetailsInformation.lngHousingID =
tblLookupPickListValues_12.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_13 ON
tblBenefitCaseDetailsInformation.lngOtherBenefitsID =
tblLookupPickListValues_13.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_14 ON
tblBenefitCaseDetailsInformation.lngOtherIncomeID =
tblLookupPickListValues_14.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_15 ON
tblBenefitCaseDetailsInformation.lngRd18NirsID =
tblLookupPickListValues_15.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_16 ON
tblBenefitCaseDetailsInformation.lngCSAInvolvementID =
tblLookupPickListValues_16.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_17 ON
tblBenefitCaseDetailsInformation.lngAbandonedPointID =
tblLookupPickListValues_17.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_20 ON
tblBenefitCaseDetailsInformation.lngDepInvolvmentCodeID =
tblLookupPickListValues_20.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_22 ON
tblBenefitCaseDetailsInformation.lngIBCategorySDAID =
tblLookupPickListValues_22.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_23 ON
tblBenefitCaseDetailsInformation.lngWFIOffErrorID =
tblLookupPickListValues_23.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_21 ON
tblBenefitCaseDetailsInformation.lng5VCID =
tblLookupPickListValues_21.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_25 ON
tblBenefitCaseDetailsInformation.lngCurrActivityCodeID =
tblLookupPickListValues_25.lngPickListValuesID) ON
tblBenefitCase.lngBenefitCaseId =
tblBenefitCaseDetailsInformation.lngBenefitCaseId;
 
I am lost. I have never seen anyone build a query like this. The closest
would to use what is your tblLookupPickListValues in a one-to-many relations
with your tblBenefitCaseDetailsInformation but I do not see what looks like a
field name reflecting what you put in the list.

Tony Doyle said:
Here you go...

Basicallym, I have stored all lookup values in one table, and then in my
main table we only have to lookup the data in one table instead of one table
for evey drop down list.

Td

SELECT tblBenefitCaseDetailsInformation.lngCaseDetailsInformationID,
tblBenefitCaseDetailsInformation.lngBenefitCaseId,
tblBenefitCase.sFirstName, tblBenefitCase.sLastName,
tblBenefitCase.sNINumber, tblLookupPickListValues.sValues AS TechPreviewID,
tblBenefitCaseDetailsInformation.dteTechPreviewCompletionDate,
tblBenefitCaseDetailsInformation.sCustAddress,
tblLookupPickListValues_1.sValues AS CustAddrCorrect,
tblLookupPickListValues_2.sValues AS Abandoned,
tblLookupPickListValues_3.sValues AS AccResult,
tblLookupPickListValues_4.sValues AS CommonSample,
tblBenefitCaseDetailsInformation.dteMonday,
tblBenefitCaseDetailsInformation.lngWeekID,
tblLookupPickListValues_5.sValues AS ClientGroup,
tblLookupPickListValues_6.sValues AS StatSymbol,
tblLookupPickListValues.sValues AS CaseHandling,
tblBenefitCaseDetailsInformation.lngPeriodID,
tblBenefitCaseDetailsInformation.dteCurrentClaim,
tblBenefitCaseDetailsInformation.dteEntStartDate,
tblLookupPickListValues_8.sValues AS MethodOfPayment,
tblLookupPickListValues_9.sValues AS MaritalStatus,
tblLookupPickListValues_10.sValues AS NoOfDep,
tblLookupPickListValues_11.sValues AS AgeOfYoung,
tblLookupPickListValues_12.sValues AS Housing,
tblLookupPickListValues_13.sValues AS OtherBenefit,
tblLookupPickListValues_14.sValues AS OtherIncome,
tblLookupPickListValues_15.sValues AS RD18NIRS,
tblLookupPickListValues_16.sValues AS CSAInvolvement,
tblLookupPickListValues_17.sValues AS AbandonedPoint,
tblLookupPickListValues_20.sValues AS DepInvolvement,
tblLookupPickListValues_22.sValues AS IBCategory,
tblLookupPickListValues_23.sValues AS WFI,
tblLookupPickListValues_21.sValues AS 5VC,
tblLookupPickListValues_25.sValues AS CurrActivityCode,
IIf([lngPostalSigner]=1,"Yes","No") AS PostalSigner,
IIf([lngUnderEntJSA]=1,"Yes","No") AS UnderEntJSA,
IIf([lngHardNPay]=1,"Yes","No") AS HardNPay,
IIf([lngSancSuspDisa]=1,"Yes","No") AS SancSuspDisa,
IIf([lngSDP]=1,"Yes","No") AS SDP, IIf([lngCapital]=1,"Yes","No") AS
Capital, IIf([lngClericalComp]=1,"Yes","No") AS ClericalComp,
IIf([lngNewTaxCredit]=1,"Yes","No") AS NewTaxCredit,
IIf([lngJointClaim]=1,"Yes","No") AS JointClaim,
IIf([lngFinalEarnings]=1,"Yes","No") AS FinalEarnings,
IIf([lngEmpZone]=1,"Yes","No") AS EmpZone,
IIf([lngCarersAllowance]=1,"Yes","No") AS CarersAllowance,
IIf([lngTransitionAddition]=1,"Yes","No") AS TransitiionAddition,
IIf([lngCaseOver14Months]=1,"Yes","No") AS CaseOver14Months,
IIf([lngWFIInterest]=2,"Yes","No") AS WFIInterest,
IIf([lngRD18NIRS]=1,"Yes","No") AS RD18NIRSYesNo,
tblBenefitCaseDetailsInformation.dteRecordEntered
FROM tblBenefitCase INNER JOIN
(((((((((((((((((((((((tblBenefitCaseDetailsInformation LEFT JOIN
tblLookupPickListValues ON tblBenefitCaseDetailsInformation.lngTechPreviewID
= tblLookupPickListValues.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_1 ON
tblBenefitCaseDetailsInformation.lngCustAddressCorrectID =
tblLookupPickListValues_1.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_2 ON
tblBenefitCaseDetailsInformation.lngAbandonedYesNoID =
tblLookupPickListValues_2.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_3 ON
tblBenefitCaseDetailsInformation.lngAccResultID =
tblLookupPickListValues_3.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_4 ON
tblBenefitCaseDetailsInformation.lngCommonSampleID =
tblLookupPickListValues_4.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_5 ON
tblBenefitCaseDetailsInformation.lngClientGroupID =
tblLookupPickListValues_5.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_6 ON
tblBenefitCaseDetailsInformation.lngStatSymbolID =
tblLookupPickListValues_6.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_7 ON
tblBenefitCaseDetailsInformation.lngCaseHandlingID =
tblLookupPickListValues_7.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_8 ON
tblBenefitCaseDetailsInformation.lngMethodOfPaymentID =
tblLookupPickListValues_8.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_9 ON
tblBenefitCaseDetailsInformation.lngMaritalStatusID =
tblLookupPickListValues_9.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_10 ON
tblBenefitCaseDetailsInformation.lngNoOfDepID =
tblLookupPickListValues_10.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_11 ON
tblBenefitCaseDetailsInformation.lngAgeOfYoungID =
tblLookupPickListValues_11.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_12 ON
tblBenefitCaseDetailsInformation.lngHousingID =
tblLookupPickListValues_12.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_13 ON
tblBenefitCaseDetailsInformation.lngOtherBenefitsID =
tblLookupPickListValues_13.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_14 ON
tblBenefitCaseDetailsInformation.lngOtherIncomeID =
tblLookupPickListValues_14.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_15 ON
tblBenefitCaseDetailsInformation.lngRd18NirsID =
tblLookupPickListValues_15.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_16 ON
tblBenefitCaseDetailsInformation.lngCSAInvolvementID =
tblLookupPickListValues_16.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_17 ON
tblBenefitCaseDetailsInformation.lngAbandonedPointID =
tblLookupPickListValues_17.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_20 ON
tblBenefitCaseDetailsInformation.lngDepInvolvmentCodeID =
tblLookupPickListValues_20.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_22 ON
tblBenefitCaseDetailsInformation.lngIBCategorySDAID =
tblLookupPickListValues_22.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_23 ON
tblBenefitCaseDetailsInformation.lngWFIOffErrorID =
tblLookupPickListValues_23.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_21 ON
tblBenefitCaseDetailsInformation.lng5VCID =
tblLookupPickListValues_21.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_25 ON
tblBenefitCaseDetailsInformation.lngCurrActivityCodeID =
tblLookupPickListValues_25.lngPickListValuesID) ON
tblBenefitCase.lngBenefitCaseId =
tblBenefitCaseDetailsInformation.lngBenefitCaseId;

KARL DEWEY said:
!5 joins? Post your SQL.
Click to expand...
Click to expand...
 
Basically it's this...

We have a website that has numerous "drop down lists" on it. Instead of
creating a table for each one, we created one huge table, and then assigned
the name of each on inside this table with the values. When you save the
data back to the master table, you can just use the "index" of the
PickListValues table...

I just need to understand why when I use a WHERE statement inside my SQL it
works, and when I don't it doesn't.

Td

KARL DEWEY said:
I am lost. I have never seen anyone build a query like this. The closest
would to use what is your tblLookupPickListValues in a one-to-many
relations
with your tblBenefitCaseDetailsInformation but I do not see what looks
like a
field name reflecting what you put in the list.

Tony Doyle said:
Here you go...

Basicallym, I have stored all lookup values in one table, and then in my
main table we only have to lookup the data in one table instead of one
table
for evey drop down list.

Td

SELECT tblBenefitCaseDetailsInformation.lngCaseDetailsInformationID,
tblBenefitCaseDetailsInformation.lngBenefitCaseId,
tblBenefitCase.sFirstName, tblBenefitCase.sLastName,
tblBenefitCase.sNINumber, tblLookupPickListValues.sValues AS
TechPreviewID,
tblBenefitCaseDetailsInformation.dteTechPreviewCompletionDate,
tblBenefitCaseDetailsInformation.sCustAddress,
tblLookupPickListValues_1.sValues AS CustAddrCorrect,
tblLookupPickListValues_2.sValues AS Abandoned,
tblLookupPickListValues_3.sValues AS AccResult,
tblLookupPickListValues_4.sValues AS CommonSample,
tblBenefitCaseDetailsInformation.dteMonday,
tblBenefitCaseDetailsInformation.lngWeekID,
tblLookupPickListValues_5.sValues AS ClientGroup,
tblLookupPickListValues_6.sValues AS StatSymbol,
tblLookupPickListValues.sValues AS CaseHandling,
tblBenefitCaseDetailsInformation.lngPeriodID,
tblBenefitCaseDetailsInformation.dteCurrentClaim,
tblBenefitCaseDetailsInformation.dteEntStartDate,
tblLookupPickListValues_8.sValues AS MethodOfPayment,
tblLookupPickListValues_9.sValues AS MaritalStatus,
tblLookupPickListValues_10.sValues AS NoOfDep,
tblLookupPickListValues_11.sValues AS AgeOfYoung,
tblLookupPickListValues_12.sValues AS Housing,
tblLookupPickListValues_13.sValues AS OtherBenefit,
tblLookupPickListValues_14.sValues AS OtherIncome,
tblLookupPickListValues_15.sValues AS RD18NIRS,
tblLookupPickListValues_16.sValues AS CSAInvolvement,
tblLookupPickListValues_17.sValues AS AbandonedPoint,
tblLookupPickListValues_20.sValues AS DepInvolvement,
tblLookupPickListValues_22.sValues AS IBCategory,
tblLookupPickListValues_23.sValues AS WFI,
tblLookupPickListValues_21.sValues AS 5VC,
tblLookupPickListValues_25.sValues AS CurrActivityCode,
IIf([lngPostalSigner]=1,"Yes","No") AS PostalSigner,
IIf([lngUnderEntJSA]=1,"Yes","No") AS UnderEntJSA,
IIf([lngHardNPay]=1,"Yes","No") AS HardNPay,
IIf([lngSancSuspDisa]=1,"Yes","No") AS SancSuspDisa,
IIf([lngSDP]=1,"Yes","No") AS SDP, IIf([lngCapital]=1,"Yes","No") AS
Capital, IIf([lngClericalComp]=1,"Yes","No") AS ClericalComp,
IIf([lngNewTaxCredit]=1,"Yes","No") AS NewTaxCredit,
IIf([lngJointClaim]=1,"Yes","No") AS JointClaim,
IIf([lngFinalEarnings]=1,"Yes","No") AS FinalEarnings,
IIf([lngEmpZone]=1,"Yes","No") AS EmpZone,
IIf([lngCarersAllowance]=1,"Yes","No") AS CarersAllowance,
IIf([lngTransitionAddition]=1,"Yes","No") AS TransitiionAddition,
IIf([lngCaseOver14Months]=1,"Yes","No") AS CaseOver14Months,
IIf([lngWFIInterest]=2,"Yes","No") AS WFIInterest,
IIf([lngRD18NIRS]=1,"Yes","No") AS RD18NIRSYesNo,
tblBenefitCaseDetailsInformation.dteRecordEntered
FROM tblBenefitCase INNER JOIN
(((((((((((((((((((((((tblBenefitCaseDetailsInformation LEFT JOIN
tblLookupPickListValues ON
tblBenefitCaseDetailsInformation.lngTechPreviewID
= tblLookupPickListValues.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_1 ON
tblBenefitCaseDetailsInformation.lngCustAddressCorrectID =
tblLookupPickListValues_1.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_2 ON
tblBenefitCaseDetailsInformation.lngAbandonedYesNoID =
tblLookupPickListValues_2.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_3 ON
tblBenefitCaseDetailsInformation.lngAccResultID =
tblLookupPickListValues_3.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_4 ON
tblBenefitCaseDetailsInformation.lngCommonSampleID =
tblLookupPickListValues_4.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_5 ON
tblBenefitCaseDetailsInformation.lngClientGroupID =
tblLookupPickListValues_5.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_6 ON
tblBenefitCaseDetailsInformation.lngStatSymbolID =
tblLookupPickListValues_6.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_7 ON
tblBenefitCaseDetailsInformation.lngCaseHandlingID =
tblLookupPickListValues_7.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_8 ON
tblBenefitCaseDetailsInformation.lngMethodOfPaymentID =
tblLookupPickListValues_8.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_9 ON
tblBenefitCaseDetailsInformation.lngMaritalStatusID =
tblLookupPickListValues_9.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_10 ON
tblBenefitCaseDetailsInformation.lngNoOfDepID =
tblLookupPickListValues_10.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_11 ON
tblBenefitCaseDetailsInformation.lngAgeOfYoungID =
tblLookupPickListValues_11.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_12 ON
tblBenefitCaseDetailsInformation.lngHousingID =
tblLookupPickListValues_12.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_13 ON
tblBenefitCaseDetailsInformation.lngOtherBenefitsID =
tblLookupPickListValues_13.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_14 ON
tblBenefitCaseDetailsInformation.lngOtherIncomeID =
tblLookupPickListValues_14.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_15 ON
tblBenefitCaseDetailsInformation.lngRd18NirsID =
tblLookupPickListValues_15.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_16 ON
tblBenefitCaseDetailsInformation.lngCSAInvolvementID =
tblLookupPickListValues_16.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_17 ON
tblBenefitCaseDetailsInformation.lngAbandonedPointID =
tblLookupPickListValues_17.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_20 ON
tblBenefitCaseDetailsInformation.lngDepInvolvmentCodeID =
tblLookupPickListValues_20.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_22 ON
tblBenefitCaseDetailsInformation.lngIBCategorySDAID =
tblLookupPickListValues_22.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_23 ON
tblBenefitCaseDetailsInformation.lngWFIOffErrorID =
tblLookupPickListValues_23.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_21 ON
tblBenefitCaseDetailsInformation.lng5VCID =
tblLookupPickListValues_21.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_25 ON
tblBenefitCaseDetailsInformation.lngCurrActivityCodeID =
tblLookupPickListValues_25.lngPickListValuesID) ON
tblBenefitCase.lngBenefitCaseId =
tblBenefitCaseDetailsInformation.lngBenefitCaseId;

KARL DEWEY said:
!5 joins? Post your SQL.

:

All,

I have a table with 15 lookup values in. The lookup values all related
to
the same table, therefore I have 15 Left Joins (tried it with Inner,
no
difference). There are 1590 records in the main table, if I change my
select
so that it brings back all records where caseid<=1590, it takes approx
2
secs. If I remove the where clause, it takes over 30 minutes (its
still
running).

Can anyone shed anylight on this...

Td
Click to expand...
Click to expand...
Click to expand...
 
I would have used two field in the BIG lookup table. First field to contain
the lower level lookup table name and the second to contain the data.

Tony Doyle said:
Basically it's this...

We have a website that has numerous "drop down lists" on it. Instead of
creating a table for each one, we created one huge table, and then assigned
the name of each on inside this table with the values. When you save the
data back to the master table, you can just use the "index" of the
PickListValues table...

I just need to understand why when I use a WHERE statement inside my SQL it
works, and when I don't it doesn't.

Td

KARL DEWEY said:
I am lost. I have never seen anyone build a query like this. The closest
would to use what is your tblLookupPickListValues in a one-to-many
relations
with your tblBenefitCaseDetailsInformation but I do not see what looks
like a
field name reflecting what you put in the list.

Tony Doyle said:
Here you go...

Basicallym, I have stored all lookup values in one table, and then in my
main table we only have to lookup the data in one table instead of one
table
for evey drop down list.

Td

SELECT tblBenefitCaseDetailsInformation.lngCaseDetailsInformationID,
tblBenefitCaseDetailsInformation.lngBenefitCaseId,
tblBenefitCase.sFirstName, tblBenefitCase.sLastName,
tblBenefitCase.sNINumber, tblLookupPickListValues.sValues AS
TechPreviewID,
tblBenefitCaseDetailsInformation.dteTechPreviewCompletionDate,
tblBenefitCaseDetailsInformation.sCustAddress,
tblLookupPickListValues_1.sValues AS CustAddrCorrect,
tblLookupPickListValues_2.sValues AS Abandoned,
tblLookupPickListValues_3.sValues AS AccResult,
tblLookupPickListValues_4.sValues AS CommonSample,
tblBenefitCaseDetailsInformation.dteMonday,
tblBenefitCaseDetailsInformation.lngWeekID,
tblLookupPickListValues_5.sValues AS ClientGroup,
tblLookupPickListValues_6.sValues AS StatSymbol,
tblLookupPickListValues.sValues AS CaseHandling,
tblBenefitCaseDetailsInformation.lngPeriodID,
tblBenefitCaseDetailsInformation.dteCurrentClaim,
tblBenefitCaseDetailsInformation.dteEntStartDate,
tblLookupPickListValues_8.sValues AS MethodOfPayment,
tblLookupPickListValues_9.sValues AS MaritalStatus,
tblLookupPickListValues_10.sValues AS NoOfDep,
tblLookupPickListValues_11.sValues AS AgeOfYoung,
tblLookupPickListValues_12.sValues AS Housing,
tblLookupPickListValues_13.sValues AS OtherBenefit,
tblLookupPickListValues_14.sValues AS OtherIncome,
tblLookupPickListValues_15.sValues AS RD18NIRS,
tblLookupPickListValues_16.sValues AS CSAInvolvement,
tblLookupPickListValues_17.sValues AS AbandonedPoint,
tblLookupPickListValues_20.sValues AS DepInvolvement,
tblLookupPickListValues_22.sValues AS IBCategory,
tblLookupPickListValues_23.sValues AS WFI,
tblLookupPickListValues_21.sValues AS 5VC,
tblLookupPickListValues_25.sValues AS CurrActivityCode,
IIf([lngPostalSigner]=1,"Yes","No") AS PostalSigner,
IIf([lngUnderEntJSA]=1,"Yes","No") AS UnderEntJSA,
IIf([lngHardNPay]=1,"Yes","No") AS HardNPay,
IIf([lngSancSuspDisa]=1,"Yes","No") AS SancSuspDisa,
IIf([lngSDP]=1,"Yes","No") AS SDP, IIf([lngCapital]=1,"Yes","No") AS
Capital, IIf([lngClericalComp]=1,"Yes","No") AS ClericalComp,
IIf([lngNewTaxCredit]=1,"Yes","No") AS NewTaxCredit,
IIf([lngJointClaim]=1,"Yes","No") AS JointClaim,
IIf([lngFinalEarnings]=1,"Yes","No") AS FinalEarnings,
IIf([lngEmpZone]=1,"Yes","No") AS EmpZone,
IIf([lngCarersAllowance]=1,"Yes","No") AS CarersAllowance,
IIf([lngTransitionAddition]=1,"Yes","No") AS TransitiionAddition,
IIf([lngCaseOver14Months]=1,"Yes","No") AS CaseOver14Months,
IIf([lngWFIInterest]=2,"Yes","No") AS WFIInterest,
IIf([lngRD18NIRS]=1,"Yes","No") AS RD18NIRSYesNo,
tblBenefitCaseDetailsInformation.dteRecordEntered
FROM tblBenefitCase INNER JOIN
(((((((((((((((((((((((tblBenefitCaseDetailsInformation LEFT JOIN
tblLookupPickListValues ON
tblBenefitCaseDetailsInformation.lngTechPreviewID
= tblLookupPickListValues.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_1 ON
tblBenefitCaseDetailsInformation.lngCustAddressCorrectID =
tblLookupPickListValues_1.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_2 ON
tblBenefitCaseDetailsInformation.lngAbandonedYesNoID =
tblLookupPickListValues_2.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_3 ON
tblBenefitCaseDetailsInformation.lngAccResultID =
tblLookupPickListValues_3.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_4 ON
tblBenefitCaseDetailsInformation.lngCommonSampleID =
tblLookupPickListValues_4.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_5 ON
tblBenefitCaseDetailsInformation.lngClientGroupID =
tblLookupPickListValues_5.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_6 ON
tblBenefitCaseDetailsInformation.lngStatSymbolID =
tblLookupPickListValues_6.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_7 ON
tblBenefitCaseDetailsInformation.lngCaseHandlingID =
tblLookupPickListValues_7.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_8 ON
tblBenefitCaseDetailsInformation.lngMethodOfPaymentID =
tblLookupPickListValues_8.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_9 ON
tblBenefitCaseDetailsInformation.lngMaritalStatusID =
tblLookupPickListValues_9.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_10 ON
tblBenefitCaseDetailsInformation.lngNoOfDepID =
tblLookupPickListValues_10.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_11 ON
tblBenefitCaseDetailsInformation.lngAgeOfYoungID =
tblLookupPickListValues_11.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_12 ON
tblBenefitCaseDetailsInformation.lngHousingID =
tblLookupPickListValues_12.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_13 ON
tblBenefitCaseDetailsInformation.lngOtherBenefitsID =
tblLookupPickListValues_13.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_14 ON
tblBenefitCaseDetailsInformation.lngOtherIncomeID =
tblLookupPickListValues_14.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_15 ON
tblBenefitCaseDetailsInformation.lngRd18NirsID =
tblLookupPickListValues_15.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_16 ON
tblBenefitCaseDetailsInformation.lngCSAInvolvementID =
tblLookupPickListValues_16.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_17 ON
tblBenefitCaseDetailsInformation.lngAbandonedPointID =
tblLookupPickListValues_17.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_20 ON
tblBenefitCaseDetailsInformation.lngDepInvolvmentCodeID =
tblLookupPickListValues_20.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_22 ON
tblBenefitCaseDetailsInformation.lngIBCategorySDAID =
tblLookupPickListValues_22.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_23 ON
tblBenefitCaseDetailsInformation.lngWFIOffErrorID =
tblLookupPickListValues_23.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_21 ON
tblBenefitCaseDetailsInformation.lng5VCID =
tblLookupPickListValues_21.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_25 ON
tblBenefitCaseDetailsInformation.lngCurrActivityCodeID =
tblLookupPickListValues_25.lngPickListValuesID) ON
tblBenefitCase.lngBenefitCaseId =
tblBenefitCaseDetailsInformation.lngBenefitCaseId;

!5 joins? Post your SQL.

:

All,

I have a table with 15 lookup values in. The lookup values all related
to
the same table, therefore I have 15 Left Joins (tried it with Inner,
no
difference). There are 1590 records in the main table, if I change my
select
so that it brings back all records where caseid<=1590, it takes approx
2
secs. If I remove the where clause, it takes over 30 minutes (its
still
running).

Can anyone shed anylight on this...

Td
Click to expand...
Click to expand...
Click to expand...
 
Dear Tony:

Perhaps you have built this lsrge, complex query all at once, then attempted
to run it.

I suggest you not do this. Add one table at a time to the query and run it.
If you have a problem with performance, or anything else, you'll know where
to look for the problem. If you create the whole thing before you try any
of it, this is the kind of thing that can result.

You may want to look for corruption. What happens if you perform a Compact
and Repair?

Tom Ellison
 
Karl,

That is what in essence we have, it still doesn't answer my problem though.

Td
KARL DEWEY said:
I would have used two field in the BIG lookup table. First field to
contain
the lower level lookup table name and the second to contain the data.

Tony Doyle said:
Basically it's this...

We have a website that has numerous "drop down lists" on it. Instead of
creating a table for each one, we created one huge table, and then
assigned
the name of each on inside this table with the values. When you save the
data back to the master table, you can just use the "index" of the
PickListValues table...

I just need to understand why when I use a WHERE statement inside my SQL
it
works, and when I don't it doesn't.

Td

KARL DEWEY said:
I am lost. I have never seen anyone build a query like this. The
closest
would to use what is your tblLookupPickListValues in a one-to-many
relations
with your tblBenefitCaseDetailsInformation but I do not see what looks
like a
field name reflecting what you put in the list.

:

Here you go...

Basicallym, I have stored all lookup values in one table, and then in
my
main table we only have to lookup the data in one table instead of one
table
for evey drop down list.

Td

SELECT tblBenefitCaseDetailsInformation.lngCaseDetailsInformationID,
tblBenefitCaseDetailsInformation.lngBenefitCaseId,
tblBenefitCase.sFirstName, tblBenefitCase.sLastName,
tblBenefitCase.sNINumber, tblLookupPickListValues.sValues AS
TechPreviewID,
tblBenefitCaseDetailsInformation.dteTechPreviewCompletionDate,
tblBenefitCaseDetailsInformation.sCustAddress,
tblLookupPickListValues_1.sValues AS CustAddrCorrect,
tblLookupPickListValues_2.sValues AS Abandoned,
tblLookupPickListValues_3.sValues AS AccResult,
tblLookupPickListValues_4.sValues AS CommonSample,
tblBenefitCaseDetailsInformation.dteMonday,
tblBenefitCaseDetailsInformation.lngWeekID,
tblLookupPickListValues_5.sValues AS ClientGroup,
tblLookupPickListValues_6.sValues AS StatSymbol,
tblLookupPickListValues.sValues AS CaseHandling,
tblBenefitCaseDetailsInformation.lngPeriodID,
tblBenefitCaseDetailsInformation.dteCurrentClaim,
tblBenefitCaseDetailsInformation.dteEntStartDate,
tblLookupPickListValues_8.sValues AS MethodOfPayment,
tblLookupPickListValues_9.sValues AS MaritalStatus,
tblLookupPickListValues_10.sValues AS NoOfDep,
tblLookupPickListValues_11.sValues AS AgeOfYoung,
tblLookupPickListValues_12.sValues AS Housing,
tblLookupPickListValues_13.sValues AS OtherBenefit,
tblLookupPickListValues_14.sValues AS OtherIncome,
tblLookupPickListValues_15.sValues AS RD18NIRS,
tblLookupPickListValues_16.sValues AS CSAInvolvement,
tblLookupPickListValues_17.sValues AS AbandonedPoint,
tblLookupPickListValues_20.sValues AS DepInvolvement,
tblLookupPickListValues_22.sValues AS IBCategory,
tblLookupPickListValues_23.sValues AS WFI,
tblLookupPickListValues_21.sValues AS 5VC,
tblLookupPickListValues_25.sValues AS CurrActivityCode,
IIf([lngPostalSigner]=1,"Yes","No") AS PostalSigner,
IIf([lngUnderEntJSA]=1,"Yes","No") AS UnderEntJSA,
IIf([lngHardNPay]=1,"Yes","No") AS HardNPay,
IIf([lngSancSuspDisa]=1,"Yes","No") AS SancSuspDisa,
IIf([lngSDP]=1,"Yes","No") AS SDP, IIf([lngCapital]=1,"Yes","No") AS
Capital, IIf([lngClericalComp]=1,"Yes","No") AS ClericalComp,
IIf([lngNewTaxCredit]=1,"Yes","No") AS NewTaxCredit,
IIf([lngJointClaim]=1,"Yes","No") AS JointClaim,
IIf([lngFinalEarnings]=1,"Yes","No") AS FinalEarnings,
IIf([lngEmpZone]=1,"Yes","No") AS EmpZone,
IIf([lngCarersAllowance]=1,"Yes","No") AS CarersAllowance,
IIf([lngTransitionAddition]=1,"Yes","No") AS TransitiionAddition,
IIf([lngCaseOver14Months]=1,"Yes","No") AS CaseOver14Months,
IIf([lngWFIInterest]=2,"Yes","No") AS WFIInterest,
IIf([lngRD18NIRS]=1,"Yes","No") AS RD18NIRSYesNo,
tblBenefitCaseDetailsInformation.dteRecordEntered
FROM tblBenefitCase INNER JOIN
(((((((((((((((((((((((tblBenefitCaseDetailsInformation LEFT JOIN
tblLookupPickListValues ON
tblBenefitCaseDetailsInformation.lngTechPreviewID
= tblLookupPickListValues.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_1 ON
tblBenefitCaseDetailsInformation.lngCustAddressCorrectID =
tblLookupPickListValues_1.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_2 ON
tblBenefitCaseDetailsInformation.lngAbandonedYesNoID =
tblLookupPickListValues_2.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_3 ON
tblBenefitCaseDetailsInformation.lngAccResultID =
tblLookupPickListValues_3.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_4 ON
tblBenefitCaseDetailsInformation.lngCommonSampleID =
tblLookupPickListValues_4.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_5 ON
tblBenefitCaseDetailsInformation.lngClientGroupID =
tblLookupPickListValues_5.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_6 ON
tblBenefitCaseDetailsInformation.lngStatSymbolID =
tblLookupPickListValues_6.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_7 ON
tblBenefitCaseDetailsInformation.lngCaseHandlingID =
tblLookupPickListValues_7.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_8 ON
tblBenefitCaseDetailsInformation.lngMethodOfPaymentID =
tblLookupPickListValues_8.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_9 ON
tblBenefitCaseDetailsInformation.lngMaritalStatusID =
tblLookupPickListValues_9.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_10 ON
tblBenefitCaseDetailsInformation.lngNoOfDepID =
tblLookupPickListValues_10.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_11 ON
tblBenefitCaseDetailsInformation.lngAgeOfYoungID =
tblLookupPickListValues_11.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_12 ON
tblBenefitCaseDetailsInformation.lngHousingID =
tblLookupPickListValues_12.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_13 ON
tblBenefitCaseDetailsInformation.lngOtherBenefitsID =
tblLookupPickListValues_13.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_14 ON
tblBenefitCaseDetailsInformation.lngOtherIncomeID =
tblLookupPickListValues_14.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_15 ON
tblBenefitCaseDetailsInformation.lngRd18NirsID =
tblLookupPickListValues_15.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_16 ON
tblBenefitCaseDetailsInformation.lngCSAInvolvementID =
tblLookupPickListValues_16.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_17 ON
tblBenefitCaseDetailsInformation.lngAbandonedPointID =
tblLookupPickListValues_17.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_20 ON
tblBenefitCaseDetailsInformation.lngDepInvolvmentCodeID =
tblLookupPickListValues_20.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_22 ON
tblBenefitCaseDetailsInformation.lngIBCategorySDAID =
tblLookupPickListValues_22.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_23 ON
tblBenefitCaseDetailsInformation.lngWFIOffErrorID =
tblLookupPickListValues_23.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_21 ON
tblBenefitCaseDetailsInformation.lng5VCID =
tblLookupPickListValues_21.lngPickListValuesID) LEFT JOIN
tblLookupPickListValues AS tblLookupPickListValues_25 ON
tblBenefitCaseDetailsInformation.lngCurrActivityCodeID =
tblLookupPickListValues_25.lngPickListValuesID) ON
tblBenefitCase.lngBenefitCaseId =
tblBenefitCaseDetailsInformation.lngBenefitCaseId;

!5 joins? Post your SQL.

:

All,

I have a table with 15 lookup values in. The lookup values all
related
to
the same table, therefore I have 15 Left Joins (tried it with
Inner,
no
difference). There are 1590 records in the main table, if I change
my
select
so that it brings back all records where caseid<=1590, it takes
approx
2
secs. If I remove the where clause, it takes over 30 minutes (its
still
running).

Can anyone shed anylight on this...

Td
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query Help 2
Update query issue 0
Update random values from field 1
perform INNER JOIN on query results 2
Lookup Table Used More than Once in a Query 5
Use the Second Max Value in a Formula???? 4
Query crosses 3 tables 2
Repost-Edit and save query criteria 5

Back
Top