October 7, 2008 at 12:30 am
Hello,
Currently I am calling my SP of SQL 2005 using Asp.NET 2.0.
SP is based on more than 12 selection criteria, so I am firstly creating whole Query in one string and then executing. Also there are some reports in which I have used Cursors which are necessory to me fetch the data.(for cross tab reports).
I have set the CommandTimeout more than 50,
Currently SP execution taking time more than 80 to 90 seconds,
When i call it through Asp.NET it is giving Time out error .
NOTE : Data varies.
Plz. plz. suggest
Warm Regards
October 7, 2008 at 12:57 am
My first suggestion would be to optimise the proc. Replace those cursors with set-based code that should run a lot faster. If you're doing cross tabs, look up the PIVOT keyword in SQL 2005.
If you want some help with that, post the proc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 1:02 am
Hi Gail
Tnx. for very fast reply.
Can u explain me in brief?
Regards
Yogita
October 7, 2008 at 1:05 am
Most of times, you can replace a cursor/row-by-row processing with batch processing. Depending on the output you are expecting, and the structure of the source data, the apporach/code may be different. So I would suggest you post the script of your tables and explain the output you are trying to generate from the source data.
.
October 7, 2008 at 1:31 am
Hello SSC Veteran
I am Executing Application using local host of .NET framework.
SQL Server 2005 Developer Edition of 60 GB Data Size.
here is my SP : (This is not for Cross-tab style report)
------8<-------------------8<---------------------------8<----------
ALTER Procedure [dbo].[RptProduct_Wise_Detailed_Report]
@intFilterLocation as int,--//By Country,Region,Depot,Area,SubArea,Route
@strLocationCode as varchar(max),--//Location code
@strCustomerTypeCode as varchar(max),--//Customer Categorycode or Group code
@intCustomerTypeBy as int,--//By Customercategory,By Customer Group
@strCustomerIDs as varchar(max),--//Customer IDs
@intFilterCategory as int,--//Major,submajor....
@strCategoryCode as varchar(max),--//Item categorycode
@intReportType as int,--//Value,Cases etc...
@intReportStructure as int,--//Sumary ,Detail...
@intRptPeriod as int,--//Weeekly,Daterange....
@strdtFromDate as varchar(30),--//fromdate
@strdtToDate as varchar(30),--//todate
@intSortBy as int,--//Data Sequence,Item Code
@intOrderBy as int,--//Asc,Desc
@intGroupBy as int,--//Group by Dynamic Column
@intReportBy as int--//Report by (Report pattern as per dynamic col)
As
SET NOCOUNT ON;
Begin
--- NOTE : In this report Location is used in GroupBy i.e as Dynamic Column
Declare @strQuery varchar(max)
Declare @strQuery1 varchar(max)
Declare @strQuery2 varchar(max)
Declare @strFromQuery1 varchar(max)
Declare @strFromQuery2 varchar(max)
Declare @strCommon varchar(max)
Declare @strWhere varchar(max)
Declare @strFilterQuery varchar(max)
Declare @strFilterQuery2 varchar(max)
Declare @WeekStartDate varchar(20)
Declare @WeekEndDate varchar(20)
Declare @strGroupBy varchar(2000)
Declare @ISSUED decimal(18,3)
Declare @InventorySOLD decimal
Declare @EXPIRED decimal
Declare @DAMAGE decimal
Declare @PROMO decimal
Declare @RENT decimal
DECLARE @strInsert varchar(3000)
Declare @ItemGroupCode numeric
Declare @ActualItemCode numeric
DECLARE @CountryCode numeric,@RegionMstCode numeric,@DepotCode numeric,@AreaCode numeric
DECLARE @SubAreaCode numeric,@RouteCode numeric,@Column1Code numeric,@Column2Code numeric,@Column3Code numeric
declare @UnitsPerCase numeric, @LiterPerUnit decimal(18,2)
DECLARE @strSubQry varchar(max)
DECLARE @strSubQry2 varchar(max)
--DECLARE @strGroupby varchar(max)
DECLARE @strSubFrom varchar(max)
DECLARE @strOrderBy varchar(max)
SET @strInsert = ''
SET @strSubQry =''
SET @strSubQry2 =''
SET @strGroupby =''
SET @strSubFrom = ''
SET @strOrderBy = ''
Create table #tblProductDetail(CountryCode numeric default((0)),CountryName varchar(100),RegionMstCode numeric default((0)),RegionMstName varchar(100),DepotCode numeric default((0)),DepotName varchar(100),AreaCode numeric default((0)),AreaName varchar(100),SubAreaCode numeric default((0)), SubAreaName varchar(100),RouteCode numeric,RouteName varchar(100)
,Column1Code numeric default((0)),Column1Descrp varchar(100),Column2Code numeric default((0)),Column2Descrp varchar(100),Column3Code numeric default((0)),Column3Descrp varchar(100),UnitsPerCase numeric,LiterPerUnit decimal(18,2),
ISSUED decimal(18,3),SOLD decimal(18,3) default ((0)),EXPIRED decimal(18,3),
DAMAGE decimal(18,3),PROMO decimal(18,3),RENT decimal(18,3),SOLDAMT decimal(18,3) default ((0)))
--Column1Code,0 as Column1Descrp
set @strInsert = 'Insert into #tblProductDetail(CountryCode,CountryName,RegionMstCode,RegionMstName,DepotCode,DepotName,AreaCode,AreaName,SubAreaCode,SubAreaName,RouteCode,RouteName,Column1Code,Column1Descrp,Column2Code,Column2Descrp,Column3Code,Column3Descrp,UnitsPerCase,LiterPerUnit,SOLD,EXPIRED,DAMAGE,PROMO,RENT,SOLDAMT)'
SET @strCommon =''
Set @strFromQuery1 = ' FROM InventoryTransactionHeader INNER JOIN
InventorySummaryDetail ON InventoryTransactionHeader.InventoryKey = InventorySummaryDetail.InventoryKey INNER JOIN
ItemMaster ON InventorySummaryDetail.ItemCode = ItemMaster.ActualItemCode INNER JOIN
ItemGroup ON ItemMaster.ItemGroupCode = ItemGroup.ItemGroupCode INNER JOIN
SubMajorCategory ON ItemGroup.SubMajorCategoryCode = SubMajorCategory.SubMajorCategoryCode INNER JOIN
MajorCategory ON SubMajorCategory.MajorCategoryCode = MajorCategory.MajorCategoryCode INNER JOIN
CompanyGroup ON CompanyGroup.CompanyGroupCode = MajorCategory.CompanyGroupCode INNER JOIN
RouteMaster ON InventoryTransactionHeader.RouteCode = RouteMaster.RouteCode INNER JOIN
SubAreaMaster ON RouteMaster.SubAreaCode = SubAreaMaster.SubAreaCode INNER JOIN
AreaMaster ON SubAreaMaster.AreaCode = AreaMaster.AreaCode INNER JOIN
DepotMaster ON AreaMaster.DepotCode = DepotMaster.DepotCode INNER JOIN
RegionMaster ON DepotMaster.RegionMstCode = RegionMaster.RegionMstCode INNER JOIN
Country ON RegionMaster.CountryCode = Country.CountryCode INNER JOIN
CurrencyMaster ON Country.CurrencyCode = CurrencyMaster.CurrencyCode AND InventoryTransactionHeader.TransactionType = 3'
Set @strFromQuery2 = ' FROM InvoiceHeader INNER JOIN
InvoiceDetail ON InvoiceHeader.TransactionKey = InvoiceDetail.TransactionKey INNER JOIN
ItemMaster ON InvoiceDetail.ItemCode = ItemMaster.ActualItemCode INNER JOIN
ItemGroup ON ItemMaster.ItemGroupCode = ItemGroup.ItemGroupCode INNER JOIN
SubMajorCategory ON ItemGroup.SubMajorCategoryCode = SubMajorCategory.SubMajorCategoryCode INNER JOIN
MajorCategory ON SubMajorCategory.MajorCategoryCode = MajorCategory.MajorCategoryCode INNER JOIN
CompanyGroup ON CompanyGroup.CompanyGroupCode = MajorCategory.CompanyGroupCode INNER JOIN
RouteMaster ON InvoiceHeader.RouteCode = RouteMaster.RouteCode INNER JOIN
SubAreaMaster ON RouteMaster.SubAreaCode = SubAreaMaster.SubAreaCode INNER JOIN
AreaMaster ON SubAreaMaster.AreaCode = AreaMaster.AreaCode INNER JOIN
DepotMaster ON AreaMaster.DepotCode = DepotMaster.DepotCode INNER JOIN
RegionMaster ON DepotMaster.RegionMstCode = RegionMaster.RegionMstCode INNER JOIN
Country ON RegionMaster.CountryCode = Country.CountryCode INNER JOIN
CustomerMaster ON InvoiceHeader.CustomerCode = CustomerMaster.CustomerCode INNER JOIN
CategoryMaster ON CustomerMaster.CustomerCategory = CategoryMaster.CategoryId '
if(@intReportType = 1) -- By Value
Begin
SET @strQuery1 =' sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) - (Sum((InvoiceDetail.ReturnQty) * InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) + Sum((ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0))* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode))) as SOLD,
sum(ISNULL(InvoiceDetail.ExpiryQty,0)* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) as EXPIRED,
sum(InvoiceDetail.DamagedQty* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) as DAMAGE ,
sum (((ISNULL(InvoiceDetail.FreeSampleQty,0)-(ISNULL(InvoiceDetail.ManualFreeQty,0)+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)))+ISNULL(InvoiceDetail.ManualFreeQty,0)-ISNULL(InvoiceDetail.ReturnFreeQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) as PROMO,
sum((ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) as RENT,
sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) - (Sum((ISNULL(InvoiceDetail.ReturnQty,0)) * InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) + Sum((ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0))* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode))) as SOLDAMT '
--SET @strQuery2 =' Select ItemGroup.ItemGroupCode,ItemMaster.ActualItemCode,Sum((InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)*InventorySummaryDetail.StdSalesPrice * [dbo].[udf_GetExchangeRate](1,InventorySummaryDetail.CurrencyCode)) as ISSUED '
SET @strQuery2 =' Sum((InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)*InventorySummaryDetail.StdSalesPrice * [dbo].[udf_GetExchangeRate](1,InventorySummaryDetail.CurrencyCode)) as ISSUED '
End
if(@intReportType = 2) --By Liters
Begin
SET @strQuery1 =' (sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0))-(ISNULL(InvoiceDetail.ReturnQty,0)+ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0)))) as SOLD,
(sum((ISNULL(InvoiceDetail.ExpiryQty,0))* ItemMaster.LiterPerUnit)) as EXPIRED,
(sum((ISNULL(InvoiceDetail.DamagedQty,0))* ItemMaster.LiterPerUnit)) as DAMAGE ,
(sum((ISNULL(InvoiceDetail.FreeSampleQty,0)-(ISNULL(InvoiceDetail.ManualFreeQty,0)+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)))+(ISNULL(InvoiceDetail.ManualFreeQty,0))-(ISNULL(InvoiceDetail.ReturnFreeQty,0)) *ItemMaster.LiterPerUnit))as PROMO,
(sum((ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * ItemMaster.LiterPerUnit)) as RENT,
sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) - (Sum((ISNULL(InvoiceDetail.ReturnQty,0)) * InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) + Sum((ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0))* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode))) as SOLDAMT '
--SET @strQuery2 ='Select ItemGroup.ItemGroupCode,ItemMaster.ActualItemCode,(Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)*ItemMaster.LiterPerUnit) as ISSUED '
SET @strQuery2 =' (Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)*ItemMaster.LiterPerUnit) as ISSUED '
End
if(@intReportType = 3) -- By Cases
Begin
SET @strQuery1 =' (sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0))-(InvoiceDetail.ReturnQty+InvoiceDetail.ExpiryQty + InvoiceDetail.DamagedQty))) as SOLD,
(sum(ISNULL(InvoiceDetail.ExpiryQty,0))) as EXPIRED,
sum(ISNULL(InvoiceDetail.DamagedQty,0)) as DAMAGE ,
sum((ISNULL(InvoiceDetail.FreeSampleQty,0)-(ISNULL(InvoiceDetail.ManualFreeQty,0)+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)))+(ISNULL(InvoiceDetail.ManualFreeQty,0))-(ISNULL(InvoiceDetail.ReturnFreeQty,0)))as PROMO,
(sum(ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0))) as RENT,
sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) - (Sum((ISNULL(InvoiceDetail.ReturnQty,0)) * InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) + Sum((ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0))* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode))) as SOLDAMT '
--SET @strQuery2 ='Select ItemGroup.ItemGroupCode,ItemMaster.ActualItemCode,(Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)) as ISSUED '
SET @strQuery2 =' (Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)) as ISSUED '
End
if(@intReportType = 4) -- By Pieces
Begin
SET @strQuery1 =' (sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0))-(InvoiceDetail.ReturnQty+InvoiceDetail.ExpiryQty + ISNULL(InvoiceDetail.DamagedQty,0)))) as SOLD,
sum(ISNULL(InvoiceDetail.ExpiryQty,0)) as EXPIRED,
sum(ISNULL(InvoiceDetail.DamagedQty,0)) as DAMAGE ,
sum((ISNULL(InvoiceDetail.FreeSampleQty,0)-(ISNULL(InvoiceDetail.ManualFreeQty,0)+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)))+(ISNULL(InvoiceDetail.ManualFreeQty,0))-(ISNULL(InvoiceDetail.ReturnFreeQty,0)))as PROMO,
sum(ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) as RENT,
sum((InvoiceDetail.SalesQty+ISNULL(InvoiceDetail.RebateRentQty,0)+ISNULL(InvoiceDetail.FixedRentQty,0)) * InvoiceDetail.SalesPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) - (Sum((ISNULL(InvoiceDetail.ReturnQty,0)) * InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode)) + Sum((ISNULL(InvoiceDetail.ExpiryQty,0) + ISNULL(InvoiceDetail.DamagedQty,0))* InvoiceDetail.ReturnPrice * [dbo].[udf_GetExchangeRate](1,InvoiceDetail.CurrencyCode))) as SOLDAMT '
--SET @strQuery2 ='Select ItemGroup.ItemGroupCode,ItemMaster.ActualItemCode,(Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)) as ISSUED '
SET @strQuery2 =' (Sum(InventorySummaryDetail.LoadQty + InventorySummaryDetail.LoadAddQty -InventorySummaryDetail.LoadCutQty -InventorySummaryDetail.UnloadQty)) as ISSUED '
End
if(@intReportType = 6 )-- By Quanity
Begin
SET @strQuery1 =' sum(InvoiceDetail.SalesQty) as SOLD, sum(InvoiceDetail.DamagedQty) as EXPIRED, 0 as DAMAGE,
sum(InvoiceDetail.PromoQty) as PROMO, 0 as RENT,sum(((InvoiceDetail.SalesQty /ItemMaster.UnitsPerCase )* InvoiceDetail.SalesCasePrice) + ((InvoiceDetail.SalesQty - (ItemMaster.UnitsPerCase * (InvoiceDetail.SalesQty / ItemMaster.UnitsPerCase))) * InvoiceDetail.SalesPrice)) as SOLDAMT,ItemMaster.ItemShortDescription '
--SET @strQuery2 ='Select ItemGroup.ItemGroupCode,ItemMaster.ActualItemCode,sum(InvoiceDetail.SalesQty) as SOLD'
SET @strQuery2 =' sum(InvoiceDetail.SalesQty) as SOLD'
End
SET @strFilterQuery =''
if(@intGroupBy = 1) -- Sub total Country
Begin
--SET @strSubQry = @strSubQry + ' SELECT Country.CountryCode,Country.CountryName'
SET @strSubQry = @strSubQry + 'SELECT Country.CountryCode,Country.CountryName, 0 as RegionMstCode,0 as RegionMstName,0 as DepotCode,0 as DepotName,0 as AreaCode,0 as AreaName,0 as SubAreaCode,0 as SubAreaName, 0 as RouteCode,0 as RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT Country.CountryCode,0 as RegionMstCode,0 as DepotCode,0 as AreaCode,0 as SubAreaCode,0 as RouteCode'
SET @strGroupBy = @strGroupBy + ' Group By Country.CountryCode,Country.CountryName'
End
if(@intGroupBy = 2) -- Sub total Region
Begin
--SET @strSubQry = @strSubQry + 'SELECT RegionMaster.RegionMstCode,RegionMaster.RegionMstName'
SET @strSubQry = @strSubQry + 'SELECT 0 as CountryCode,0 as CountryName, RegionMaster.RegionMstCode,RegionMaster.RegionMstName,0 as DepotCode,0 as DepotName,0 as AreaCode,0 as AreaName,0 as SubAreaCode,0 as SubAreaName, 0 as RouteCode,0 as RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT 0 as CountryCode,RegionMaster.RegionMstCode,0 as DepotCode,0 as AreaCode,0 as SubAreaCode,0 as RouteCode'
SET @strGroupBy = @strGroupBy + 'Group By RegionMaster.RegionMstCode,RegionMaster.RegionMstName'
End
if(@intGroupBy = 3) -- Sub total Depot
Begin
--SET @strSubQry = @strSubQry + 'SELECT DepotMaster.DepotCode,DepotMaster.DepotName'
SET @strSubQry = @strSubQry + 'SELECT 0 as CountryCode,0 as CountryName, 0 as RegionMstCode,0 as RegionMstName,DepotMaster.DepotCode,DepotMaster.DepotName,0 as AreaCode,0 as AreaName,0 as SubAreaCode,0 as SubAreaName, 0 as RouteCode,0 as RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT 0 as CountryCode, 0 as RegionMstCode,DepotMaster.DepotCode,0 as AreaCode,0 as SubAreaCode, 0 as RouteCode'
SET @strGroupBy = @strGroupBy + 'Group By DepotMaster.DepotCode,DepotMaster.DepotName'
End
if(@intGroupBy = 4) -- Sub total Area
Begin
--SET @strSubQry = @strSubQry + 'SELECT AreaMaster.AreaCode,AreaMaster.AreaName'
SET @strSubQry = @strSubQry + 'SELECT 0 as CountryCode,0 as CountryName, 0 as RegionMstCode,0 as RegionMstName,0 as DepotCode,0 as DepotName,AreaMaster.AreaCode,AreaMaster.AreaName,0 as SubAreaCode,0 as SubAreaName, 0 as RouteCode,0 as RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT 0 as CountryCode,0 as RegionMstCode,0 as DepotCode,AreaMaster.AreaCode,0 as SubAreaCode,0 as RouteCode'
SET @strGroupBy = @strGroupBy + 'Group By AreaMaster.AreaCode,AreaMaster.AreaName'
End
if(@intGroupBy = 5) -- Sub total SubArea
Begin
--SET @strSubQry = @strSubQry + 'SELECT SubAreaMaster.SubAreaCode,SubAreaMaster.SubAreaName'
SET @strSubQry = @strSubQry + 'SELECT 0 as CountryCode,0 as CountryName, 0 as RegionMstCode,0 as RegionMstName,0 as DepotCode,0 as DepotName,0 as AreaCode,0 as AreaName,SubAreaMaster.SubAreaCode,SubAreaMaster.SubAreaName, 0 as RouteCode,0 as RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT 0 as CountryCode,0 as RegionMstCode,0 as DepotCode,0 as AreaCode,SubAreaMaster.SubAreaCode,0 as RouteCode'
SET @strGroupBy = @strGroupBy + 'Group By SubAreaMaster.SubAreaCode,SubAreaMaster.SubAreaName'
End
if(@intGroupBy = 6) --Sub total by route
Begin
--SET @strSubQry = @strSubQry + 'SELECT RouteMaster.RouteCode,RouteMaster.RouteName'
SET @strSubQry = @strSubQry + 'SELECT 0 as CountryCode,0 as CountryName, 0 as RegionMstCode,0 as RegionMstName,0 as DepotCode,0 as DepotName,0 as AreaCode,0 as AreaName,0 as SubAreaCode,0 as SubAreaName, RouteMaster.RouteCode,RouteMaster.RouteName'
SET @strSubQry2 = @strSubQry2 + 'SELECT 0 as CountryCode,0 as RegionMstCode,0 as DepotCode,0 as AreaCode,0 as SubAreaCode,RouteMaster.RouteCode'
SET @strGroupBy = @strGroupBy + 'Group By RouteMaster.RouteCode,RouteMaster.RouteName'
End
-- Report By option
if(@intReportBy = 6) -- ByItems
Begin
--SET @strSubQry = @strSubQry + ',ItemMaster.ActualItemCode,ItemMaster.ItemShortDescription,ItemMaster.UnitsPerCase, ItemMaster.LiterPerUnit,'
SET @strSubQry = @strSubQry + ',SubMajorCategory.SubMajorCategoryCode as Column1Code,SubMajorCategory.EnDescription as Column1Descrp,
ItemGroup.ItemGroupCode as Column2Code,ItemGroup.ItemGroupName as Column2Descrp,
ItemMaster.ActualItemCode as Column3Code,ItemMaster.ItemShortDescription as Column3Descrp,ItemMaster.UnitsPerCase, ItemMaster.LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',SubMajorCategory.SubMajorCategoryCode as Column1Code,
ItemGroup.ItemGroupCode as Column2Code,
ItemMaster.ActualItemCode as Column3Code,ItemMaster.UnitsPerCase, ItemMaster.LiterPerUnit,'
SET @strGroupBy = @strGroupBy + ',SubMajorCategory.SubMajorCategoryCode,SubMajorCategory.EnDescription,
ItemGroup.ItemGroupCode ,ItemGroup.ItemGroupName,ItemMaster.ActualItemCode,ItemMaster.ItemShortDescription,ItemMaster.UnitsPerCase, ItemMaster.LiterPerUnit'
End
if(@intReportBy = 5) -- ByParentItems
Begin
SET @strSubQry = @strSubQry + ',SubMajorCategory_1.SubMajorCategoryCode as Column1Code,SubMajorCategory_1.EnDescription as Column1Descrp,
ItemGroup_1.ItemGroupCode as Column2Code,ItemGroup_1.ItemGroupName as Column2Descrp,
ItemMaster_1.ActualItemCode as Column3Code,ItemMaster_1.ItemShortDescription as Column3Descrp,ItemMaster_1.UnitsPerCase, ItemMaster_1.LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',SubMajorCategory_1.SubMajorCategoryCode as Column1Code,
ItemGroup_1.ItemGroupCode as Column2Code,
ItemMaster_1.ActualItemCode as Column3Code,ItemMaster_1.UnitsPerCase, ItemMaster_1.LiterPerUnit,'
SET @strSubFrom = ' INNER JOIN ItemMaster AS ItemMaster_1 ON ItemMaster.ParentItemCode = ItemMaster_1.ActualItemCode
INNER JOIN ItemGroup AS ItemGroup_1 ON ItemMaster_1.ItemGroupCode = ItemGroup_1.ItemGroupCode
INNER JOIN SubMajorCategory AS SubMajorCategory_1 ON ItemGroup_1.SubMajorCategoryCode = SubMajorCategory_1.SubMajorCategoryCode'
SET @strGroupBy = @strGroupBy + ',SubMajorCategory_1.SubMajorCategoryCode,SubMajorCategory_1.EnDescription,
ItemGroup_1.ItemGroupCode ,ItemGroup_1.ItemGroupName,ItemMaster_1.ActualItemCode,ItemMaster_1.ItemShortDescription,ItemMaster_1.UnitsPerCase, ItemMaster_1.LiterPerUnit'
End
if(@intReportBy = 4) -- ByItemGroup
Begin
SET @strSubQry = @strSubQry + ',MajorCategory.MajorCategoryCode as Column1Code,MajorCategory.EnDescription as Column1Descrp,
SubMajorCategory.SubMajorCategoryCode as Column2Code,SubMajorCategory.EnDescription as Column2Descrp,
ItemGroup.ItemGroupCode as Column3Code,ItemGroup.ItemGroupName as Column3Descrp,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',MajorCategory.MajorCategoryCode as Column1Code,
SubMajorCategory.SubMajorCategoryCode as Column2Code,
ItemGroup.ItemGroupCode as Column3Code,0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strGroupBy = @strGroupBy + ',MajorCategory.MajorCategoryCode,MajorCategory.EnDescription,
SubMajorCategory.SubMajorCategoryCode,SubMajorCategory.EnDescription,
ItemGroup.ItemGroupCode ,ItemGroup.ItemGroupName'
End
if(@intReportBy = 3) -- BySubMajorCategory
Begin
SET @strSubQry = @strSubQry + ',CompanyGroup.CompanyGroupCode as Column1Code,CompanyGroup.EnglishDescription as Column1Descrp,
MajorCategory.MajorCategoryCode as Column2Code,MajorCategory.EnDescription as Column2Descrp,
SubMajorCategory.SubMajorCategoryCode as Column3Code,SubMajorCategory.EnDescription as Column3Descrp,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',CompanyGroup.CompanyGroupCode as Column1Code,
MajorCategory.MajorCategoryCode as Column2Code,
SubMajorCategory.SubMajorCategoryCode as Column3Code,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strGroupBy = @strGroupBy + ',CompanyGroup.CompanyGroupCode,CompanyGroup.EnglishDescription,MajorCategory.MajorCategoryCode,MajorCategory.EnDescription,
SubMajorCategory.SubMajorCategoryCode,SubMajorCategory.EnDescription'
End
if(@intReportBy = 2) -- ByMajorCategory
Begin
SET @strSubQry = @strSubQry + ',0 as Column1Code,0 as Column1Descrp,
CompanyGroup.CompanyGroupCode as Column2Code,CompanyGroup.EnglishDescription as Column2Descrp,
MajorCategory.MajorCategoryCode as Column3Code,MajorCategory.EnDescription as Column3Descrp,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',0 as Column1Code,
CompanyGroup.CompanyGroupCode as Column2Code,
MajorCategory.MajorCategoryCode as Column3Code,0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strGroupBy = @strGroupBy + ',CompanyGroup.CompanyGroupCode,CompanyGroup.EnglishDescription,MajorCategory.MajorCategoryCode,MajorCategory.EnDescription'
End
if(@intReportBy = 1) -- ByProductCategory
Begin
SET @strSubQry = @strSubQry + ',0 as Column1Code,0 as Column1Descrp,
0 as Column2Code,0 as Column2Descrp,
CompanyGroup.CompanyGroupCode as Column3Code,CompanyGroup.EnglishDescription as Column3Descrp,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strSubQry2 = @strSubQry2 + ',0 as Column1Code,
0 as Column2Code,
CompanyGroup.CompanyGroupCode as Column3Code,
0 as UnitsPerCase, 0 as LiterPerUnit,'
SET @strGroupBy = @strGroupBy + ',CompanyGroup.CompanyGroupCode,CompanyGroup.EnglishDescription'
End
IF(@intFilterLocation = 1) -- For Country
BEGIN
SET @strFilterQuery =' Where Country.CountryCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where Country.CountryCode IN (' + @strLocationCode + ') '
END
IF(@intFilterLocation = 2) --For Region
BEGIN
SET @strFilterQuery =' Where RegionMaster.RegionMstCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where RegionMaster.RegionMstCode IN (' + @strLocationCode + ') '
END
IF(@intFilterLocation = 3) -- For Depot
BEGIN
SET @strFilterQuery =' Where DepotMaster.DepotCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where DepotMaster.DepotCode IN (' + @strLocationCode + ') '
END
IF(@intFilterLocation = 4) -- For Area
BEGIN
SET @strFilterQuery =' Where AreaMaster.AreaCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where AreaMaster.AreaCode IN (' + @strLocationCode + ') '
END
IF(@intFilterLocation = 5) -- For SubArea
BEGIN
SET @strFilterQuery =' Where SubAreaMaster.SubAreaCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where SubAreaMaster.SubAreaCode IN (' + @strLocationCode + ') '
END
IF(@intFilterLocation = 6) -- For Route
BEGIN
SET @strFilterQuery =' Where RouteMaster.RouteCode IN (' + @strLocationCode + ') '
SET @strFilterQuery2 =' Where RouteMaster.RouteCode IN (' + @strLocationCode + ') '
END
-----Item Category
IF(@intFilterCategory = 1) -- Parent Item Group
BEGIN
SET @strFilterQuery= @strFilterQuery + ' AND MajorCategory.CompanyGroupCode IN (' + @strCategoryCode + ')'
SET @strFilterQuery2= @strFilterQuery2 + ' AND MajorCategory.CompanyGroupCode IN (' + @strCategoryCode + ')'
END
IF(@intFilterCategory = 2) --MajorCategory
BEGIN
SET @strFilterQuery = @strFilterQuery + ' AND MajorCategory.MajorCategoryCode IN(' + @strCategoryCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND MajorCategory.MajorCategoryCode IN(' + @strCategoryCode + ')'
END
IF(@intFilterCategory = 3) --SubMajor
BEGIN
SET @strFilterQuery = @strFilterQuery + ' AND SubMajorCategory.SubMajorCategoryCode IN(' + @strCategoryCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND SubMajorCategory.SubMajorCategoryCode IN(' + @strCategoryCode + ')'
END
IF(@intFilterCategory = 4)--ItemGroup
BEGIN
SET @strFilterQuery = @strFilterQuery + ' AND ItemGroup.ItemGroupCode IN(' + @strCategoryCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND ItemGroup.ItemGroupCode IN(' + @strCategoryCode + ')'
END
IF(@intFilterCategory = 5) -- Parent Item
BEGIN
SET @strFilterQuery=@strFilterQuery + ' AND ItemMaster.ParentItemCode IN(' + @strCategoryCode + ')'
SET @strFilterQuery2=@strFilterQuery2 + ' AND ItemMaster.ParentItemCode IN(' + @strCategoryCode + ')'
END
IF(@intFilterCategory = 6) -- Item
BEGIN
SET @strFilterQuery = @strFilterQuery + ' AND ItemMaster.ActualItemCode IN(' + @strCategoryCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND ItemMaster.ActualItemCode IN(' + @strCategoryCode + ')'
END
---Customers
---if(@strCustomerIDs <>'')
--Begin
--SET @strFilterQuery2 = @strFilterQuery2 + ' AND InvoiceHeader.CustomerCode IN (' + @strCustomerIDs + ')'
--End
---Customer Type
IF(@intCustomerTypeBy = 1) --//Customer category
Begin
--//SET @strFilterQuery = @strFilterQuery + ' AND CustomerMaster.CustomerCategory IN (' + @strCustomerTypeCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND CustomerMaster.CustomerCategory IN (' + @strCustomerTypeCode + ')'
End
ELSE IF(@intCustomerTypeBy = 2) --Customer Group
Begin
--//SET @strFilterQuery = @strFilterQuery + ' AND CustomerMaster.GroupCode IN (' + @strCustomerTypeCode + ')'
SET @strFilterQuery2 = @strFilterQuery2 + ' AND CustomerMaster.GroupCode IN (' + @strCustomerTypeCode + ')'
End
---Period
if(@intRptPeriod = 1) -- Date Range
Begin
SET @strFilterQuery = @strFilterQuery + ' AND InventoryTransactionHeader.TransactionDate between ''' + @strdtFromDate + ''' AND '''+ @strdtToDate + ''''
SET @strFilterQuery2 = @strFilterQuery2 + ' AND InvoiceHeader.TransactionDate between ''' + @strdtFromDate + ''' AND '''+ @strdtToDate + ''''
End
if(@intRptPeriod = 2) --Week Range
Begin
SET @WeekStartDate = (Select Convert(varchar,WeekStartDate) from SalesCalender where SalesCalender.WeekNumber = Convert(int,@strdtToDate) and year(SalesCalender.WeekStartDate) = Convert(int,@strdtFromDate) and year(SalesCalender.WeekEndDate) = Convert(int,@strdtFromDate))
SET @WeekEndDate =(Select Convert(varchar,WeekEndDate) from SalesCalender where SalesCalender.WeekNumber = Convert(int,@strdtToDate) and year(SalesCalender.WeekStartDate) = Convert(int,@strdtFromDate) and year(SalesCalender.WeekEndDate) = Convert(int,@strdtFromDate))
IF(@WeekStartDate <> '' AND @WeekEndDate <> '')
Begin
SET @strdtFromDate = @WeekStartDate
SET @strdtToDate = @WeekEndDate
SET @strFilterQuery = @strFilterQuery + ' AND InventoryTransactionHeader.TransactionDate between ''' + @WeekStartDate + ''' AND '''+ @WeekEndDate + ''''
SET @strFilterQuery2 = @strFilterQuery2 + ' AND InvoiceHeader.TransactionDate between ''' + @WeekStartDate + ''' AND '''+ @WeekEndDate + ''''
End
End
if(@intRptPeriod = 3)--Month Range
Begin
print @strdtToDate
SET @strFilterQuery = @strFilterQuery + ' AND (year(InventoryTransactionHeader.TransactionDate) between ' + convert(varchar,year(Convert(DateTime,@strdtFromDate))) + ' AND '+ Convert(varchar,year(Convert(DateTime,@strdtToDate))) + ')
AND Month(InventoryTransactionHeader.TransactionDate) between ' + Convert(varchar,Month(Convert(DateTime,@strdtFromDate))) + ' AND ' + Convert(varchar,Month(Convert(DateTime,@strdtToDate)))
SET @strFilterQuery2 = @strFilterQuery2 + ' AND (year(InvoiceHeader.TransactionDate) between '+ Convert(varchar,year(Convert(DateTime,@strdtFromDate))) + ' AND '+ Convert(varchar,year(Convert(DateTime,@strdtToDate))) + ')
AND Month(InvoiceHeader.TransactionDate) between ' + Convert(varchar,Month(Convert(DateTime,@strdtFromDate))) + ' AND ' + Convert(varchar,Month(Convert(DateTime,@strdtToDate)))
End
if(@intRptPeriod = 4) --Year Range
Begin
SET @strFilterQuery = @strFilterQuery + ' AND year(InventoryTransactionHeader.TransactionDate) between ''' + @strdtFromDate + ''' AND ''' + @strdtToDate + ''''
SET @strFilterQuery2 = @strFilterQuery2 + ' AND year(InvoiceHeader.TransactionDate) between ''' + @strdtFromDate + ''' AND ''' + @strdtToDate + ''''
End
SET @strQuery1 = @strInsert + @strSubQry + @strQuery1 + @strFromQuery2 + @strSubFrom + @strFilterQuery2 +@strGroupBy
print('>> 1 ' + @strQuery1)
exec(@strQuery1)
--Set @strQuery2 = @strQuery2 + @strFromQuery1 + @strFilterQuery
SET @strQuery2 = @strSubQry2 + @strQuery2 + @strFromQuery1 + @strSubFrom + @strFilterQuery +@strGroupBy
print('>> 2 ' + @strQuery2)
Exec('Declare SubCursor Cursor For ' + @strQuery2)
OPEN SubCursor
FETCH NEXT FROM SubCursor
INTO @CountryCode,@RegionMstCode,@DepotCode,@AreaCode,@SubAreaCode,@RouteCode,@Column1Code,@Column2Code,@Column3Code,@UnitsPerCase,@LiterPerUnit,@ISSUED
While (@@Fetch_Status =0)
Begin
UPDATE #tblProductDetail
Set ISSUED =@ISSUED
Where CountryCode = @CountryCode AND
RegionMstCode = @RegionMstCode AND
DepotCode = @DepotCode AND
AreaCode = @AreaCode AND
SubAreaCode = @SubAreaCode AND
RouteCode = @RouteCode AND
Column1Code = @Column1Code AND
Column2Code = @Column2Code AND Column3Code = @Column3Code
FETCH NEXT FROM SubCursor
INTO @CountryCode,@RegionMstCode,@DepotCode,@AreaCode,@SubAreaCode,@RouteCode,@Column1Code,@Column2Code,@Column3Code,@UnitsPerCase,@LiterPerUnit,@ISSUED
End
Close SubCursor
Deallocate SubCursor
SELECT CountryCode,CountryName,RegionMstCode,RegionMstName,DepotCode,DepotName,AreaCode,AreaName ,SubAreaCode, SubAreaName ,RouteCode ,RouteName
,Column1Code ,Column1Descrp ,Column2Code ,Column2Descrp,Column3Code ,Column3Descrp ,UnitsPerCase ,LiterPerUnit ,
ISSUED ,SOLD ,EXPIRED ,DAMAGE ,PROMO ,RENT ,SOLDAMT from #tblProductDetail
order by CountryCode,RegionMstCode,DepotCode,AreaCode,SubAreaCode,RouteCode,Column1Code,Column2Code,Column3Code
DROP TAble #tblProductDetail
End
------8<-------------------8<---------------------------8<----------
Regards
October 7, 2008 at 1:35 am
IntellectYog (10/7/2008)
Hi GailTnx. for very fast reply.
Can u explain me in brief?
What do you want explaining?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 1:59 am
Hi Gail !
I need more explanation regarding your statement
"Replace those cursors with set-based code that should run a lot faster"
for your reference, above I have pasted my SP too, if any suggetions over it also plz.
Tnx,
Regards
October 7, 2008 at 7:07 am
What Gail means is that there is no reason to use a cursor to do a "Cross Tab" report. Now, I'm not going to troubleshoot the size stored procedure you just posted especially for free :), but I do recommend you simply rewrite the thing using the techniques found at the following URL...
http://www.sqlservercentral.com/articles/T-SQL/63681/
Done properly, such Cross-Tabs or "Pivots" are lightning fast especially if you use the "pre-aggregation" method described in the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2008 at 10:45 pm
Hi Jeff Moden,
Tnx. for the reply. I will definetly follow the URL u have given.
n it's my pleasure that may my SP help others incase .. 🙂
Regards
Yogita
October 9, 2008 at 6:37 am
I haven't look in detail at what your dynamic queries are doing, but by looking at the bottom of your proc I believe that you could easily convert this to a set-based update.
Instead of generating a Cursor for @strQuery2, simply join in #tblProductDetail and do your update directly. Since @strQuery2 has SUMs in it, one way to do that is as follows:
UPDATE pd
SET ISSUED = x.ISSUED
FROM #tblProductDetail pd
INNER JOIN ( --the entire SELECT-FROM-WHERE-GROUPBY from @strQuery2 goes here-- ) x
ON pd.CountryCode = x.CountryCode AND
pd.RegionMstCode = x.RegionMstCode AND
pd.DepotCode = x.DepotCode AND
pd.AreaCode = x.AreaCode AND
pd.SubAreaCode = x.SubAreaCode AND
pd.RouteCode = x.RouteCode AND
pd.Column1Code = x.Column1Code AND
pd.Column2Code = x.Column2Code AND
pd.Column3Code = x.Column3Code
You could also use @strQuery2 to load another temp table and then reference that temp table where the --*-- is.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply