这段sql代码怎么优化提高执行速度？

abjba 2017-10-26 09:44:04
往SQL Server 2016 数据库（数据库中只有8万条记录，而且只有3列都是smallint类型的小于30000的整数）中插入80条数据，居然要2.3秒。以下是我的asp代码。请问怎么优化可以提高执行速度呢？



<%'使用是asp语言



Set cons = Server.CreateObject("ADODB.Connection")

ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc"

cons.Open connstr

for i2= 1 to 80

  a1=***:a2=***:a3=*** 

    '说明：a1、a2、a3都是smallint类型的小于30000的整数，都是从其它access数据库获取的数据,已经测试过，获取这80条数据总共不会超过0.1秒。



   sql1="select * from d where a1="&a1&" and a2="&a2&" and a3="&a3

   set rs1=server.createobject("adodb.recordset")

   rs1.open sql1,cons,0,3,1

   if rs1.eof then

    rs1.addnew

    rs1("a1")=a1:rs1("a2")=a2:rs1("a3")=a3

    rs1.update

   end if

   rs1.close

next

%>
...全文
758 29 打赏 收藏 转发到动态 举报
写回复
用AI写文章
29 条回复
切换为时间正序
请发表友善的回复…
发表回复
abjba 2017-10-28
  • 打赏
  • 举报
 回复
引用 21 楼 yisuylm 的回复:
又看了一下，楼主的意思还要检查a1,a2,a3是否存在，那一定要建索引，不能因为samllint就不建
的确，对a1创建聚集索引，整体查询并插入的速度提高了不少，现在插入80条记录（插入之前先检查是否该条记录数据库中已经存在）大约需要0.3秒。
吉普赛的歌 版主 2017-10-27
  • 打赏
  • 举报
 回复
如果楼主 主要是为了优化程序，数据库方面根本不想动任何东西，建议转到程序版块
abjba 2017-10-27
  • 打赏
  • 举报
 回复
引用 24 楼 ayalicer 的回复:
80W行access 数据 每次拼接1W行 插入效率应该还可以
如果只有8W，使用access数据库非常合适，执行速度快。可是我的数据库初期是8W条记录，后面会不断增加，可能会达到上千万条，所以考虑到可持续性，不得不使用SQL Server数据库。
abjba 2017-10-27
  • 打赏
  • 举报
 回复
引用 23 楼 z10843087 的回复:
查询记录是否存在的那段代码 在数据库 中执行需要多长时间？
我的数据库，表中只有id、a1、a2、a3四个列（id为自增量，int类型；a1、a2、a3都是小于30000的整数，smallint类型），四个列都没有建立索引。 经测试，以下代码执行大约需1秒： 

<%'使用是asp语言,SQL Server数据库
Set cons = Server.CreateObject("ADODB.Connection")
ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc"
cons.Open connstr
j=0
for i= 1 to 80
 sql1="select a1,a2,a3 from d where a1=2 and a2=3 and a3=4"
 set rs1=server.createobject("adodb.recordset")
 rs1.open sql1,cons,0,3,1
 if rs1.eof then
  j=j+1
 end if
next
rs1.close
%>
就算没有建议索引，查询80次也不需要1秒吧？ 如果把上面的代码中的if rs1.eof then修改成if rs1.recordcount=0 then则执行只需不到0.1秒，但仅仅这样改是不对的，因为改了之后无论数据库中是否存在该条记录，rs1.recordcount都返回-1，和if rs1.eof then不等价。要使rs1.recordcount返回正确的记录数，就必须把rs1.open sql1,cons,0,3,1改成rs1.open sql1,cons,1,3,1 但这样一改，执行时间又变成要1秒左右的，和原来的if rs1.eof then执行时间不相上下。 所以，我觉得我上面的那段查询是否存在的代码还可以再优化，缩短执行时间。
砸死牛顿的苹果 2017-10-27
  • 打赏
  • 举报
 回复
说错是8W~
砸死牛顿的苹果 2017-10-27
  • 打赏
  • 举报
 回复
80W行access 数据 每次拼接1W行 插入效率应该还可以
OwenZeng_DBA 2017-10-27
  • 打赏
  • 举报
 回复
引用 22 楼 abjba 的回复:
[quote=引用 19 楼 ayalicer 的回复:] 我们说的 你是完全没理解...
因为当时不知道慢到底是哪里出了问题，以为是插入记录慢，所以大家的回答没法对症下药。现在找到问题所在，对症下药，才能解决问题呀。 慢的原因是 以下这段查询记录是否存在的那段代码需要优化： 
 
