为什么.net程序员不喜欢使用预处理sql语句

[panhaichun]

日题

实不相瞒，鄙人最近以前是搞JAVA的，最近又参加了一个.net项目。

发现代码使用的都是字符串拼接成的sql语句，要不就是存储过程

为什么不用预处理语句？象这样

SqlCommand sqlCmd = new SqlCommand(........) ;

sqlCmd. CommandText = ″insert into user( username, password) values (@ username, @ password) ″;

sqlCmd. Parameters. Add ("@ username", "aaaa") ;

sqlCmd. Parameters. Add ("@ password", "bbbbbbbb") ;

sqlCmd. Prepare( ) ;

sqlCmd. ExecuteNonQuery( ) ;

。。。。。。

预处理语句顾名思义就是预编译好的SQL程序，除了第一次需要解释编译外，性能与存储过程相当，为什么没人用？？

[cymandhxl]

这个只和使用的人有关系。

[rufus_lee]

[Quote=引用 7 楼 lijian8021 的回复:]
用预处理语句可以避免sql注入
[/Quote]


绝对同意

[SadEmprie]

lz有些武断吧。。。。我们C#项目中就是用参数绑定的~~

[芽疼]

我每天都在拼SQL。。。

[zhu4139365]

[Quote=引用 2 楼 zzxap 的回复:]

因为市面上很多书都是拼SQL的

[/Quote]

[tkscascor]

这个不是预处理语句吧. 只是参数化sql语句而已,
基本上我都是用这种方式,少用存储过程,
减少以后数据库移植的问题,只有存储过程才是预编译的,
sql语句 都得编译一次才能使用,存储过程的速度上优势之一就是因为这个了,
说到变量, 参数化sql语句,也可以在里面声明变量的

			StringBuilder strSql=new StringBuilder();
			strSql.Append(" declare @Return int");
			strSql.Append(" select @Return=max(ProdSortChange) from TbInfoChange  ");
			strSql.Append(" set @Return=@Return+1" );
			strSql.Append(" update TbInfoChange set ProdSortChange=@Return  ");
			sTool.Open();
			bool returnValue=false;
			try
			{
				sTool.RunProcduce(strSql.ToString(),null,CommandType.Text);
				returnValue=true;
			}

...

[vssvss]

[Quote=引用 25 楼 jiezigege 的回复:]
拼sql很有可能别 sql注入，建议用存储过程，或楼主推荐的预处理sql，还可以试试 linq啊，我最近的项目一直在用linq感觉很好。
[/Quote]
Linq在多次处理时效率很低的 一边只在小型的项目中用 中大型用它会崩溃的 速度奇慢

[panhaichun]

[Quote=引用 41 楼 cgabriel 的回复:]
引用 32 楼 panhaichun 的回复:
引用 20 楼 cgabriel 的回复:
请勿使用你见到的 .Net 程序员代替所有的 .Net 程序员。

另外，你所谓的预处理本质上与直接写语句是一样的。在反复调用的情况之下，与存储过程的性能依然有很大的差距。

存储过程的优势是由数据库提供的，不是由编程语言提供的。

请勿混淆两个问题.


引用 31 楼 ourola 的回复:
预处理快多少??如果是这样,我想问LZ为什么不声明成静态的字段?更快.
PS:楼主的Java思想在作怪而已..
就好像很多人Java人不理解为什么C#的二维数组要用Arr[3,3],而Java用的是Arr[3][3].
谁更具面向对象思想相信大家都很清楚..


你说的哪里跟哪门。。。

为什么用预处理语句？？？？？？


数据库执行的时候会缓存sql语句，
比如现在要执行一条 select * from t where id = 1。
数据库会解释并编译它，执行，还会缓存它，
第二次出现一摸一样的语句时候只要缓存中有就不会再去编译这条sql语句，而直接执行

如果用了预处理语句

select * from t where id = @id 对于id=1,2,.....是都认为是同一条语句，因此不用再去解释编译，

而拼装执行时，select * from t where id = 1，select * from t where id = 2 是2条不同的语句，需要重新编译sql。。

因此说预处理语句跟存储过程性能相当，
存储过程在数据库中也就是个编译好的sql程序，很多一条简单的select也来个存储过程，不觉得很可笑吗


