where子句占用CPU时间的子句 [英] Substring in where clause eating CPU time
问题描述
SQL stmt的一部分,其中where子句中的子字符串函数占用了CPU时间。
我可以用其他任何形式重新排列以下查询吗? />
SELECT ELIG_TYP FROM TABLE1
WHERE COMPANY_CD IN
(SELECT SUBSTR(CMPNYID,1,4)FROM TABLE2 WHERE USR ='Badshah')
请回复
我尝试了什么:
SELECT ELIG_TYP FROM TABLE1 WHERE COMPANY_CD IN(SELECT SUBSTR(CMPNYID,1,4)FROM TABLE2 WHERE USR ='Badshah')
检查这些步骤...如果表格中的数据很大。
1. 1st使用临时表中所需的WHERE条件从表2中获取数据。
2.使用Substring,使用此临时表进行所需的Select查询。
如果需要,我可以在SQL服务器上发帖查询。
干杯
SELECT
t1.ELIG_TYP
FROM
TABLE1 t1
内连接
TABLE2 t2 on(t1.COMPANY_CD = left(t2.CMPNYID,4))WHERE t2.USR ='Badshah';
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')解决方案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
SELECT
t1.ELIG_TYP
FROM
TABLE1 t1
inner join
TABLE2 t2 on(t1.COMPANY_CD=left(t2.CMPNYID,4)) WHERE t2.USR ='Badshah';
这篇关于where子句占用CPU时间的子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!