提高百万级SQL数据库in查询速度的方法

dawin

方法一反着用挺新鲜的

wuxiang_123

方法4的IN在这里是属于运算符，方法1的IN在这里是属于谓词，虽然都是IN，但表示的意义是不同。方法4是属于SELECT查询，方法1是属于子查询。两者是不同的查询。

软件爱好者

学习了，谢谢

平平淡淡的天使

大师，我经过测试，有以下问题：
我之前用ACCESS做数据库，用SELECT * FROM TABLE WHERE FILEDS IN("XX","UU")这种方法是可以的。
但是我切换到用SQL SERVER做数据库，因为我是用变量的形式，，所以SELECT * FROM TABLE WHERE FILEDS IN('XX','UU')就不行了。。大师在SQL SERVER如何解决啊。。

little-key

平平淡淡的天使 发表于 2013-9-24 09:52
大师，我经过测试，有以下问题：
我之前用ACCESS做数据库，用SELECT * FROM TABLE WHERE FILEDS IN("XX"," ...

in在Sql Server 中是可以使用的

平平淡淡的天使

little-key 发表于 2013-9-24 10:40
in在Sql Server 中是可以使用的

1. Sub jinzhengduishou()
   
2. t = Timer
   
3. On Error Resume Next
   
4. Application.ScreenUpdating = False
   
5. shenghui = "SELECT 'ZZ'as 发货公司,* FROM XXX WHERE 发货公司中文 LIKE '浙CDCDCDC有限公司%'"
   
6.     jinyuan = "SELECT 'XX'as 发货公司,* FROM XXX where 发货公司中文 like '宁CDCDCD易有限公%'"
   
7.     yangguang = "SELECT 'DD'as 发货公司,* FROM XXX WHERE 发货公司中文 IN('厦CDCDCDC明有限公司','浙CDCDCD股份有限公司')"
   
8.     lidaxin = "SELECT 'GFD'as 发货公司,* FROM XXX WHERE 发货公司中文 IN('CDCDCD明有限公司','立CDCDCD有限公司','厦CDCD限公司') "
   
9.     aodeliang = "SELECT 'THG'as 发货公司,* FROM XXX WHERE 发货公司中文 LIKE '%CDCDCD%'"
   
10.     tongshida = "SELECT 'OIU'as 发货公司,* FROM XXX WHERE 发货公司中文 LIKE '厦门CDCDC'"
    
11.     hengdiandebang = "SELECT 'MNB'as 发货公司,* FROM XXX WHERE 发货公司中文 LIKE '浙CDCDCD口有限公司'"
    
12.     ningbooushang = "SELECT 'NVDVD'as 发货公司,* FROM XXX WHERE 发货公司中文 in('宁CDCDCD公司','宁CDCDCDCD工工贸有限公司')"
    
13.     zhejiangmeike = "SELECT 'FDFD'as 发货公司,* FROM XXXX WHERE 发货公司中文 IN('上CDCDCD有限公司','CDCDCD器有限公司')"
    
14.     hangzhouzhuangcheng = "SELECT 'OIU'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%杭CDCDC出口有限公司%'"
    
15.      zm = "SELECT 'FDFDF'as 发货公司,* FROM XXX WHERE 发货公司中文 IN('深圳CDCDCD有限公司','启CDCDCD技有限公司')"
    
16.     ln = "SELECT 'FDFDFD'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%深CDCD照明%'"
    
17.      yg = "SELECT 'FDCDC'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%深CDCDCD电科技%'"
    
18.      xingji = "SELECT 'ADDAA'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%厦CDCD电器%'"
    
19.       qiangl = "SELECT 'VDVSD'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%上CDCDC电子%'"
    
20.       ljin = "SELECT 'VSCSDCDS'as 发货公司,* FROM XXX WHERE 发货公司中文 LIKE '%东CDCD电子%'"
    
21.     donglin = "SELECT 'XAXASX'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%厦CSCSC电子%'"
    
22.        zhongr = "SELECT 'DEWDW'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%南CSCSCS器%'"
    
23.         zhoumin = "SELECT 'CSDCS'as 发货公司,* FROM XXXX WHERE 发货公司中文 LIKE '%深CSCSCSC份%'"
    
24.         juzuo = "SELECT 'CDSCSD'as 发货公司,* FROM XXXX WHERE 发货公司中文 IN('深CSCSCS份有限公司','深CSCSCSCS业有限公司')"
    
25.         hailan = "SELECT 'VDVDFV'as 发货公司,* FROM XXXX where 发货公司中文 in('厦CSCSCSCS公司','厦CSCSCSCS限公司')"
    
26.         guoxing = "SELECT 'CSDCSD'as 发货公司,* FROM XXXXXX where 发货公司中文='佛CSACSSCS限公司'"
    
27. Set cnn = New ADODB.Connection
    
28. Set rst = New ADODB.Recordset
    
29.   rst.CursorLocation = adUseClient
    
30.    Range("a5:AT66536").Cells.ClearContents
    
31.    ActiveSheet.PivotTables("数据透视表1").TableRange2.Delete
    
32. If TextBox1.Text = "" Or TextBox2.Text = "" Then
    
33.     MsgBox "请输入日期", 1 + 16, "系统提示"
    
34. Else
    
35.    strcn = "Provider=SQLOLEDB; User ID=XX;Password =XXX.X.XX;Data Source=XXXXXX.XXXX.XXX,1433;Initial Catalog=XXX" 'SQL SERVER版本
    
36.     cnn.Open strcn
    
37.    shuliang = "select sum(数量) from (" & shenghui & " union all " & jinyuan & " union all " & yangguang & " union all " & lidaxin & " union all " & aodeliang & " union all " & tongshida & " union all " & hengdiandebang & " union all " & ningbooushang & " union all " & zhejiangmeike & " union all " & hangzhouzhuangcheng & " union all " & zm & " union all " & ln & " union all " & yg & " union all " & xingji & " union all " & qiangl & " union all " & ljin & " union all " & donglin & " union all " & zhongr & " union all " & zhoumin & " union all " & juzuo & " union all " & hailan & " union all " & guoxing & ")a WHERE 日期 BETWEEN '" & TextBox1.Value & "' and '" & TextBox2.Value & "'"
    
38.    jine = "select sum(美金金额) from (" & shenghui & " union all " & jinyuan & " union all " & yangguang & " union all " & lidaxin & " union all " & aodeliang & " union all " & tongshida & " union all " & hengdiandebang & " union all " & ningbooushang & " union all " & zhejiangmeike & " union all " & hangzhouzhuangcheng & " union all " & zm & " union all " & ln & " union all " & yg & " union all " & xingji & " union all " & qiangl & " union all " & ljin & " union all " & donglin & " union all " & zhongr & " union all " & zhoumin & " union all " & juzuo & " union all " & hailan & " union all " & guoxing & ")a WHERE 日期 BETWEEN '" & TextBox1.Value & "' and '" & TextBox2.Value & "'"
    
39.     sql = "select 发货公司,sum(美金金额) as 总金额,sum(数量) as 总数量 from (" & shenghui & " union all " & jinyuan & " union all " & yangguang & " union all " & lidaxin & " union all " & aodeliang & " union all " & tongshida & " union all " & hengdiandebang & " union all " & ningbooushang & " union all " & zhejiangmeike & " union all " & hangzhouzhuangcheng & " union all " & zm & " union all " & ln & " union all " & yg & " union all " & xingji & " union all " & qiangl & " union all " & ljin & " union all " & donglin & " union all " & zhongr & " union all " & zhoumin & " union all " & juzuo & " union all " & hailan & " union all " & guoxing & ")a WHERE 日期 between '" & TextBox1.Value & "' and '" & TextBox2.Value & "'  group by 发货公司 ORDER BY sum(美金金额) desc "
    
40. 
    
41.      sql1 = "select 发货公司,收货公司英文,sum(美金金额) as 总金额,sum(数量) as 总数量 from (" & shenghui & " union all " & jinyuan & " union all " & yangguang & " union all " & lidaxin & " union all " & aodeliang & " union all " & tongshida & " union all " & hengdiandebang & " union all " & ningbooushang & " union all " & zhejiangmeike & " union all " & hangzhouzhuangcheng & " union all " & zm & " union all " & ln & " union all " & yg & " union all " & xingji & " union all " & qiangl & " union all " & ljin & " union all " & donglin & " union all " & zhongr & " union all " & zhoumin & " union all " & juzuo & " union all " & hailan & " union all " & guoxing & ")a WHERE 日期 between '" & TextBox1.Value & "' and '" & TextBox2.Value & "'  group by 发货公司,收货公司英文 ORDER BY sum(美金金额) desc "
    
42. 
    
43.     sql2 = "select 原产目的国,month(日期) as 月份,sum(美金金额) AS 总金额 from (" & shenghui & " union all " & jinyuan & " union all " & yangguang & " union all " & lidaxin & " union all " & aodeliang & " union all " & tongshida & " union all " & hengdiandebang & " union all " & ningbooushang & " union all " & zhejiangmeike & " union all " & hangzhouzhuangcheng & " union all " & zm & " union all " & ln & " union all " & yg & " union all " & xingji & " union all " & qiangl & " union all " & ljin & " union all " & donglin & " union all " & zhongr & " union all " & zhoumin & " union all " & juzuo & " union all " & hailan & " union all " & guoxing & ")a WHERE 日期 between '" & TextBox1.Value & "' and '" & TextBox2.Value & "'  group by 原产目的国,month(日期)"
    
44. 
    
45.      End If
    
46. Set rst = cnn.Execute(sql)
    
47.     Cells(5, 1).CopyFromRecordset rst
    
48. Set rst = cnn.Execute(sql1)
    
49.     Cells(5, 10).CopyFromRecordset rst
    
50.      Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    
51.     Set objPivotCache.Recordset = cnn.Execute(sql2)
    
52.      With objPivotCache
    
53.         .CreatePivotTable TableDestination:=Range("R3"), TableName:="数据透视表1"
    
54.     End With
    
55.      With ActiveSheet.PivotTables("数据透视表1")
    
56.         .PivotFields("原产目的国").Orientation = xlRowField
    
57.         .PivotFields("月份").Orientation = xlColumnField
    
58.         .AddDataField ActiveSheet.PivotTables("数据透视表1").PivotFields("总金额"), "求和项：总金额", xlSum
    
59.        With .PivotFields("求和项：总金额")
    
60.         .NumberFormat = "0.00"
    
61.        End With
    
62.     End With
    
63. '   arr5 = cnn.Execute(sql2).GetRows
    
64. '       arr5 = Application.WorksheetFunction.Transpose(arr5)
    
65. '       Call daquxiaoshao
    
66.       rst.Close
    
67.     Set rst = Nothing
    
68.       cnn.Close
    
69.     Set cnn = Nothing
    
70.       MsgBox " 运行时间：" & Format((Timer - t), "0.0000000")
    
71. End Sub

复制代码

大师，不好意思，是我搞错了。。我没有SELECT * FROM (SELECT * FROM TABLE UNION ALL SELECT * FROM TABLE)A 这里没有加别名，在SQL SERVER中好像都要加别名才能识别。
大师，您所IN('XX','YY')基本3秒。我才100多一年的数据。。就花了26秒多。。惭愧啊。。代码如下：大师帮忙优化下吧。。

little-key

平平淡淡的天使 发表于 2013-9-24 10:47
大师，不好意思，是我搞错了。。我没有SELECT * FROM (SELECT * FROM TABLE UNION ALL SELECT * FROM ...

(SELECT * FROM TABLE UNION ALL SELECT * FROM TABLE)
你这个重复的意义是什么呢？

平平淡淡的天使

little-key 发表于 2013-9-24 10:55
(SELECT * FROM TABLE UNION ALL SELECT * FROM TABLE)
你这个重复的意义是什么呢？

就是，我有很多个条件，我要把它先汇总 所以要用UNION ALL，，然后再汇总的基础上进行 分组 所以完整的是 SELECT SUM(数量),sum(金额）,sum(名称) from (这里面试所有UNION ALL 汇总的数据) a  这里要加个别名，SQL SERVER 才能识别。。
是这个意思，大师。。

little-key

平平淡淡的天使 发表于 2013-9-24 11:03
就是，我有很多个条件，我要把它先汇总 所以要用UNION ALL，，然后再汇总的基础上进行 分组 所以完整的是 ...

你这个是一个表的合并

平平淡淡的天使

little-key 发表于 2013-9-24 13:30
你这个是一个表的合并

大师，，我上面的代码，能否帮忙优化下，，谢谢！！