原来这也叫做预处理？

想问一下，例如首先执行一次 select * from t where id = @id， 然后在执行一万条其他各式查询，插入，更新语句，接着第一万零一次再执行 select * from t where id = @id， 你能够确保所谓的“缓存”还在？

如果你确定在的话，恳请提供官方的文档说明，让我等开开眼界。
[/Quote]


参见 <<ORACLE高效设计>>

其它很多数据库书籍都有说明，数据库都有缓存区，ORACLE不是还有个SGA吗？？那个够大

[Error_Code]

存储过程不容易维护
过分依赖数据库
智能客户端/离线操作不支持

[panhaichun]

不是预处理SQL??

那预处理SQL用.net应该怎么写？？

我的主要意思就是提倡使用预处理语。

存储过程是用来解决业务逻辑问题的，非此如果单单是为了性能，就用sql解决。
不过我还是推荐在程序里面解决业务逻辑问题

[tkscascor]

[Quote=引用 50 楼 panhaichun 的回复:]
不是预处理SQL??

那预处理SQL用.net应该怎么写？？

我的主要意思就是提倡使用预处理语。

存储过程是用来解决业务逻辑问题的，非此如果单单是为了性能，就用sql解决。
不过我还是推荐在程序里面解决业务逻辑问题

[/Quote]
不瞒你说,在c# 我还是第一次听说预处理语句,
sql语句, 需要编译一次, 才能转化成数据库可以识别的语句.
存储过程是已经编译过的,数据库直接可以调用, 无需编译 就可以识别.
基于业务逻辑的分离与数据库以后的移植工作, 我也不建议使用存储过程.
楼上有人说存储过程不容易维护,如果公司有专门的dba的时候, 你就不会这么说了 ...

[panhaichun]

我表达可能不好，终于在网上博客找到一遍文章来说明这个问题，不一定表示博主就是正确，我所表述的意思跟这个一致，

另外，可能我写的那个真不是预处理sql，JAVA对预处理sql的支持很明确

仅供参考。。


以下内容转帖

原帖地址：http://hi.baidu.com/zagelover/blog/item/e130e1a17fefe08d46106451.html?echoback=baidu&_t=0.37414278247802485

1.

ALTER PROCEDURE [dbo].[test]
AS
DECLARE
@statment NVARCHAR(300) -- 构造sql语句
BEGIN
SET @statment = 'SELECT ' + '1*10+20+15' + ' AS RESULT';
-- 方式一 预处理语句应该会使用 EXEC sp_executesql模式，这里楼主加的，微软官方无此说明，仅供参考
EXEC sp_executesql @statment
-- 方式二 普通sql语句
EXEC (@statment)
END


1.方式一支持参数替换，方式二不支持。

2.sp_executesql扩展存储过程与t-sql的execute功能相似，但有一点不同，
通过sp_executesql执行的执行计划会被缓存起来，可重复使用。

测试：nz.perfectaction [e-mail]

下面测试sp_executesql和exec的性能差别

