sql效率问题 大哥大姐帮忙啊！！ 马上结账

[huangdong1900]

怎么样才能让这条sql速度变快？

SELECT
CASE
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=1 THEN '星期日'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=2 THEN '星期一'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=3 THEN '星期二'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=4 THEN '星期三'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=5 THEN '星期四'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=6 THEN '星期五'
ELSE '星期六'
END 星期几判断,
SUBSTRING(A.[kaisi],1,4) kaisi年,SUBSTRING(A.[kaisi],5,2) kaisi月,
B.[画面用Pattern] Pattern_1,
C.[画面用Pattern] Pattern_2,
D.[画面用Pattern] Pattern_3,
E.[画面用Pattern] Pattern_4,
F.[画面用Pattern] Pattern_5,
G.[画面用Pattern] Pattern_6,
H.[画面用Pattern] Pattern_7,
I.[画面用Pattern] Pattern_8,
J.[画面用Pattern] Pattern_9,
K.[画面用Pattern] Pattern_10,
L.[画面用Pattern] Pattern_11,
M.[画面用Pattern] Pattern_12,
N.[画面用Pattern] Pattern_13,
O.[画面用Pattern] Pattern_14,
P.[画面用Pattern] Pattern_15,
Q.[画面用Pattern] Pattern_16,
R.[画面用Pattern] Pattern_17,
S.[画面用Pattern] Pattern_18,
T.[画面用Pattern] Pattern_19,
U.[画面用Pattern] Pattern_20,
V.[画面用Pattern] Pattern_21,
W.[画面用Pattern] Pattern_22,
X.[画面用Pattern] Pattern_23,
Y.[画面用Pattern] Pattern_24,
Z.[画面用Pattern] Pattern_25,
AA.[画面用Pattern] Pattern_26,
BB.[画面用Pattern] Pattern_27,
CC.[画面用Pattern] Pattern_28,
DD.[画面用Pattern] Pattern_29,
EE.[画面用Pattern] Pattern_30,
FF.[画面用Pattern] Pattern_31
FROM [dbo].[calendar] A
LEFT JOIN [dbo].[tariff] B ON A.[bango]=B.[bango] AND A.[code]=B.[code] AND A.[SEQ]=B.[SEQ] AND A.[Pattern_1]= B.[Pattern]
LEFT JOIN [dbo].[tariff] C ON A.[bango]=C.[bango] AND A.[code]=C.[code] AND A.[SEQ]=C.[SEQ] AND A.[Pattern_2]= C.[Pattern]
LEFT JOIN [dbo].[tariff] D ON A.[bango]=D.[bango] AND A.[code]=D.[code] AND A.[SEQ]=D.[SEQ] AND A.[Pattern_3]= D.[Pattern]
LEFT JOIN [dbo].[tariff] E ON A.[bango]=E.[bango] AND A.[code]=E.[code] AND A.[SEQ]=E.[SEQ] AND A.[Pattern_4]= E.[Pattern]
LEFT JOIN [dbo].[tariff] F ON A.[bango]=F.[bango] AND A.[code]=F.[code] AND A.[SEQ]=F.[SEQ] AND A.[Pattern_5]= F.[Pattern]
LEFT JOIN [dbo].[tariff] G ON A.[bango]=G.[bango] AND A.[code]=G.[code] AND A.[SEQ]=G.[SEQ] AND A.[Pattern_6]= G.[Pattern]
LEFT JOIN [dbo].[tariff] H ON A.[bango]=H.[bango] AND A.[code]=H.[code] AND A.[SEQ]=H.[SEQ] AND A.[Pattern_7]= H.[Pattern]
LEFT JOIN [dbo].[tariff] I ON A.[bango]=I.[bango] AND A.[code]=I.[code] AND A.[SEQ]=I.[SEQ] AND A.[Pattern_8]= I.[Pattern]
LEFT JOIN [dbo].[tariff] J ON A.[bango]=J.[bango] AND A.[code]=J.[code] AND A.[SEQ]=J.[SEQ] AND A.[Pattern_9]= J.[Pattern]
LEFT JOIN [dbo].[tariff] K ON A.[bango]=K.[bango] AND A.[code]=K.[code] AND A.[SEQ]=K.[SEQ] AND A.[Pattern_10]= K.[Pattern]
LEFT JOIN [dbo].[tariff] L ON A.[bango]=L.[bango] AND A.[code]=L.[code] AND A.[SEQ]=L.[SEQ] AND A.[Pattern_11]= L.[Pattern]
LEFT JOIN [dbo].[tariff] M ON A.[bango]=M.[bango] AND A.[code]=M.[code] AND A.[SEQ]=M.[SEQ] AND A.[Pattern_12]= M.[Pattern]
LEFT JOIN [dbo].[tariff] N ON A.[bango]=N.[bango] AND A.[code]=N.[code] AND A.[SEQ]=N.[SEQ] AND A.[Pattern_13]= N.[Pattern]
LEFT JOIN [dbo].[tariff] O ON A.[bango]=O.[bango] AND A.[code]=O.[code] AND A.[SEQ]=O.[SEQ] AND A.[Pattern_14]= O.[Pattern]
LEFT JOIN [dbo].[tariff] P ON A.[bango]=P.[bango] AND A.[code]=P.[code] AND A.[SEQ]=P.[SEQ] AND A.[Pattern_15]= P.[Pattern]
LEFT JOIN [dbo].[tariff] Q ON A.[bango]=Q.[bango] AND A.[code]=Q.[code] AND A.[SEQ]=Q.[SEQ] AND A.[Pattern_16]= Q.[Pattern]
LEFT JOIN [dbo].[tariff] R ON A.[bango]=R.[bango] AND A.[code]=R.[code] AND A.[SEQ]=R.[SEQ] AND A.[Pattern_17]= R.[Pattern]
LEFT JOIN [dbo].[tariff] S ON A.[bango]=S.[bango] AND A.[code]=S.[code] AND A.[SEQ]=S.[SEQ] AND A.[Pattern_18]= S.[Pattern]
LEFT JOIN [dbo].[tariff] T ON A.[bango]=T.[bango] AND A.[code]=T.[code] AND A.[SEQ]=T.[SEQ] AND A.[Pattern_19]= T.[Pattern]
LEFT JOIN [dbo].[tariff] U ON A.[bango]=U.[bango] AND A.[code]=U.[code] AND A.[SEQ]=U.[SEQ] AND A.[Pattern_20]= U.[Pattern]
LEFT JOIN [dbo].[tariff] V ON A.[bango]=V.[bango] AND A.[code]=V.[code] AND A.[SEQ]=V.[SEQ] AND A.[Pattern_21]= V.[Pattern]
LEFT JOIN [dbo].[tariff] W ON A.[bango]=W.[bango] AND A.[code]=W.[code] AND A.[SEQ]=W.[SEQ] AND A.[Pattern_22]= W.[Pattern]
LEFT JOIN [dbo].[tariff] X ON A.[bango]=X.[bango] AND A.[code]=X.[code] AND A.[SEQ]=X.[SEQ] AND A.[Pattern_23]= X.[Pattern]
LEFT JOIN [dbo].[tariff] Y ON A.[bango]=Y.[bango] AND A.[code]=Y.[code] AND A.[SEQ]=Y.[SEQ] AND A.[Pattern_24]= Y.[Pattern]
LEFT JOIN [dbo].[tariff] Z ON A.[bango]=Z.[bango] AND A.[code]=Z.[code] AND A.[SEQ]=Z.[SEQ] AND A.[Pattern_25]= Z.[Pattern]
LEFT JOIN [dbo].[tariff] AA ON A.[bango]=AA.[bango] AND A.[code]=AA.[code] AND A.[SEQ]=AA.[SEQ] AND A.[Pattern_26]= AA.[Pattern]
LEFT JOIN [dbo].[tariff] BB ON A.[bango]=BB.[bango] AND A.[code]=BB.[code] AND A.[SEQ]=BB.[SEQ] AND A.[Pattern_27]= BB.[Pattern]
LEFT JOIN [dbo].[tariff] CC ON A.[bango]=CC.[bango] AND A.[code]=CC.[code] AND A.[SEQ]=CC.[SEQ] AND A.[Pattern_28]= CC.[Pattern]
LEFT JOIN [dbo].[tariff] DD ON A.[bango]=DD.[bango] AND A.[code]=DD.[code] AND A.[SEQ]=DD.[SEQ] AND A.[Pattern_29]= DD.[Pattern]
LEFT JOIN [dbo].[tariff] EE ON A.[bango]=EE.[bango] AND A.[code]=EE.[code] AND A.[SEQ]=EE.[SEQ] AND A.[Pattern_30]= EE.[Pattern]
LEFT JOIN [dbo].[tariff] FF ON A.[bango]=FF.[bango] AND A.[code]=FF.[code] AND A.[SEQ]=FF.[SEQ] AND A.[Pattern_31]= FF.[Pattern]

