October 26, 2010 at 3:51 am
GO
/****** Object: StoredProcedure [dbo].[sp_Analysis] Script Date: 10/26/2010 15:19:02 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CreatePROCEDURE [dbo].[sp_Analysis]
@fromdate smalldatetime,
@todate smalldatetime,
@userid int,
@companyids varchar(8000),
@ResultType int=0
AS
/*
DECLARE @fromdate smalldatetime
DECLARE @todate smalldatetime
DECLARE @userid int
DECLARE @companyids varchar(8000)
set @fromdate = '01-Apr-2006'
set @todate = '31-Mar-2007'
set @userid = 0
set @companyids = '0'
*/
/* select 'Report Blocked Permition Denied For All Users' as Company,
'' as Director,
1 as Sale,1 as GIT,1 as [UNDER DISPATCH], 1 as [PENDING ORDERS],1 as [Total], 1.00 as [Contribution %]
*/
--set @companyids ='12,9,5,8,1,2,4,3,6,10,11'
if @ResultType = 0 -- Customer Wise
BEGIN
SELECT
(select scust_desc + ' (' + scust_cd + ')' from customermst where icust_id = iinv_custid) as CUSTOMER,
(select sEMP_CD from EMPLOYEEMST where iEMP_ID = (select top 1 iCUST_MERCHANDID from CUSTOMERMST where iCUST_id = iinv_custid)) [MERCHAND],
--(select top 1 sCUST_country from CUSTOMERMST where iCUST_id = iinv_custid) [MERCHAND],
--(select top 1 sComp_desc from CompanyMST where iComp_id = iinv_compid) Comp,
(select
isnull(sum(convert(numeric(18,2),isnull(aa.dINV_EXCHNGRATE ,0) * ( isnull(aa.dINV_NETAMT,0) + isnull(aa.dinv_ADVPMT,0)))),0)
from invoicemst aa
where
iinv_CUSTID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID)) and
aa.iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID)) and
CONVERT(datetime,aa.[dtinv_bl]) >= @fromdate and CONVERT(datetime,aa.[dtinv_bl]) <= @todate
and aa.[dtinv_bl] is not null
and aa.bINV_free=0
and aa.iinv_CUSTID = invoicemst.iinv_CUSTID
--and aa.iINV_COMPID = 1
) AS Sale,
isnull((SELECT sum([INR AMT]) FROM vwGIT
WHERE
iCUST_ID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID)) and
iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID))
and [Date] >= @FromDate and [Date] <= @ToDate
and iCUST_ID = invoicemst.iinv_CUSTID),0) AS GIT,
-- isnull((SELECT sum([INR AMT]) FROM vwUnderCollection
-- WHERE
-- iCUST_ID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID)) and
-- iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID))
-- and [date] >= @FromDate and [date] <= @ToDate
-- and iCUST_ID = invoicemst.iinv_CUSTID),0) AS [UNDET COLLECTION],
isnull((SELECT sum([INR AMT]) FROM vwUnderDispatch
WHERE
iCUST_ID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID)) and
iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID))
and [date] >= @FromDate and [date] <= @ToDate
and iCUST_ID = invoicemst.iinv_CUSTID),0) as [UNDER DISPATCH]
,
(select isnull(sum([TOTAL PENDING INR AMT]),0) from ((select
convert(numeric(18,2),(isnull(dCURR_RATE,0) *SUM(dORDD_RATE* (iORDD_QTY-iORDD_QTYISS)))) as [TOTAL PENDING INR AMT]
from vwBackup_ORDER
left join CURRENCYMST on iCURR_ID = iORD_CURRID
where dtORD_SHIPMENTDT>= @FromDate and dtORD_SHIPMENTDT <= @ToDate
and iORD_CUSTID in (SELECT CUSTID from dbo.fn_GetCustomers(@userID)) and
iORD_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID))
AND iordd_qty > iordd_QTYiss and bORDD_CLOSE=0 and bORD_CLOSE=0
AND bORD_OLD = 0 and iORD_CUSTID = invoicemst.iinv_CUSTID
group by
iORD_CUSTID, dCURR_RATE --,sORD_PYMTTERMCD,sORD_TRANSMODECD,sBANK_NAME,sORD_INDENTNO,bORD_CLOSE
HAVING SUM(iORDD_QTY)-SUM(iORDD_QTYISS) >0 --and bORD_CLOSE=0
)) as ord) as [PENDING ORDERS],
0 as [Total], 0.00 as [Contribution %]
FROM invoicemst WHERE
iinv_CUSTID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID))
and invoicemst.iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID)) and
((CONVERT(datetime,[dtinv_date]) >= @fromdate and CONVERT(datetime,[dtinv_date]) <= @todate and binv_cancel = 0) or
(CONVERT(datetime,[dtinv_bl]) >= @fromdate and CONVERT(datetime,[dtinv_bl]) <= @todate and [dtinv_bl] is not null))
and bINV_free=0
group by iinv_CUSTID
UNION all
select CUSTOMER,
(select sEMP_CD from EMPLOYEEMST where iEMP_ID = (select top 1 iCUST_MERCHANDID from CUSTOMERMST where iCUST_id = iord_custid)) [MERCHAND],
--(select top 1 sCUST_country from CUSTOMERMST where iCUST_id = iord_custid) [MERCHAND],
--comp,
0,0,0, isnull(SUM(INR),0) as aa,0, 0.00 as [Contribution %] from (
select
iORD_CUSTID,
(select scust_desc + ' (' + scust_cd + ')' from customermst where icust_id = iORD_CUSTID) as CUSTOMER,
convert(numeric(18,2),(isnull(dCURR_RATE,0) *SUM(isnull(dORDD_RATE,0)* (isnull(iORDD_QTY,0)-isnull(iORDD_QTYISS,0))))) as INR
--(select sComp_desc from CompanyMST where iComp_id = iORD_COMPID) Comp
from vwBackup_ORDER
left join CURRENCYMST on iCURR_ID = iORD_CURRID
where dtORD_SHIPMENTDT>= @fromdate and dtORD_SHIPMENTDT <= @todate
and iORD_CUSTID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID)) and
iORD_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID)) and
iordd_qty > iordd_QTYiss AND bORD_OLD = 0 and bORD_CLOSE=0 and bORDD_CLOSE=0
and iORD_CUSTID not in (select iinv_custid FROM invoicemst WHERE
iinv_CUSTID in (SELECT CUSTID from dbo.fn_GetCustomers(@USERID))
and invoicemst.iINV_COMPID in (select value from dbo.fn_GetCompanies(@companyids,',',@USERID)) and
((CONVERT(datetime,[dtinv_date]) >= @fromdate and CONVERT(datetime,[dtinv_date]) <= @todate and binv_cancel = 0) or
(CONVERT(datetime,[dtinv_bl]) >= @fromdate and CONVERT(datetime,[dtinv_bl]) <= @todate and [dtinv_bl] is not null))
and bINV_free=0)
GROUP BY iORD_CUSTID,dCURR_RATE
,iord_compid
) as ww
group by CUSTOMER,iord_custid
END
October 26, 2010 at 4:28 am
You should really put your question in the text rather than in the heading,
however i would start by comparing the execution plans between the two servers
most likely the statistics are out of date on 2008..
July 6, 2011 at 8:26 am
Was there any fix for this?
We have a script running on SQL 2008 that takes 11 hours to run - this only took 1.5 hours to run on SQL 2000!!!
The script is part of an ETL job for a datawarehouse - any suggestions?
V. annoying - I'm considering reverting back to SQL 2000!!
July 6, 2011 at 8:44 am
there is no real magic fix for these sorts of problems, but i would start by updating the statistics and having a look at the execution plan for the query
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply