关于SQLServer锁定 找个人记账软件
2楼: 谁来帮忙呀 如个人记账软件
3楼: 发生锁定？
是不是用sp_who查看，发现有两个进程相互deadlock？
4楼: 1 事务尽量短！
2 select 时显性的加with(nolock)，记住，with(nolock)一定要紧跟着表名
如：
select * from test with(nolock) where .....
5楼: 既然是查询，要什么临时表？
6楼: 查询时要作分析的，当然要用到临时表了，而且数据太大了，不可能一个语句就解决所有的处理的。
记账软件版7楼: 查询太大的时候，可以考虑分组。
8楼: 贴语句
大家帮你优化
记得用索引
9楼: IF EXISTS (SELECT ID FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID(＇＇TEMPDB..#TEMP_CGPSS＇＇)) DROP TABLE #TEMP_CGPSS
IF EXISTS (SELECT ID FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID(＇＇TEMPDB..#TEMP_CGPSS1＇＇)) DROP TABLE #TEMP_CGPSS1
IF EXISTS (SELECT ID FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID(＇＇TEMPDB..#TEMP_CGPSS2＇＇)) DROP TABLE #TEMP_CGPSS2
IF EXISTS (SELECT ID FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID(＇＇TEMPDB..#TEMP_CGTFPSS＇＇)) DROP TABLE #TEMP_CGTFPSS
SELECT A.PS_DD,A.CLS_DATE, A.PS_NO, (CASE WHEN (A.FC_ID=＇＇T＇＇ and A.PS_ID=＇＇SA＇＇) then ＇＇SF＇＇ else A.PS_ID end) PS_ID,B.CUS_OS_NO, SUBSTRING(A.REM,1,255) AS H_REM,A.SEND_MTH, A.PAY_REM,A.Voh_NO,A.VOH_ID,A.BIL_TYPE,B.ITM, A.IS_PE,
B.REM, B.DIS_CNT, B.VALID_DD, B.AMTN_COM,B.EST_DD,B.AMTN_RCV,B.AMT_RCV, B.PRD_NO, B.ID_NO,A.CUS_NO, A.CK_CLS_ID,A.LZ_CLS_ID, B.UNIT,B.CST_STD,B.CK_NO,A.CUR_ID,G.LZ_DD AS INV_DD,ABS(C.QTY) AS QTY,ABS(C.AMTN_NET) AS AMTN_NET,ABS(C.AMT) AS AMT,ABS(C.TAX) AS TAX, A.DEP, A.EXC_RTO,A.CHK_MAN,A.USR,B.PRD_MARK,A.SAL_NO, B.WH, A.AMTN_IRP,A.SEND_WH As HSEND_WH,B.QTY1,B.CSTN_SAL, B.Up,B.Up_Qty1,CST_MTL=isnull(B.CSTN_SAL,0)-isnull(B.CST_MAKE,0)-isnull(B.CST_MAN,0)-isnull(B.CST_PRD,0)-isnull(B.CST_OUT,0), B.CST_MAKE, B.CST_MAN, B.CST_PRD, B.CST_OUT, CST_SMTL=isnull(B.CST_STD,0)-isnull(B.CST_SMAKE,0)-isnull(B.CST_SMAN,0)-isnull(B.CST_SPRD,0)-isnull(B.CST_SOUT,0), B.CST_SMAKE, B.CST_SMAN, B.CST_SPRD, B.CST_SOUT, A.Amtn_EP,A.DIS_CNT DIS_CNT_ALL,B.BAT_NO,A.INV_NO,B.OS_ID,B.OS_NO,B.SEND_WH,B.PAK_UNIT,isnull(B.PAK_EXC,0) PAK_EXC,isnull(B.PAK_NW,0) PAK_NW,B.PAK_WEIGHT_UNIT,isnull(B.PAK_GW,0) PAK_GW,isnull(B.PAK_MEAST,0) PAK_MEAST,B.PAK_MEAST_UNIT,B.AMTN_EP AMTN_EP1, TAX_SA_ER1=( SELECT ISNULL(SUM(M.TAX),0) FROM TF_EXP M,MF_EXP H
WHERE H.EP_ID=M.EP_ID AND H.EP_NO=M.EP_NO AND H.EP_NO=A.EP_NO1 AND H.EP_ID=＇＇EP＇＇ AND H.BIL_ID=＇＇SA＇＇
AND H.PC_NO=A.PS_NO And IsNull(M.Share_Mth,＇＇＇＇)<>＇＇5＇＇),
TAX_SA_ER=( SELECT ISNULL(SUM(M.TAX),0) FROM TF_EXP M,MF_EXP H WHERE H.EP_ID=M.EP_ID AND H.EP_NO=M.EP_NO AND H.EP_ID=＇＇ER＇＇ AND H.BIL_ID=＇＇SA＇＇ AND H.PC_NO=A.PS_NO ), QTY_CK=(SELECT Sum(Case N.Unit When ＇＇2＇＇ then (IsNull(N.Qty,0)*IsNull(F.PK2_QTY,0)) When ＇＇3＇＇ then (IsNull(N.Qty,0)*IsNull(F.PK3_QTY,0)) Else IsNull(N.Qty,0) end) From TF_CK N Left Outer Join PRDT F On F.PRD_NO=N.PRD_NO Where ( ((IsNull(B.CK_NO,＇＇＇＇)<>＇＇＇＇) And (B.CK_NO=N.CK_NO) And (N.PRE_ITM=B.EST_ITM)) or((IsNull(N.OS_NO,＇＇＇＇)<>＇＇＇＇)And(N.OS_NO=B.PS_NO)and(N.OS_ID=＇＇SA＇＇)and(B.PRE_ITM=N.EST_ITM) ) ) )
,E.MRK AS MRK_NO, E.IDX1 AS IDX_NO,E.SPC,E.NAME AS PRD_NAME,E.NAME_ENG,
E.UPR,E.UP_MIN, E.UT1 AS UNIT1_NAME,E.DFU_UT,E.KND AS KND_ID,
E.UT,E.PK2_UT, E.PK2_QTY,E.PK3_UT, E.PK3_QTY,E.SUP1
,(TF_PSS_Z.ABCD) AS ABCD_TF_PSS_Z
,(MF_PSS_Z.BBBB) AS BBBB_MF_PSS_Z ,(MF_PSS_Z.CCC) AS CCC_MF_PSS_Z ,(MF_PSS_Z.ABCD) AS ABCD_MF_PSS_Z
INTO #TEMP_CGPSS1 From (MF_PSS A inner join TF_PSS B on A.PS_NO=B.PS_NO) Left Outer Join TF_PSS_Z On ((TF_PSS_Z.PS_ID=B.PS_ID) and (TF_PSS_Z.PS_NO=B.PS_NO) and (TF_PSS_Z.ITM=B.ITM)) inner join(MF_LZ G inner join TF_LZ C on G.LZ_NO=C.LZ_NO) on (C.CK_NO=B.PS_NO AND B.PRE_ITM=C.EST_ITM) LEFT OUTER JOIN PRDT E ON E.PRD_NO=B.PRD_NO Left Outer Join MF_PSS_Z On (MF_PSS_Z.PS_ID=A.PS_ID and MF_PSS_Z.PS_NO=A.PS_NO )
where (B.PS_ID In (＇＇SD＇＇,＇＇SB＇＇,＇＇SA＇＇) ) AND (B.PS_ID=A.PS_ID) AND (B.PS_NO=A.PS_NO)And(A.PS_DD>=＇＇02-01-2006＇＇)And(A.PS_DD<=＇＇02-28-2006＇＇)And (B.PRD_NO In (Select F.PRD_NO From PRDT F Where F.PRD_NO = B.PRD_NO And (F.KND in (＇＇1＇＇,＇＇2＇＇,＇＇3＇＇,＇＇4＇＇,＇＇5＇＇,＇＇6＇＇,＇＇7＇＇,＇＇A＇＇,＇＇B＇＇,＇＇C＇＇)))) and(1=1)
Order by G.INV_NO
大家帮忙看看怎么优化，有什么结构上的疑问可以问我。
联系我们