<%'使用是asp语言,SQL Server数据库
Set cons = Server.CreateObject("ADODB.Connection")
ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc"
cons.Open connstr
for i= 1 to 80
 sql1="select * from d where a1=2 and a2=3 and a3=4"
 set rs1=server.createobject("adodb.recordset")
 rs1.open sql1,cons,0,3,1
 if rs1.eof then  '此处消耗大量时间，需要修改
 end if
 rs1.close  '此处消耗一部分时间，应该移到for循环的外面(即next的下面)
next
%>
[/quote] 查询记录是否存在的那段代码 在数据库 中执行需要多长时间？优化办法： 1.对a1,a2,a3增加索引 2.不要用select * 改成select 1 from d where a1=2 and a2=3 and a3=4"
abjba 2017-10-27
  • 打赏
  • 举报
 回复
引用 19 楼 ayalicer 的回复:
我们说的 你是完全没理解...
因为当时不知道慢到底是哪里出了问题，以为是插入记录慢，所以大家的回答没法对症下药。现在找到问题所在，对症下药，才能解决问题呀。 慢的原因是 以下这段查询记录是否存在的那段代码需要优化： 
 
<%'使用是asp语言,SQL Server数据库
Set cons = Server.CreateObject("ADODB.Connection")
ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc"
cons.Open connstr
for i= 1 to 80
 sql1="select * from d where a1=2 and a2=3 and a3=4"
 set rs1=server.createobject("adodb.recordset")
 rs1.open sql1,cons,0,3,1
 if rs1.eof then  '此处消耗大量时间，需要修改
 end if
 rs1.close  '此处消耗一部分时间，应该移到for循环的外面(即next的下面)
next
%>
日月路明 2017-10-27
  • 打赏
  • 举报
 回复
又看了一下，楼主的意思还要检查a1,a2,a3是否存在，那一定要建索引，不能因为samllint就不建
日月路明 2017-10-27
  • 打赏
  • 举报
 回复
   sql1="select * from d where a1="&a1&" and a2="&a2&" and a3="&a3    set rs1=server.createobject("adodb.recordset")    rs1.open sql1,cons,0,3,1 ----------------------------------- a1,a2,a3未作索引，另外，如果只是插入，不考虑数据是否已存在， sql1改为 sql1="select  * from d where 1=2 and a1="&a1&" and a2="&a2&" and a3="&a3 可以极大地提高速度    
砸死牛顿的苹果 2017-10-27
  • 打赏
  • 举报
 回复
我们说的 你是完全没理解...
abjba 2017-10-27
  • 打赏
  • 举报
 回复
经过反复测试，发现问题出现在查询记录是否数据库中已经存在上。 

<%'使用是asp语言,SQL Server数据库
Set cons = Server.CreateObject("ADODB.Connection")
ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc"
cons.Open connstr
for i= 1 to 80
 sql1="select * from d where a1=2 and a2=3 and a3=4"
 set rs1=server.createobject("adodb.recordset")
 rs1.open sql1,cons,0,3,1
 if rs1.eof then  '此处消耗大量时间
 end if
 rs1.close  '此处消耗大量时间
next
%>
rs1.close将数据库关闭80次，消耗了大量时间，所以应该把rs1.close去掉，最后再关闭数据库。 此外，rs1.eof 也消耗大量时间，我本来打算把if rs1.eof then用 if rs1.recordcount=0 then来代替，可惜这样改无论数据库中是否存在该条记录，rs1.recordcount都返回-1。要使rs1.recordcount返回正确的值，必须把rs1.open sql1,cons,0,3,1改成rs1.open sql1,cons,1,3,1，但这样一来总的执行时间又变长了，和if rs1.eof then没什么区别。 请问有什么办法提高查询速度呢？
砸死牛顿的苹果 2017-10-26
  • 打赏
  • 举报
 回复
默认每一句 都是一个事务
OwenZeng_DBA 2017-10-26
  • 打赏
  • 举报
 回复
引用 11 楼 ayalicer 的回复:
上面第三行语句写错关键词了~ 其实都是支持的 效率提高差不多一个数量级 conn.BeginTrans 循环体插入语句 insert data conn.CommitTrans
嗯刚才打错了，， 不过access 每个语句也包含隐藏的事务吗，还是要显示的
砸死牛顿的苹果 2017-10-26
  • 打赏
  • 举报
 回复
上面第三行语句写错关键词了~ 其实都是支持的 效率提高差不多一个数量级 conn.BeginTrans 循环体插入语句 insert data conn.CommitTrans
OwenZeng_DBA 2017-10-26
  • 打赏
  • 举报
 回复