Create DATABASE T_DBGOUSE T_DBGOCreate TABLE TB(ID INT IDENTITY(1,1) PRIMARY KEY,NAME VARCHAR(20))
GO
Insert INTO TB Select 'A'Insert INTO TB Select 'B'Insert INTO TB Select 'C'
Insert INTO TB Select 'D'Insert INTO TB Select 'E'Insert INTO TB Select 'F'
GO
--清除缓存中所有元素
DBCC FREEPROCCACHE
--查看T_DB数据库使用的缓存
Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB') AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'ORDER BY SQL
结果为空，
--测试使用EXEC，
执行下面sql语块
DECLARE @SQL VARCHAR(2000)DECLARE @NAME VARCHAR(20)DECLARE @I
INTSET @I=1WHILE @I<=6BEGIN
IF @I=1 SET @NAME='A'
IF @I=2 SET @NAME='B'
IF @I=3 SET @NAME='C'
IF @I=4 SET @NAME='D'
IF @I=5 SET @NAME='E'
IF @I=6 SET @NAME='F'
SET @SQL = 'Select * FROM TB Where NAME = '''+@NAME+''''
EXEC(@SQL)
SET @I = @I + 1
END
--查看T_DB数据库使用的缓存
Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB') AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'ORDER BY SQL
结果有六条记录如图:
这说明sql server 对于exec执行的sql语句，即使where字段是同一个，但值不一样，每次都需要重新编译，而使用不同的缓存。

--测试使用SP_EXECUTESQL，
执行下面sql语块
DECLARE @SQL NVARCHAR(2000)DECLARE @NAME NVARCHAR(20)DECLARE @I INTSET @I=1WHILE @I<=6BEGIN
IF @I=1 SET @NAME='A'
IF @I=2 SET @NAME='B'
IF @I=3 SET @NAME='C'
IF @I=4 SET @NAME='D'
IF @I=5 SET @NAME='E'
IF @I=6 SET @NAME='F'
SET @SQL = 'Select * FROM TB Where NAME = @NAME'
EXEC SP_EXECUTESQL @SQL,N'@NAME NVARCHAR(20)',@NAME
SET @I = @I + 1
END
--查看缓存
Select SQL AS EXEC_SQL,OBJTYPE AS EXEC_TYPE,* FROM MASTER..SYSCACHEOBJECTS Where DBID=DB_ID('T_DB') AND SQL LIKE '%Select * FROM TB Where NAME%' AND SQL NOT LIKE '%SYSCACHEOBJECTS%'ORDER BY SQL
结果除了刚才的六条记录，又增加一条记录如图:如上图，

说明sql server 对于sp_executesql执行的sql语句，只要where字段是相同的，尽管值不同，都不再需要重新编译，而执行使用同一个缓存计划。

--测试完毕
Drop DATABASE T_DBGODrop TABLE TBGO总结，sp_executesql执行计划会被缓存，而execute不可以，如果大量重复查询，sp_executesql比execute更能提高数据库性能。

[soaringbird]

[Quote=引用 42 楼 rufus_lee 的回复:]
引用 7 楼 lijian8021 的回复:
用预处理语句可以避免sql注入
绝对同意
[/Quote]
避免注入的是参数化语句，跟预处理是两码事

[panhaichun]

刚才又找了一篇博客证明我的问题。
其中的例子竟然跟我差不多，也是user表，呵呵



如果有人认为我那个不是预处理sql，请给出.net预处理sql的写法，或者你说.net不支持预处理sql。。。。


原帖地址：
http://kenchell.blog.163.com/blog/static/2608830920096412815105/

仅供参考，这篇跟之前引用的那篇又有所区别，请仔细区分。


我推荐大家看 <<ORACLE高效设计>>这本书，写的很详细

以下转帖：
DECLARE @A INT,@B INT
SET @A=1
SET @B=1
WHILE @A<100000
BEGIN
INSERT INTO TEST_TABLE (NEWS_TITLE,NEWS_CONTENT) VALUES(@A,@B)
SET @A=@A+1
SET @B=(@A+1)*5-2
END
//方法一：以上语句执行了26秒

DECLARE @A INT,@B INT
SET @A=1
SET @B=1
WHILE @A<100000
BEGIN
EXEC ('INSERT INTO TEST_TABLE (NEWS_TITLE,NEWS_CONTENT) VALUES(''' +@A + ''',''' + @B + ''')')
SET @A=@A+1
SET @B=(@A+1)*5-2
END
//方法二：以上语句执行了53秒

INSERT INTO test_table ( [news_content], [news_title]) VALUES ( 1,1)
GO
INSERT INTO test_table ( [news_content], [news_title]) VALUES ( 13,2)
GO
INSERT INTO test_table ( [news_content], [news_title]) VALUES ( 18,3)
GO

…………………………………………………………………
INSERT INTO test_table ( [news_content], [news_title]) VALUES ( 499993,99998)
GO
INSERT INTO test_table ( [news_content], [news_title]) VALUES ( 499998,99999)

//如果转换为99999条语句那么一共执行了2分钟。因为方法一和方法二使用了绑定变量，对于相同的语句只数据库服务器只编译一次，而这种方式每一句语句丢到数据库服务器解析都是不一样的。
在应用程序中使用绑定变量：
如果需要反复地执行一条SQL语句( 如插入和更新操
作) , 为提高效率可以利用Command 对象的Prepare( ) 方法创
建命令的一个准备版本以避免对SQL 语句的重复分析, 加快
执行速度, 以下代码( C#) 以Prepare 方法预处理SQL 插入命
令, 然后填写不同参数反复执行。这种方法在执行大量的数据
插入和更新命令时, 效率很高。
dbConn. Open( ) ;
SqlCommand sqlCmd = new SqlCommand( null, dbConn) ;
sqlCmd. CommandText = ″insert into user( username, password) values ( @ username, @ password) ″;
sqlCmd. Parameters. Add ( ″@ username″, userinfo[ 0] [ 0] ) ;
sqlCmd. Parameters. Add ( ″@ password″, userinfo[ 0] [ 1] ) ;
sqlCmd. Prepare( ) ;
sqlCmd. ExecuteNonQuery( ) ;
for( int i = 1; i < users. Length; i + + )
{
sqlCmd. Parameters[ 0] . Value = userinfo[ i] [ 0 ] ;
sqlCmd. Parameters[ 1] . Value = userinfo[ i] [ 1 ] ;
sqlCmd. ExecuteNonQuery( ) ;
}
dbConn. Close( ) ;

[CGabriel]

[Quote=引用 32 楼 panhaichun 的回复:]
引用 20 楼 cgabriel 的回复:
请勿使用你见到的 .Net 程序员代替所有的 .Net 程序员。

另外，你所谓的预处理本质上与直接写语句是一样的。在反复调用的情况之下，与存储过程的性能依然有很大的差距。

存储过程的优势是由数据库提供的，不是由编程语言提供的。

请勿混淆两个问题.


引用 31 楼 ourola 的回复:
预处理快多少??如果是这样,我想问LZ为什么不声明成静态的字段?更快.
PS:楼主的Java思想在作怪而已..
就好像很多人Java人不理解为什么C#的二维数组要用Arr[3,3],而Java用的是Arr[3][3].
谁更具面向对象思想相信大家都很清楚..



你说的哪里跟哪门。。。

为什么用预处理语句？？？？？？


数据库执行的时候会缓存sql语句，
比如现在要执行一条 select * from t where id = 1。
数据库会解释并编译它，执行，还会缓存它，
第二次出现一摸一样的语句时候只要缓存中有就不会再去编译这条sql语句，而直接执行

如果用了预处理语句

select * from t where id = @id 对于id=1,2,.....是都认为是同一条语句，因此不用再去解释编译，

而拼装执行时，select * from t where id = 1，select * from t where id = 2 是2条不同的语句，需要重新编译sql。。

因此说预处理语句跟存储过程性能相当，
存储过程在数据库中也就是个编译好的sql程序，很多一条简单的select也来个存储过程，不觉得很可笑吗
[/Quote]

原来这也叫做预处理？

想问一下，例如首先执行一次 select * from t where id = @id， 然后在执行一万条其他各式查询，插入，更新语句，接着第一万零一次再执行 select * from t where id = @id， 你能够确保所谓的“缓存”还在？

如果你确定在的话，恳请提供官方的文档说明，让我等开开眼界。

[Lezen]

习惯

[liupeng6899319]

什么东西？

[anlge999999]

[Quote=引用 19 楼 yyz985 的回复:]

是他们不会
很多时候做java的学.net都比做.net要精
[/Quote]

不对吧！
做.net 的比java做的精吧

[ningyb]

    conn.Open();    
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)";              
    
    cmd.Prepare();    
    
    cmd.Parameters.Add("?number", 1); 
    cmd.Parameters.Add("?text", "One");

lz这样才叫预处理，你那个语句，参数都添加好了，还预处理什么，等于没说

[ningyb]

使用预处理语句能够现住改善多次执行的查询的性能。对于多次执行的语句，预处理执行的速度快于直接执行，这是因为只需进行1次解析操作。在直接执行的情况下，每次执行时均将进行解析操作。预处理执行还能降低网络通信量，这是因为对于预处理语句的每次执行，仅需发送用于参数的数据。
预处理语句的另一优点是，它能使用二进制协议，这使得客户端和服务器间的数据传输更有效率。