Left join running very slow - any directions to speed up

I have a query using left join, running very slow - how to speed up.

SELECT temp_RFI.callid, [%$##@_Alias].request_type
FROM temp_RFI LEFT JOIN [SELECT call.request_type, call.casetype, oldcasenumber
FROM call
WHERE (((call.request_type)="IT" Or (call.request_type)="TA") AND ((call.casetype)="FA" Or (call.casetype)="NFA" Or (call.casetype)="ERU" Or (call.casetype)="Lost Injured Missing"))]. AS [%$##@_Alias] ON temp_RFI.oldcasenumber = [%$##@_Alias].oldcasenumber;
JsaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SeanStricklandCommented:
Do you have indexes on requesttype and casetype?

That would probably help.
0
JsaraAuthor Commented:
Yes the requesttype and casetype fields are part of index fields.
0
jamesguCommented:
is it slow to run your inner query alone?

[SELECT call.request_type, call.casetype, oldcasenumber
FROM call
WHERE (((call.request_type)="IT" Or (call.request_type)="TA") AND ((call.casetype)="FA" Or (call.casetype)="NFA" Or (call.casetype)="ERU" Or (call.casetype)="Lost Injured Missing"))

is oldcasenumber  the first column of your index if any on your temp_RFI  table?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JsaraAuthor Commented:
The inner query runs faster no problem with that.
0
NatchiketCommented:
sometimes using an IN clause works faster than OR , you could try:

SELECT temp_RFI.callid, [%$##@_Alias].request_type
FROM temp_RFI LEFT JOIN [SELECT call.request_type, call.casetype, oldcasenumber
FROM call
WHERE (((call.request_type IN ("IT","TA")) AND ((call.casetype IN ("FA","NFA","ERU","Lost Injured Missing")))]. AS [%$##@_Alias] ON temp_RFI.oldcasenumber = [%$##@_Alias].oldcasenumber;

(not sure about my bracketing but you get the idea)


0
SeanStricklandCommented:
Can you join them directly without doing the subquery?  It's likely running slow because you're hitting that subquery on every record that you'll pulling here: "SELECT temp_RFI.callid, [$##@_Alias].request_type FROM temp_RFI".  If you can do this without a subquery it will run faster, otherwise this may be the fastest way for you to process it.  What I'm saying is similar to the code below, but not dead on.

SELECT temp_RFI.callid, [%$##@_Alias].request_type, call.request_type, call.casetype, call.oldcasenumber
FROM temp_RFI LEFT JOIN temp_RFI.oldcasenumber = [%$##@_Alias].oldcasenumber
WHERE ((call.request_Type IN ("IT","TA")) AND (call.casetype IN ("FA","NFA","ERU","Lost Injured Missing"));
0
jamesguCommented:
do you have an index on the  temp_RFI  table?

is oldcasenumber  the first column of your index if any on your temp_RFI  table?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.