[sklett]

I’ll admit, I’m not a DBA, I know enough to be dangerous

With that disclaimer in place, I’m wondering if anyone would be willing to comment on my query (posted below) and why it’s taking 5+ seconds to run.

Code:
SELECT pat.companyname, pat.weight_lbs, pat.patient_ssn, pat.phone,
pat_addy.Address_line_1, pat_addy.address_line_2, pat_addy.city, pat_addy.state, pat_addy.zip,
claim.insurance_claim_id, claim.amount,
trans.tranid, trans.transaction_id,
ins.companyname, policy.Policy_0
FROM INSURANCE_CLAIM AS claim
INNER JOIN TRANSACTIONS AS trans ON trans.transaction_id = claim.invoice_id
INNER JOIN CUSTOMERS AS pat ON pat.customer_id = trans.entity_id
INNER JOIN INSURANCE_CLAIM_STATUS AS ics ON ics.list_id = claim.status_id
INNER JOIN ADDRESS_BOOK AS pat_addy ON pat_addy.entity_id = pat.customer_id
INNER JOIN INSURANCE_POLICY AS policy ON policy.INSURANCE_POLICY_ID = claim.Insurance_Policy_ID
INNER JOIN CUSTOMERS AS ins ON ins.customer_id = policy.insurance_id
WHERE claim.insurance_claim_id = 10
It’s big, I know. Considering it’s only returning a single result, I’m surprised it’s taking so long.

I have a couple of possible ideas why this might be:

1) I’ve done something wrong, something in my JOINs is inefficient

2) Custom fields aren’t indexed in the database – this would really slow things down if this were the case.

3) It’s just the way it is and I shouldn’t expect things to be fast

I’m on a high speed internet connection.

I’m not doing any text comparisons, they are all PK/FK joins

Any comments, hints or suggestions very welcome.

Thanks,

Steve
This is a cached copy. Click here to see the original post.

[ivan.parra]

RE: Question about performance (review of my query maybe?)

The majority of the time is taken up in the custom fields.

I’m currently working on a way to make this faster. As soon as I’m done, you should notice a performance boost both custom field and non-custom field queries.

Thanks.

[Author]

Posts