首页 新闻 赞助 找找看

字符串拼接的存储过程/视图/函数 效率问题!

0
悬赏园豆:10 [已解决问题] 解决于 2008-05-19 00:18
<P>字符串拼接的存储过程/视图/函数 效率问题!</P> <P>如USE [PMS_HZTEST]<BR><BR>ALTER PROCEDURE [dbo].[FIN_CurrentDepositLedger_Select]<BR>(<BR>&nbsp;@PID BIGINT,<BR>&nbsp;@LGID BIGINT = NULL<BR>)<BR>AS<BR>SET NOCOUNT OFF;<BR>DECLARE @SQL_Statement NVARCHAR(3000);</P> <P>BEGIN</P> <P>&nbsp;IF @LGID IS NULL<BR>&nbsp;&nbsp;BEGIN<BR>&nbsp;&nbsp;SET @LGID=dbo.dpk_FD_DefaultLanguage();<BR>&nbsp;&nbsp;END<BR></P> <P>&nbsp;SET @SQL_Statement = '<BR>SELECT DISTINCT CASE WHEN z.stayBKTID=2<BR>&nbsp;&nbsp;&nbsp;THEN ''G''+IsNull(z.stayResvNo,'''')<BR>&nbsp;&nbsp;&nbsp;ELSE IsNull(z.stayResvNo,'''')<BR>&nbsp;&nbsp;END AS RESV#,z.stayHDID,<BR>&nbsp;z.BusinessDate AS RESV_DATE,<BR>&nbsp;dbo.uf_RPT_GetGuestName(z.stayBKTID,z.stayGuestPFID,z.stayDelegateFamilyName,z.stayDelegateGivenName, b.pfFamilyName,b.pfGivenName,c.hdGroupName) AS GUSET_NAME,<BR>&nbsp;stayArrivalDate AS ARRIVAL,<BR>&nbsp;[dbo].[uf_RPT_GetNoOfNTS](z.stayArrivalDate, z.stayDepartureDate) AS #NTS,<BR>&nbsp;ISNULL(e.rtCode,'''') AS RM_TYPE,<BR>&nbsp;dbo.uf_RPT_GetNoOfRoom_CurDepositLedger(z.PID,z.stayBKTID,e.rtROID,d.roomRTID,e.rtIsAffectAvail,z.stayShareWithSTID,z.stayHDID) AS #ROOM,<BR>&nbsp;dbo.uf_RPT_GetRMRate(z.pid,z.stayID,'+CAST(@LGID AS NVARCHAR)+','''+CONVERT(NVARCHAR(20),@BusinessDate,111)+''') AS RM_RATE,<BR>&nbsp;[dbo].[uf_RPT_GetTotalRoomCharges](z.PID,z.StayID,z.stayBKTID,z.stayHDID) AS TOTAL_ROOM_CHARGES,<BR>&nbsp;IsNull(a.dltAmt,0) AS DEPOSIT_RECEIVED,<BR>&nbsp;[dbo].[uf_RPT_Get_DiscountAmt](z.PID,z.StayID,z.stayBKTID,z.stayHDID) AS DISCOUNT_DEDUCTED,<BR>&nbsp;dbo.uf_RPT_GetHCV_dltID(z.PID,z.stayid) AS HCV#,<BR>&nbsp;a.BusinessDate AS DEPOSIT_DATE,<BR>&nbsp;ISNULL(g.plDesc,'''') AS PNAME,<BR>&nbsp;ISNULL(h.pCode,'''') AS PID,<BR>&nbsp;a.dltid<BR>FROM TXN_Stay z(NOLOCK)<BR>&nbsp;INNER JOIN TXN_DepositLedgerTxn a(NOLOCK) ON (z.PID=a.PID AND z.STAYID=a.dltSTAYID)<BR>&nbsp;LEFT JOIN Resv_Profile b(NOLOCK) ON (b.PFID=z.stayGuestPFID AND b.PFID&gt;0)<BR>&nbsp;INNER JOIN TXN_Header c(NOLOCK) ON (c.HDID = z.stayHDID AND c.PID=z.PID)<BR>&nbsp;INNER JOIN Txn_Room d(NOLOCK) ON (z.stayROOMID=d.ROOMID AND d.PID=z.PID)<BR>&nbsp;LEFT JOIN FO_RoomType e(NOLOCK) ON (e.RTID=d.roomRTID AND e.PID=d.PID)<BR>&nbsp;INNER JOIN FO_Property h(NOLOCK) ON (h.PID=z.PID)<BR>&nbsp;LEFT JOIN vw_Property_Local g(NOLOCK) ON (g.PID=h.PID AND g.LGID='+CAST(@LGID AS NVARCHAR)+' AND g.IsDeleted=0)<BR>WHERE STAYID&gt;0 AND z.stayInhouseSTID=102001 AND a.dltSTID IN (112001, 112004, 112009) AND a.dltType=1 ';</P> <P>&nbsp;PRINT @SQL_Statement;<BR>&nbsp;<BR>&nbsp;print len(@SQL_Statement);<BR>&nbsp;<BR>&nbsp;EXECUTE (@SQL_Statement);</P> <P>&nbsp;RETURN 0;<BR>END<BR></P> <P>------------------------------------------------------------------</P> <P>字符串拼接的存储过程中-&gt;函数多点好? 还是视图多点好? 还有写子查询?</P> <P>在性能上那个好一点?</P> <P><BR>&nbsp;</P> <P>&nbsp;</P>
IamV的主页 IamV | 初学一级 | 园豆:180
提问于:2008-05-05 01:44
< >
分享
最佳答案
0
这段SQL语句我没有细看(主要是因为排版的缘故),只是简单说说我的看法: 1. 你的这个多表查询的确是长了点,不过不要因此就修改数据库结构,不是因为“牵一发而动全身”,而是数据库结构优化要全盘考虑,决不能因为一条语句就跑去瞎改。 2. 函数、视图、子查询,这三者的性能没有必然的顺序。 3. 你的这个存储过程没有给全,对吧? 4. 两条print语句是测试用的? 5. 最后面的return用来干什么的? 6. 请仔细分析你的SQL语句,看看是不是可以避免使用动态SQL。(我大概看了你的那条动态SQL,好像完全可以用避免的)
电机拖动 | 小虾三级 |园豆:1295 | 2008-05-07 12:41
其他回答(4)
0
你太强了 竟然将那么多的表都联接查询 你的表关系真有那么复杂吗? 我最多的也就四个表进行过查询 看看你的数据库还能不能改进哈? 字符串拼接并不影响效率 但是转换成视图就不好了 函数是封装好的 这个也没什么的 写存储过程 最好不要用视图
yeyang | 园豆:418 (菜鸟二级) | 2008-05-05 09:13
0
昏`~看到这一大堆东西就头皮发麻~~很多时候把数据加载到内存再进行处理会更方便一些. 写成这种太难维护了`~
沙加 | 园豆:3680 (老鸟四级) | 2008-05-05 16:11
0
貌似只有一个查询的样子, 上帝保佑你!
风海迷沙 | 园豆:4453 (老鸟四级) | 2008-05-06 10:45
0
建议取出来数据之后在逻辑层做处理。而不是把所有的数据都在sql server上拼接好
玉开 | 园豆:8822 (大侠五级) | 2008-05-14 12:19
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册