Slow Query

B

bill.peters

I have a query that I know will be a little slow, but with changes I
just made it's so slow ODBC times out. I think it has to do with the
way the joins are built, but I can't figure out how to change the SQL
to make it work. I'll paste the code below, any optimizations are
appreciated, but please look at the joins, I just want all records in
FRPSDBA_FRPST100_MEMBER and anything matching in the rest of the
tables.

Thanks in advance
-Bill

----------CODE-----------
SELECT Trim(FRPSDBA_FRPST100_MEMBER!M_LAST_NAME) & ", " &
Trim(FRPSDBA_FRPST100_MEMBER!M_FIRST_NAME) & " " &
FRPSDBA_FRPST100_MEMBER!M_MIDDLE_INIT AS FullName,
FRPSDBA_FRPST100_MEMBER.M_SSN AS SSN,
FRPSDBA_FRPST100_MEMBER.M_DATE_OF_BIRTH AS DOB,
FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN AS [Member Date],
FRPSDBA_FRPST130_ASSIGN.A_TERM_DATE AS [Term Date],
FRPSDBA_FRPST100_MEMBER.M_SEX AS Gender, Right([F_COMPANY1],3) AS
[CO#1], Right([F_COMPANY2],3) AS [CO#2],
FRPSDBA_FRPST100_MEMBER.M_MEMBER_STATUS AS [Member Status],
FRPSDBA_FRPST100_MEMBER.M_BENEFITS_STATUS AS [Benefits Status],
PointsForYear.R_YEAR_POINTS AS [Points Year Total Points],
IIf([R_CR_YRS_EARNED]>0,80,0) AS [Points Year Total Allowable Points],
FRPSDBA_FRPST100_MEMBER.M_PRE04_YRS_EARNED AS [Pre 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_PST04_YRS_EARNED AS [Post 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_TOT_BANK_YEARS AS [Banked Years],
FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE AS [Total years of service
(credited and banked)], IIf([Age55]=-1,"Y","N") AS [Over 55?],
55-[Age]+(Right(Date(),4)) AS [Year Turns 55],
Round((DateDiff("y",FRPSDBA_FRPST100_MEMBER!M_DATE_OF_BIRTH,Date())/365),0)
AS Age, [Age]>54 AS Age55
FROM (FRPSDBA_FRPSV320_RETIRE RIGHT JOIN (FRPSDBA_FRPSV250_VFIS RIGHT
JOIN (FRPSDBA_FRPST130_ASSIGN RIGHT JOIN FRPSDBA_FRPST100_MEMBER ON
FRPSDBA_FRPST130_ASSIGN.A_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV250_VFIS.F_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV320_RETIRE.VR_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) LEFT
JOIN PointsForYear ON FRPSDBA_FRPST100_MEMBER.M_SSN =
PointsForYear.R_SSN
WHERE (((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE)>=1)) OR
(((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((PointsForYear.R_YEAR_POINTS)>=1))
ORDER BY FRPSDBA_FRPST100_MEMBER.M_SSN;
 
G

Guest

I't try to simplyfy the Where clause like so:

WHERE FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN <#11/1/2004#
AND (FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE)>=1
OR PointsForYear.R_YEAR_POINTS)>=1)

How many records? If over 1,000 I'd try some indexing.

All or some of the SSN fields might benefit from indexing as they are used
in joins. In particular FRPSDBA_FRPST100_MEMBER.M_SSN might use an index as
it's in both the Where and Order By clauses.

FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN might also benefit from indexing.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
I have a query that I know will be a little slow, but with changes I
just made it's so slow ODBC times out. I think it has to do with the
way the joins are built, but I can't figure out how to change the SQL
to make it work. I'll paste the code below, any optimizations are
appreciated, but please look at the joins, I just want all records in
FRPSDBA_FRPST100_MEMBER and anything matching in the rest of the
tables.

Thanks in advance
-Bill

----------CODE-----------
SELECT Trim(FRPSDBA_FRPST100_MEMBER!M_LAST_NAME) & ", " &
Trim(FRPSDBA_FRPST100_MEMBER!M_FIRST_NAME) & " " &
FRPSDBA_FRPST100_MEMBER!M_MIDDLE_INIT AS FullName,
FRPSDBA_FRPST100_MEMBER.M_SSN AS SSN,
FRPSDBA_FRPST100_MEMBER.M_DATE_OF_BIRTH AS DOB,
FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN AS [Member Date],
FRPSDBA_FRPST130_ASSIGN.A_TERM_DATE AS [Term Date],
FRPSDBA_FRPST100_MEMBER.M_SEX AS Gender, Right([F_COMPANY1],3) AS
[CO#1], Right([F_COMPANY2],3) AS [CO#2],
FRPSDBA_FRPST100_MEMBER.M_MEMBER_STATUS AS [Member Status],
FRPSDBA_FRPST100_MEMBER.M_BENEFITS_STATUS AS [Benefits Status],
PointsForYear.R_YEAR_POINTS AS [Points Year Total Points],
IIf([R_CR_YRS_EARNED]>0,80,0) AS [Points Year Total Allowable Points],
FRPSDBA_FRPST100_MEMBER.M_PRE04_YRS_EARNED AS [Pre 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_PST04_YRS_EARNED AS [Post 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_TOT_BANK_YEARS AS [Banked Years],
FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE AS [Total years of service
(credited and banked)], IIf([Age55]=-1,"Y","N") AS [Over 55?],
55-[Age]+(Right(Date(),4)) AS [Year Turns 55],
Round((DateDiff("y",FRPSDBA_FRPST100_MEMBER!M_DATE_OF_BIRTH,Date())/365),0)
AS Age, [Age]>54 AS Age55
FROM (FRPSDBA_FRPSV320_RETIRE RIGHT JOIN (FRPSDBA_FRPSV250_VFIS RIGHT
JOIN (FRPSDBA_FRPST130_ASSIGN RIGHT JOIN FRPSDBA_FRPST100_MEMBER ON
FRPSDBA_FRPST130_ASSIGN.A_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV250_VFIS.F_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV320_RETIRE.VR_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) LEFT
JOIN PointsForYear ON FRPSDBA_FRPST100_MEMBER.M_SSN =
PointsForYear.R_SSN
WHERE (((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE)>=1)) OR
(((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((PointsForYear.R_YEAR_POINTS)>=1))
ORDER BY FRPSDBA_FRPST100_MEMBER.M_SSN;
Click to expand...
 
B

bill.peters

Thanks Jerry,
Yes over 1000 records (I think around 4000) The tables are actually
linked via ODBC and the indexing is done in DB2. I don't have access to
the table design, but it looks like they have done some indexing. I
changed the WHERE clause and I think that helped, but I'm still
waiting. Before I couldn't even get any output into my ODBC Trace, and
now it looks like it's cranking away, the trace file is up to 10M.

To me it looks like the Join is reading the FRPSDBA_FRPST100_MEMBER
table many times, it seems like there should be a way to say "give me
all records in FRPSDBA_FRPST100_MEMBER and matching records in table1,
table2, table3... But everythng I've tried, errors out.

Thanks,
-Bill

Jerry said:
I't try to simplyfy the Where clause like so:

WHERE FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN <#11/1/2004#
AND (FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE)>=1
OR PointsForYear.R_YEAR_POINTS)>=1)

How many records? If over 1,000 I'd try some indexing.

All or some of the SSN fields might benefit from indexing as they are used
in joins. In particular FRPSDBA_FRPST100_MEMBER.M_SSN might use an index as
it's in both the Where and Order By clauses.

FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN might also benefit from indexing.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
I have a query that I know will be a little slow, but with changes I
just made it's so slow ODBC times out. I think it has to do with the
way the joins are built, but I can't figure out how to change the SQL
to make it work. I'll paste the code below, any optimizations are
appreciated, but please look at the joins, I just want all records in
FRPSDBA_FRPST100_MEMBER and anything matching in the rest of the
tables.

Thanks in advance
-Bill

----------CODE-----------
SELECT Trim(FRPSDBA_FRPST100_MEMBER!M_LAST_NAME) & ", " &
Trim(FRPSDBA_FRPST100_MEMBER!M_FIRST_NAME) & " " &
FRPSDBA_FRPST100_MEMBER!M_MIDDLE_INIT AS FullName,
FRPSDBA_FRPST100_MEMBER.M_SSN AS SSN,
FRPSDBA_FRPST100_MEMBER.M_DATE_OF_BIRTH AS DOB,
FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN AS [Member Date],
FRPSDBA_FRPST130_ASSIGN.A_TERM_DATE AS [Term Date],
FRPSDBA_FRPST100_MEMBER.M_SEX AS Gender, Right([F_COMPANY1],3) AS
[CO#1], Right([F_COMPANY2],3) AS [CO#2],
FRPSDBA_FRPST100_MEMBER.M_MEMBER_STATUS AS [Member Status],
FRPSDBA_FRPST100_MEMBER.M_BENEFITS_STATUS AS [Benefits Status],
PointsForYear.R_YEAR_POINTS AS [Points Year Total Points],
IIf([R_CR_YRS_EARNED]>0,80,0) AS [Points Year Total Allowable Points],
FRPSDBA_FRPST100_MEMBER.M_PRE04_YRS_EARNED AS [Pre 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_PST04_YRS_EARNED AS [Post 04 Years Earned],
FRPSDBA_FRPST100_MEMBER.M_TOT_BANK_YEARS AS [Banked Years],
FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE AS [Total years of service
(credited and banked)], IIf([Age55]=-1,"Y","N") AS [Over 55?],
55-[Age]+(Right(Date(),4)) AS [Year Turns 55],
Round((DateDiff("y",FRPSDBA_FRPST100_MEMBER!M_DATE_OF_BIRTH,Date())/365),0)
AS Age, [Age]>54 AS Age55
FROM (FRPSDBA_FRPSV320_RETIRE RIGHT JOIN (FRPSDBA_FRPSV250_VFIS RIGHT
JOIN (FRPSDBA_FRPST130_ASSIGN RIGHT JOIN FRPSDBA_FRPST100_MEMBER ON
FRPSDBA_FRPST130_ASSIGN.A_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV250_VFIS.F_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) ON
FRPSDBA_FRPSV320_RETIRE.VR_SSN = FRPSDBA_FRPST100_MEMBER.M_SSN) LEFT
JOIN PointsForYear ON FRPSDBA_FRPST100_MEMBER.M_SSN =
PointsForYear.R_SSN
WHERE (((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((FRPSDBA_FRPST100_MEMBER.M_TOT_YRS_SERVICE)>=1)) OR
(((FRPSDBA_FRPST130_ASSIGN.A_VOTED_IN)<#11/1/2004#) AND
((PointsForYear.R_YEAR_POINTS)>=1))
ORDER BY FRPSDBA_FRPST100_MEMBER.M_SSN;
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

Specific Age Query for a Date Range 3
Right Join query with Between HELP 3
Can someone explain the behavior of this query? 7
Slow Query 3
Ambiguous outer join vs. multiple duplicative records 1
Vaccine query 9
Running total in query 2
query very slow on small quotes table 5

Top