存储过程动态Sql效率有关问题

存储过程动态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--------------------------------FORDSQL> select empno,ename from emp where (ename=:ename or :ename is null);     EMPNO ENAME---------- ----------      7902 FORDSQL> 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); 
 

这种写法在sql下面不错的，但是在oracle里似乎效率有点问题，oracle不进行断路径求值，有点郁闷.

SQL code
scott@ORA1> select * from emp where ename=:x OR :x is null;     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20执行计划----------------------Plan hash value: 3956160932--------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------|   0 | SELECT STATEMENT  |      |     2 |    74 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     2 |    74 |     3   (0)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter(:X IS NULL OR "ENAME"=:X)统计信息----------------------          0  recursive calls          0  db block gets          8  consistent gets          0  physical reads          0  redo size        718  bytes sent via SQL*Net to client        377  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedscott@ORA1> select * from emp where ename=:x;     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20执行计划----------------------Plan hash value: 79656022--------------------------------------------------------| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------|   0 | SELECT STATEMENT            |              |     1 |    37 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    37 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_EMP_NAME |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ENAME"=:X)统计信息----------------------          0  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        722  bytes sent via SQL*Net to client        377  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed