Query taking forever to load

Databases
1

The following query works, however it is taking forever to load the results. Can anyone offer some advice?

SELECT
dba_case_tenant.case_no,
dba_case_tenant.GranteeID,
dba_case_tenant.UnitNum,
dba_case_tenant.tenant_home_phone,
dba_case_tenant.occ_date,
dba_case_tenant.tenant_f_name,
dba_case_tenant.tenant_l_name,
dba_case_tenant.hohYN,
dba_case_info.st_no,
dba_case_info.st_name,
dba_case_info.addressdescription,
dba_case_info.ComplianceCertificate,
funding.ID,
funding.grant_no,
units.UnitName
FROM
dba_case_tenant
Inner Join dba_case_info ON dba_case_info.case_no = dba_case_tenant.case_no
Inner Join units ON units.Case_No = dba_case_tenant.case_no AND units.UnitNumber = dba_case_tenant.UnitNum
Inner Join funding ON funding.ID = dba_case_info.grant_ID
WHERE
dba_case_tenant.GranteeID =  '12' AND
dba_case_tenant.hohYN =  'yes' AND
grant_no <> 'Lead Free' AND 
dba_case_info.ComplianceCertificate IS NOT NULL
order by dba_case_info.st_name, dba_case_info.st_no
2

Well, a couple things I noticed…[LIST=1]
[]Are your IDs numeric or strings? If they contain numeric values, they should be numeric.
[]Do you have indexes on all of the appropriate values? While #1 can potentially slow you down, not having indexes will kill you. At the very least, you should have indexes on all the fields being joined, and then also on the fields being searched upon.
[*]If you have a finite number of values for grant_no, then searching for those specifically will speed up the search as it won’t have to do a record by record scan and it can just do specific index searches (again, if indexes are defined)
[/LIST]That’s what I see, but I’m sure Rudy’ll be in here soon laying the smackdown on me if I gave you some boneheaded advice…:smiley:

3

The indexes made all the difference. Thank you!

4

:slight_smile: