access的真假:四、"SELECT * INTO 工作表 FROM 临时表" 比 "CREATE TABLE 工作表 (...)" 速度快?
时 间:2009-09-11 09:55:33
作 者:ACMAIN_CHM ID:3535 城市:无锡
摘 要:试验结论: 猜想不成立。
正 文:
下面这个贴子中讨论了很多ACCESS中的一些猜想假设。
Access或其它数据库操作效率问题.欢迎讨论
http://topic.csdn.net/u/20090514/10/a93475bd-e67e-45c8-aa1e-87168ba36d02.html#replyachor
引用 55 楼 wwwwb 的回复::
create table是用什么方法?我一般是将空的工作表另存为一个临时表,
用SELECT * INTO 工作表 FROM 临时表 方法建立,这种方法比delete from table
还要慢?
这种方法的局限是如果有表间关系是不能删除的。
当看到这个猜想的时候,感觉比较怀疑。因为 select * into newtable from oldTable,ACCESS需要先从oldTable得到所有字段的定义,然后才能进行表的创建。如果猜想成立,就说明ACCESS还有一套什么机制可以直接在底层对表的结构进行访问。但select 可以是任意的查询,这样也不太可能啊。 于是,依然做个简单的试验来难证一下这个猜想。
新建空 t.mdb ,然后创建一个模块,代码如下。
view plaincopy to clipboardprint?
Option Compare Database
Option Explicit
Public Sub ti()
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
ssql = "create table table2(id integer,cname char(10))"
conn.Execute ssql
End Sub
Public Sub tx()
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim i As Integer
On Error Resume Next
For i = 1 To 9000
ssql = "drop table t" & (10000 + i)
CurrentProject.Connection.Execute ssql
Next i
On Error GoTo 0
End Sub
Public Sub t1()
Dim ssql As String
Dim i As Integer
For i = 1 To 9000
ssql = "create table t" & (10000 + i) & " (id integer,cname char(10))"
CurrentProject.Connection.Execute ssql
Next i
End Sub
Public Sub t2()
Dim ssql As String
Dim i As Integer
For i = 1 To 9000
ssql = "select * into t" & (10000 + i) & " from table2"
CurrentProject.Connection.Execute ssql
Next i
End Sub
Public Sub t()
Call tx
Debug.Print "t1 start.", Now
Call t1
Debug.Print "t1 end .", Now
Call tx
Debug.Print "t2 start.", Now
Call t2
Debug.Print "t2 end .", Now
End Sub
Option Compare Database
Option Explicit
Public Sub ti()
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
ssql = "create table table2(id integer,cname char(10))"
conn.Execute ssql
End Sub
Public Sub tx()
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim i As Integer
On Error Resume Next
For i = 1 To 9000
ssql = "drop table t" & (10000 + i)
CurrentProject.Connection.Execute ssql
Next i
On Error GoTo 0
End Sub
Public Sub t1()
Dim ssql As String
Dim i As Integer
For i = 1 To 9000
ssql = "create table t" & (10000 + i) & " (id integer,cname char(10))"
CurrentProject.Connection.Execute ssql
Next i
End Sub
Public Sub t2()
Dim ssql As String
Dim i As Integer
For i = 1 To 9000
ssql = "select * into t" & (10000 + i) & " from table2"
CurrentProject.Connection.Execute ssql
Next i
End Sub
Public Sub t()
Call tx
Debug.Print "t1 start.", Now
Call t1
Debug.Print "t1 end .", Now
Call tx
Debug.Print "t2 start.", Now
Call t2
Debug.Print "t2 end .", Now
End Sub
步骤一:运行 ti()创建一个原表 table2 以供后面的 select * into newtable from oldTable 使用。
步骤二:运行 t() 结果如下。
t1 start. 5/23/2009 3:06:54 PM
t1 end . 5/23/2009 3:07:03 PM
t2 start. 5/23/2009 3:07:17 PM
t2 end . 5/23/2009 3:07:29 PM
t1() 9s , t2() 12s
试验结论: 猜想不成立。
Access软件网官方交流QQ群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
- 通过Access宏录入数据到选...(11.10)
学习心得
最新文章
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)
- Access快速开发平台企业版--...(04.16)
- 【Access模块示例】通过模块代...(04.15)
- Access查询里面分组合计功能添...(04.13)
- 【Access删除查询】删除数字最...(04.12)
- 显示文件夹中所有文件的修改时间(04.11)