[huangdong1900]

可以不用这么多LEFT JOIN 来实现吗？

[mbh0210]

.....

[jinjazz]

这么多表，怎么看都快不了，你把链接的字段加索引吧

[mbh0210]

看的晕，你想实现什么效果？

[Liu_Qiang]

晕....看你连这么多表头就大了.数据库设计可能有些问题.

优化首先要建好表的索引

还有数据量小的可以用子查询代替INNER JOIN 这样更快些.

[huangdong1900]

没那么多表阿，就2个表
[dbo].[calendar] A
[dbo].[tariff] B

上面说LEFT JOIN 太多。。效率慢
所以求救能不能改成速度快点的sql

[编程有钱人了]

想实现什么功能

[「已注销」]

CASE
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=1 THEN '星期日'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=2 THEN '星期一'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=3 THEN '星期二'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=4 THEN '星期三'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=5 THEN '星期四'
WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=6 THEN '星期五'
ELSE '星期六'
END 星期几判断, 这一段先写吧，用CONVERT(DATETIME,112,KAISI))来代替,
像你这种情况，用游标是最快的了，用查询会更慢慢。因为游标会一条条怪值。
另外，如果像你这种情况。你的表结构是属于第一范式表结构。应该重新写表结构，

[Ny-6000]

。应该重新写表结构，

