日期:2014-05-17  浏览次数:20895 次

存储过程动态Sql效率问题?
目前我的程序是使用拼接Sql的方式,例如:
C# code
string sql = "select * from tb1 where 1=1 ";
if(id != null){
  sql += "and id = :vId ";
}
if(name != null){
  sql += "and name = :vName ";
}


因为这样执行的速度比较慢,就想改用存储过程
不过如果在存储过程里,岂不是也要使用动态Sql,那效率是不是也是一样的低呢?
比如:
SQL code
CREATE OR REPLACE PROCEDURE P_GetData(vId in varchar2,vName in varchar2) IS
    vSql Varchar2(1000);
BEGIN
    vSql := 'select * from tb1 where 1=1 ';
    if vId is not null then 
       vSql := vSql || 'and id = ' || vId || '';
    end if;
    if vName is not null then 
       vSql := vSql || 'and name = ' || vName || '';
    end if;
     execute immediate vSql;
END P_GetData;


那这样效率应该也是一样低吧
请各位老大给予指导,应该如何提高效率?
总不能这样吧:
SQL code
CREATE OR REPLACE PROCEDURE P_GetData(vId in varchar2,vName in varchar2) IS
       tmp renyb%ROWTYPE;
BEGIN
    if vId is not null and vName is not null then 
       select * into tmp from renyb where id = vId and name = vName;
    else 
       if vId is not null then
          select * into tmp from renyb where id = vId;
       end if;
       if vName is not null then
          select * into tmp from renyb where name = vName;
       end if;
    end if;
END P_GetData;



------解决方案--------------------
你看是这个意思吗? 条件为空就不加到where子句中去:

SQL code

SQL> begin
  2  :ename:='FORD';
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> print :ename;

ENAME
--------------------------------
FORD

SQL> select empno,ename from emp where (ename=:ename or :ename is null);

     EMPNO ENAME
---------- ----------
      7902 FORD

SQL> begin
  2  :ename:=null;
  3  end;
  4  /

PL/SQL 过程已成功完成。

SQL> select empno,ename from emp where (ename=:ename or :ename is null);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

已选择14行。

------解决方案--------------------
id我是按number型处理的
select * from tb1 where name like nvl(v_name,'%') and to_char(id) like nvl(to_char(v_id),'%')
------解决方案--------------------
从你帖出来的sql表达,c#和procedure在效率上不会相差多少的,因为你发送的只是一条SQL,而没有复杂的业务逻辑.

你应该查看一个执行计划,看是否用到索引,另个确认下在id,name列是否建立了index.





引用楼主 youbl 的帖子:
目前我的程序是使用拼接Sql的方式,例如:

C# codestring sql = "select * from tb1 where 1=1 ";
if(id != null){
sql += "and id = :vId ";
}
if(name != null){
sql += "and name = :vName ";
}



因为这样执行的速度比较慢,就想改用存储过程
不过如果在存储过程里,岂不是也要使用动态Sql,那效率是不是也是一样的低呢?
比如:

SQL codeCREATE OR REPLACE PROCEDURE P_GetData(vId in varchar2…

------解决方案--------------------
vSql := 'select * from tb1 where 1=1 and id like '||''''||nvl(v_id,'%')||''''||' and name like'||''''||nvl(vname,'%')||'''' ;
------解决方案--------------------
语法不对阿。你调用存储过程时候肯定两个参数都不为空的阿
------解决方案--------------------
2楼的写法:

select empno,ename from emp where (ename=:ename or :ename is null);