引用 8 楼 ayalicer 的回复:
access 也支持事务 和楼上写法类似 conn.BeginTrans 循环体插入语句 insert data conn.BeginTrans
好像新版本的access是支持的 ，很少用，不是很熟悉，不过access 每个语句也包含隐藏的事务吗，还是要显示的
zbdzjx 2017-10-26
  • 打赏
  • 举报
 回复
拼SQL，生成类似下面的语句： 
insert into d(a1, a2, a3)
select a1, a2, a3 from 
(
select a1-1 a1, a2-1 a2, a3-1 a3 union all
select a1-2, a2-2, a3-2 union all
select a1-3, a2-3, a3-3 union all
......
select a1-80, a2-80, a3-80    --80条数据
) a 
where not exists(
select 1 from d where d.a1=a.a1 and d.a2=a.a2 and d.a3=a.a3
)
砸死牛顿的苹果 2017-10-26
  • 打赏
  • 举报
 回复
access 也支持事务 和楼上写法类似 conn.BeginTrans 循环体插入语句 insert data conn.BeginTrans
OwenZeng_DBA 2017-10-26
  • 打赏
  • 举报
 回复
引用 6 楼 abjba 的回复:
[quote=引用 4 楼 z10843087 的回复:] [quote=引用 2 楼 abjba 的回复:] 即使循环插入，插入80条数据也不应该要2.3秒呀。同样的代码使用access数据库保存，只需要0.2秒。所以肯定是我的代码需要优化。
ACCESS是不支持事务的，所以不能直接对比。你可以试试在插入之前先开启一个事务，然后插入完成了再提交事务。 [/quote] 我的问题是往SQL Server 数据库中插入记录，但是速度慢。怎么提高提高速度呢？我试过将 ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc" 改成： ConnStr="PROVIDER=SQLOLEDB.1;DATA SOURCE=.;UID=sa;PWD=aaabbb;DATABASE=dbc 也试过，把 rs1.open sql1,cons,0,3,1 改成 rs1.open sql1,cons,1,3 结果速度更慢。所以，通过修改这些代码应该会提高存储速度。[/quote] ASP我不是很熟悉，我的意思你在插入之前，开启一个事务，类似下面的 conn.BeginTrans insert data conn.BeginTrans
abjba 2017-10-26
  • 打赏
  • 举报
 回复
引用 4 楼 z10843087 的回复:
[quote=引用 2 楼 abjba 的回复:] 即使循环插入，插入80条数据也不应该要2.3秒呀。同样的代码使用access数据库保存，只需要0.2秒。所以肯定是我的代码需要优化。
ACCESS是不支持事务的，所以不能直接对比。你可以试试在插入之前先开启一个事务，然后插入完成了再提交事务。 [/quote] 我的问题是往SQL Server 数据库中插入记录，但是速度慢。怎么提高提高速度呢？我试过将 ConnStr="driver={SQL Server};server=.; uid=sa; pwd=sa; database=dbc" 改成： ConnStr="PROVIDER=SQLOLEDB.1;DATA SOURCE=.;UID=sa;PWD=aaabbb;DATABASE=dbc 也试过，把 rs1.open sql1,cons,0,3,1 改成 rs1.open sql1,cons,1,3 结果速度更慢。所以，通过修改这些代码应该会提高存储速度。
加载更多回复（9）
JAVA代码审计之SQL注入
本章节课程主要从以下三方面详细的介绍了如何针对java代码中sql注入的审计方法及黑盒验证： 1、JDBC连接方式下sql注入的存在的形态及修复方法，like、in情况在如何安全使用预处理来防范sql注入 2、在使用Mybatis框架...
如何加快sql执行速度?
转自http://blog.csdn.net/weiwangchao_/article/details/89649981、升级硬件 2、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。3、扩大服务器的内存4、增加服务器CPU个数5、对于大的数据库不要设置数据库自动增长，它会降低服务器的性能6、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数...
SQL数据库如何加快查询速度?

SQL数据库如何加快查询速度? 
1、升级硬件  
2、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。 
3、扩大服务器的内存 
4、增加服务器CPU个数 
5、对于大的数据库不要设置数据库自动增长，它会降低服务器的性能 
6、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，
优化sql语句提高oracle执行效率(34种方法) 
优化sql语句提高oracle执行效率(34种方法) （1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在 FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表 (intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。 （2）WHERE子句中...
面试官：SQL优化的意义是什么？你用过哪些优化方式
随着系统的数据量逐年增加，并发量也成倍增长，SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。面对日益增多的SQL性能问题，如何下手以及如何提前审核已经成为越来越多的程序员必须要考虑的问题。 优化意义： 优化成本：硬件>系统配置>数据库表结构>SQL及索引...
MS-SQL Server

34,591

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章