[叶子]

一看就晕！
定义个变量做表名，可以能快点！

[pt1314917]

  CASE 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=1 THEN '星期日' 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=2 THEN '星期一' 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=3 THEN '星期二' 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=4 THEN '星期三' 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=5 THEN '星期四' 
        WHEN DATEPART(WEEKDAY,CAST(A.[kaisi] AS DATETIME))=6 THEN '星期五' 
        ELSE '星期六' 
    END
--只是取个星期几而已，用得着搞这么多嘛。。
--直接用这个datename函数。例如：select datename(dw,getdate())

[ypacyhero]

你的left join过多，，肯定会慢。

看了一下，始终只有两张表在操作。

可不可以这样。
先建个临时表（就是你的结果表）。和你要查询出的结果结构一样。

然后用insert into ..... select ......这种方式。

试下吧。。

[ypacyhero]

我好像理解错了。

[以专业开发人员为伍]

tariff 表的一下字段需要建立索引：bango、code、SEQ、Pattern。

[以专业开发人员为伍]

另外，难道没有关于 A 的 where 约束部分吗？进查询界面需要显示的那些记录，不要查询不需要显示的记录。如果界面上显示的记录很多，应该分页查询。或者仅查询前边100条记录，当鼠标向下拖动到后边时才查询后边的数据，每次仅查询100条。

这个查询不奇怪，是个不错的业务查询。

[exy337]

看贴要回贴.

[kong521]

索引,视图+存储过程
如果分页就用分页的存储过程