为什么我在SQL的where条件后面加一个1 = 1 性能就变快多了,求解释!

czmzhimin 2012-03-27 05:35:58
我的SQL语句是这样写的:
Dim sqlda As SqlDataAdapter
Dim query As StringBuilder = New StringBuilder()
query.Append(" SELECT STORE_ID, CUSTOMER_ID, JANCODE, ITEM_KANA, ")
query.Append(" SALES_QUANTITY_1 - SALES_QUANTITY_2 AS SALES_QUANTITY, ")
query.Append(" SALES_PRICE_SUM_1 - SALES_PRICE_SUM_2 - DETAIL_DISCOUNT - PROPORTIONAL_DISCOUNT AS SALES_PRICE ")
query.Append(" FROM (SELECT SALES.STORE_ID AS STORE_ID, ")
query.Append(" ITEM.CUSTOMER_ID AS CUSTOMER_ID, ITEM.JANCODE AS JANCODE, ")
query.Append(" ITEM.ITEM_KANA AS ITEM_KANA, ")
query.Append(" MAX(SALES.RETURN_DIVISION) AS RETURN_DIVISION, ")
query.Append(" MAX(SALES.DELETE_DIVISION) AS DELETE_DIVISION, ")
query.Append(" SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) THEN SALES.SALES_PRICE_SUM ")
query.Append(" ELSE 0 END) AS SALES_PRICE_SUM_1, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) THEN SALES.SALES_PRICE_SUM ")
query.Append(" ELSE 0 END) AS SALES_PRICE_SUM_2, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) THEN SALES.SALES_QUANTITY ")
query.Append(" ELSE 0 END) AS SALES_QUANTITY_1, SUM(CASE WHEN ((SALES.RETURN_DIVISION = 0) ")
query.Append(" AND (SALES.DELETE_DIVISION = 1)) OR ((SALES.RETURN_DIVISION = 1) ")
query.Append(" AND (SALES.DELETE_DIVISION = 0)) THEN SALES.SALES_QUANTITY ")
query.Append(" ELSE 0 END) AS SALES_QUANTITY_2, SUM(SALES.DETAIL_DISCOUNT) AS DETAIL_DISCOUNT, ")
query.Append(" SUM(SALES.PROPORTIONAL_DISCOUNT) AS PROPORTIONAL_DISCOUNT ")
query.Append(" FROM SALES INNER JOIN ITEM ON SALES.ITEM_ID = ITEM.ITEM_ID ")
query.Append(" INNER JOIN STORE ON STORE.STORE_ID = SALES.SALES_ID ")
query.Append(" INNER JOIN CUSTOMER ON ITEM.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID ")
query.Append(" WHERE SALES_DATE BETWEEN @SALES_DATE_START AND @SALES_DATE_END")

If storeid = 0 Then
If customer_id <> 0 Then
query.Append(" AND ITEM.CUSTOMER_ID = @CUSTOMER_ID")
Else
query.Append(" AND 1 = 1 ")

End If

query.Append(" GROUP BY SALES.STORE_ID, ITEM.JANCODE, ITEM.ITEM_KANA, ITEM.CUSTOMER_ID, ")
query.Append(" RETURN_DIVISION, DELETE_DIVISION, SALES_PRICE_SUM, ")
query.Append(" SALES_QUANTITY) AS AMOUNT ORDER BY ")

If orderflag = 0 Then
query.Append(" SALES_QUANTITY ")
Else
query.Append(" SALES_PRICE ")
End If

sqlda = New SqlDataAdapter(query.ToString(), Me.Connection)
sqlda.SelectCommand.Parameters.AddWithValue("@SALES_DATE_START", sales_Date_Start)
sqlda.SelectCommand.Parameters.AddWithValue("@SALES_DATE_END", sales_Date_End)
If customer_id <> 0 Then
sqlda.SelectCommand.Parameters.AddWithValue("@CUSTOMER_ID", customer_id)
End If
End If
一开始运行的时候要,性能很慢很慢,但是最后添加入红色标记的部分代码后,性能一下就大增了,数据都是一刷就出来了,这是问什么呀!求高手指点。
...全文
662 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
czmzhimin 2012-03-29
  • 打赏
  • 举报
回复
问题被证实了,确实是缓存的问题!谢谢各位。
czmzhimin 2012-03-28
  • 打赏
  • 举报
回复
不会吧,但是我加那个段else的东西,是可加可不加的没有什么实际性作用的,但是加了就性能变快了,这个缓存有关系吗?如果是和缓存有关系的话,那我第二次运行应该是很的呀,但是还是一样很慢很慢呀。
ixkixkix 2012-03-27
  • 打赏
  • 举报
回复
对,是缓存的问题,
黄_瓜 2012-03-27
  • 打赏
  • 举报
回复
问题应该不在这里,之所以这样可能是缓存问题。

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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