Substring in where clause eating CPU time

Question

0.00/5 (No votes)

See more:

A part of the SQL stmt where the substring function in where clause is eating CPU time.

Can I rearrange the below query in any other form?

SELECT ELIG_TYP FROM TABLE1
WHERE COMPANY_CD IN
(SELECT SUBSTR(CMPNYID, 1, 4) FROM TABLE2 WHERE USR = 'Badshah')

Pls Reply

What I have tried:

SELECT ELIG_TYP FROM TABLE1 WHERE COMPANY_CD IN (SELECT SUBSTR(CMPNYID, 1, 4) FROM TABLE2 WHERE USR = 'Badshah')

Posted 15-Aug-17 19:34pm

Member 12613458

Updated 15-Aug-17 23:44pm

Add a Solution

Comments

Member 12613458 16-Aug-17 1:43am

The Database is DB2

2 solutions

Solution 1

Hi,

Check these steps...if data in your tables is in huge.

1. 1st Fetch your data from table 2 with required WHERE conditions in temp table.
2. Make your desired Select query with this temp table by using Substring.

If required i can post query in SQL server.

Cheers

Posted 15-Aug-17 19:49pm

Magic Wonder

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Accepted Answer · 2017-08-15T23:44:00

Solution 2

SELECT
t1.ELIG_TYP
FROM
TABLE1 t1
inner join
TABLE2 t2 on(t1.COMPANY_CD=left(t2.CMPNYID,4)) WHERE t2.USR ='Badshah';

Posted 15-Aug-17 23:44pm

Santosh kumar Pithani

Comments

Member 12613458 16-Aug-17 6:28am

There is no difference at all between left and substring because left is translated to substring in the execution plan.

Santosh kumar Pithani 16-Aug-17 7:37am

OK but sql code execution plan is fast while using joins so left function doesn't affect CPU performance.