Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 How to improve query performance

Author  Topic 

balaram19
Starting Member

9 Posts
Posted - 2012-03-28 : 03:27:24
Hi,

Iam trying to execute one query. where query inclued many tables (which has primary key and foreign key's). Its taking 5 min 38 sec's long time to execute...

I found the missing indexes by using dmv query. what I have to do next... to improve the performance of query


thankxx

balaram

ivbalaram

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2012-03-28 : 12:00:50
apply the indexes and see effect on query by analysing execution plan. See if you can do further tuning by rewriting logic to make use of other existing indexes and also make sure you work with only required subset of data by applying required filters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2012-03-28 : 12:53:38
Just make sure that you have no existing indexes that can be trivially widened (add a key column or two at the end, add an include column or two) instead of a new one added.

Don't want to create new indexes that aren't necessary.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2012-03-28 : 13:11:33
It would help if you posted the Query and the DDL (including keys and constraints and indexes) so we can see what you are doing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/
Go to Top of Page

balaram19
Starting Member

9 Posts
Posted - 2012-03-30 : 03:25:18
Iam using 12 tables in my query.. Iam giving the details of all tables with keys and indexes.. and executing query also.. pls gimme suggetion to improve query performance..

1. tbl_audit items
a)FK__tbl_Audit__fld_i__4C82C9D6 b)DF__tbl_Audit__fld_b__0B5E4698(constraint)

2.tbl_customer master
a)PK_tbl_CustomerMaster b)NoDupes_LongName c)NoDupes_LongName(index) d)PK_tbl_CustomerMaster(clustered index)

3.tbl_inventory history
a)idx_tbl_InventoryHistory_fld_intInventoryID_fld_dtCreatedDate

4.tbl_inventory master
a)PK_tbl_InventoryMaster b)FK_tbl_InventoryMaster_tbl_InventoryAdjustments c)FK_tbl_InventoryMaster_tbl_UOM d)IX_tbl_InventoryMaster_fld_intCustomerID e)IX_tbl_InventoryMaster_fld_intSupplierID f)IX_tbl_InventoryMaster_fld_intTypeOfEquipment g)IX_tbl_InventoryMaster_fld_strBarCode h)IX_tbl_InventoryMaster_fld_strSerialNo i)IX_tbl_InventoryMaster_fld_strUPCCode g)PK_tbl_InventoryMaster(clustered)

5.tbl_issues and returns details
a)FK_tbl_IssuesAndReturnsDetails_tbl_DeliveryDocumentDetails b)FK_tbl_IssuesAndReturnsDetails_tbl_InventoryMaster c)FK_tbl_IssuesAndReturnsDetails_tbl_IssuesAndReturnsHeader

6)tbl_location master
a)PK_tbl_LocationMaster b)FK_tbl_LocationMaster_tbl_CustomerMaster c)FK_tbl_LocationMaster_tbl_LocationMaster d)FK_tbl_LocationMaster_tbl_po
e)ix_IndexName6 f)PK_tbl_LocationMaster(clustered)
6.tbl_order header
a)PK_tbl_OrderHeader b)FK_tbl_OrderHeader_tbl_CostCenters c)FK_tbl_OrderHeader_tbl_CustomerMaster d)FK_tbl_OrderHeader_tbl_SupplierMaster e)FK_tbl_OrderHeader_tbl_Users_CreatedBy f)FK_tbl_OrderHeader_tbl_Users_ModifyedBy g)IX_tbl_OrderHeader_fld_intCustomerID h)IX_tbl_OrderHeader_fld_intDeliveryLocationID i)IX_tbl_OrderHeader_fld_strStatus g)PK_tbl_OrderHeader(clustered)

7.tbl_order lines
a)PK_tbl_OrderLines b)FK_tbl_OrderLines_tbl_OrderHeader c)PK_tbl_OrderLines(clustered)

8.tbl_producthazardclass
a)PK_tbl_ProductHazardClass b)FK_tbl_ProductHazardClass_tbl_Users c)NoDupes_HazardClassName d)NoDupes_HazardClassName(unique,non-clustered index) e)PK_tbl_ProductHazardClass

9.tbl_region codes
a)PK_fld_strRegionCode(clustered)

10.tbl_supplier Master
a)PK_tbl_SupplierMaster b)NoDupes_SupplierName_CustomerID c)NoDupes_SupplierName_CustomerID(unique,non_clustered) d)PK_tbl_SupplierMaster(clustered)

11.tbl_user locations
a)FK_tbl_UserLocations_tbl_LocationMaster b)FK_tbl_UserLocations_tbl_Users c)idx_tbl_UserLocations_fld_intUserID_fld_intLocationID(clustered)

12.tbl_user suppliers
a)FK_tbl_UserSuppliers_tbl_SupplierMaster b)FK_tbl_UserSuppliers_tbl_Users

Executing query is::

Declare
/* @strCustomerIDs varchar(2000),
@strLocationIDs varchar(max),
@strPartNos varchar(max),
@strSupplierIDs varchar(2000),
@dtStartDate datetime,
@dtEndDate datetime,
@locationType varchar(10)*/
@strCustomerIDs VARCHAR(MAX),
@dtStartDate DATETIME,
@dtEndDate DATETIME,
@strSupplierIDs VARCHAR(100),
@strLocationIDs VARCHAR(MAX),
@strCurrentContentPartNos VARCHAR(MAX),
@strCustomerPartNos VARCHAR(MAX),
@intProductSizeIDs VARCHAR(MAX),
@strHazardClassIDs VARCHAR(MAX),
@intAuditFlag INT ,
@intOwnerTypes VARCHAR(500),
@strCuAccountNos VARCHAR(MAX),
@strBarCodes VARCHAR(1500),
@strSerialNos VARCHAR(1500),
@strLOTNumber VARCHAR(50),
--@strUPCCode VARCHAR(50),
@strAIMOrderNo VARCHAR(50),
@strSupplierOrderNo VARCHAR(50),
@deliveryDocNo varchar(200),
@dtCylinderExpirationDate DATETIME,
@dtAnalyticalDate DATETIME,
@dtHydrotestDate DATETIME,
@strStatus VARCHAR(75),
@intTypeOfEquipment VARCHAR(20),
@intThirdPartyOwner INT,
@strMSDSNo VARCHAR(50),
@dtAsOnDate DATETIME,
@intMultiplier INT,
@iUserID INT,
@noOfDaysGreaterthan int,
@noOfDays int,
@turnsRatio int,
@strGroupBy varchar(100);


set @dtStartDate = '1/Jan/2011'
--set @dtEndDate = '31/Dec/2011'
set @dtEndDate = '31/Mar/2011'

declare @datesTable TABLE
(
fld_dtAsOnDate datetime
)
--insert into @datesTable values('2/Jan/2011');
--insert into @datesTable values('3/Jan/2011');
declare @i int, @days int;
set @days = DATEDIFF(d, @dtStartDate, @dtEndDate);
set @i = 0;

while @i <= @days
begin
insert into @datesTable values(DATEADD(d, @i, @dtStartDate));
set @i = @i + 1;
end


set @iUserID = 1;
--set @strCustomerIDs = ',6,11,'
set @strCustomerIDs = ',';
select @strCustomerIDs = @strCustomerIDs + cast(fld_intCustomerID as varchar(10)) + ',' from tbl_CustomerMaster
where UPPER(fld_strLongName) like '%DOW%'
order by fld_intCustomerID;
--select @strCustomerIDs

set @strCustomerIDs = LTRIM(RTRIM(@strCustomerIDs));
if LEN(@strCustomerIDs) > 0
begin
if LEFT(@strCustomerIDs, 1) <> ','
set @strCustomerIDs = ',' + @strCustomerIDs;

if RIGHT(@strCustomerIDs, 1) <> ','
set @strCustomerIDs = @strCustomerIDs + ',';
end

set @strSupplierIDs = LTRIM(RTRIM(@strSupplierIDs));
IF ISNULL(@strSupplierIDs,'') = ''
BEGIN
set @strSupplierIDs = ',';
SELECT @strSupplierIDs = @strSupplierIDs + CAST(US.fld_intSupplierID AS VARCHAR(6)) + ','
FROM tbl_UserSuppliers US,tbl_SupplierMaster SM
WHERE SM.fld_intSupplierID = US.fld_intSupplierID
AND SM.fld_intCustomerID IN(select list_item from adm_fn_getlist(@strCustomerIDs)) AND SM.fld_bitActive = 1 AND US.fld_intUserID = @iUserID
order by SM.fld_intSupplierID
END
ELSE if LEN(@strSupplierIDs) > 0
begin
if LEFT(@strSupplierIDs, 1) <> ','
set @strSupplierIDs = ',' + @strSupplierIDs;

if RIGHT(@strSupplierIDs, 1) <> ','
set @strSupplierIDs = @strSupplierIDs + ',';
end

set @strLocationIDs = LTRIM(RTRIM(@strLocationIDs));
IF ISNULL(@strLocationIDs,'') = ''
BEGIN
set @strLocationIDs = ',';
SELECT @strLocationIDs = @strLocationIDs + CAST(UL.fld_intLocationID AS VARCHAR(6)) + ','
FROM tbl_UserLocations UL,tbl_LocationMaster LM
WHERE LM.fld_intLocationID=UL.fld_intLocationID
AND LM.fld_intCustomerID IN(select list_item from adm_fn_getlist(@strCustomerIDs)) AND LM.fld_bitActive=1 AND UL.fld_intUserID=@iUserID
order by LM.fld_intLocationID
END
ELSE IF LEN(@strLocationIDs) > 0
begin
if LEFT(@strLocationIDs, 1) <> ','
set @strLocationIDs = ',' + @strLocationIDs;

if RIGHT(@strLocationIDs, 1) <> ','
set @strLocationIDs = @strLocationIDs + ',';
end

set @intProductSizeIDs = LTRIM(RTRIM(@intProductSizeIDs));
if LEN(@intProductSizeIDs) > 0
begin
if LEFT(@intProductSizeIDs, 1) <> ','
set @intProductSizeIDs = ',' + @intProductSizeIDs;

if RIGHT(@intProductSizeIDs, 1) <> ','
set @intProductSizeIDs = @intProductSizeIDs + ',';
end

set @strHazardClassIDs = LTRIM(RTRIM(@strHazardClassIDs));
if LEN(@strHazardClassIDs) > 0
begin
if LEFT(@strHazardClassIDs, 1) <> ','
set @strHazardClassIDs = ',' + @strHazardClassIDs;

if RIGHT(@strHazardClassIDs, 1) <> ','
set @strHazardClassIDs = @strHazardClassIDs + ',';
end
set @intOwnerTypes = LTRIM(RTRIM(@intOwnerTypes));
if LEN(@intOwnerTypes) > 0
begin
if LEFT(@intOwnerTypes, 1) <> ','
set @intOwnerTypes = ',' + @intOwnerTypes;

if RIGHT(@intOwnerTypes, 1) <> ','
set @intOwnerTypes = @intOwnerTypes + ',';
end
set @intTypeOfEquipment = LTRIM(RTRIM(@intTypeOfEquipment));
if LEN(@intTypeOfEquipment) > 0
begin
if LEFT(@intTypeOfEquipment, 1) <> ','
set @intTypeOfEquipment = ',' + @intTypeOfEquipment;

if RIGHT(@intTypeOfEquipment, 1) <> ','
set @intTypeOfEquipment = @intTypeOfEquipment + ',';
end

declare @balancesTable TABLE
(
fld_intID int IDENTITY(1,1) PRIMARY KEY,
fld_intCustomerID int,
fld_intLocationID int,
fld_strPartNo varchar(100),
fld_intTypeOfEquipment int,
fld_strStatus varchar(10),
fld_intBalance int,
fld_dtAsOnDate Datetime,
UNIQUE(fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus, fld_dtAsOnDate, fld_intID)
)

--BEGIN

DECLARE @inventoryTable TABLE
(
fld_intID int IDENTITY(1,1) PRIMARY KEY,
fld_intInventoryID int,
fld_intCustomerID int,
fld_intLocationID int,
fld_strPartNo varchar(100),
fld_intTypeOfEquipment int,
fld_strStatus varchar(10),
fld_intQuantity int,
fld_dtAsOnDate Datetime,
--fld_strFromTable varchar(2),
fld_dtFromDate datetime,
fld_dtToDate datetime,
fld_dtDate datetime,
UNIQUE(fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtAsOnDate, fld_intID)
)

Declare @strStatuses varchar(20)
/*if UPPER(@locationType) = 'WH' OR UPPER(@locationType) LIKE 'WARE%'
set @strStatuses = ',AI,AIN,';
else
set @strStatuses = ',IU,';

if ISNULL(@locationType, '') = ''---Blank means no preference.
*/set @strStatuses = ',AI,AIN,IU,';


----1. Cylinders, Bulk & Bulk Stationary (All these are considered cylinders. NOT Hardgoods)
insert into @inventoryTable
(fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo,fld_intTypeOfEquipment, fld_strStatus,
fld_intQuantity, fld_dtAsOnDate, /*fld_strFromTable,*/ fld_dtFromDate, fld_dtToDate, fld_dtDate)

select fld_intInventoryID, fld_intCustomerID,fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus,
fld_intQuantity/*SUM(fld_intQuantity)*/, DT.fld_dtAsOnDate--@dtAsOnDate
,/*fld_strFromTable,*/ fld_dtFromDate, fld_dtToDate, fld_dtDate

FROM --X
(
SELECT fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtDate,
MIN(fld_dtFromDate) OVER(PARTITION BY fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtDate) as fld_dtFromDate,
MAX(fld_dtToDate)OVER(PARTITION BY fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtDate) as fld_dtToDate,
fld_intQuantity = 1,
ROW_NUMBER() OVER(PARTITION BY fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtDate
ORDER BY fld_dtFromDate asc, fld_dtToDate desc
) as SNo
FROM --A
(
select IM.fld_intInventoryID, IM.fld_intCustomerID, IM.fld_intCurrentLocationID as fld_intLocationID, IM.fld_strCurrentContentPartNo as fld_strPartNo,
IM.fld_intTypeOfEquipment, IM.fld_strStatus, fld_intQuantity = 1,
IM.fld_dtModifiedDate as fld_dtFromDate, fld_dtToDate = cast('1/1/2999' as datetime), fld_dtCurrentContentDate as fld_dtDate,
'IM' as fld_strFromTable
from tbl_InventoryMaster IM WITH (NOLOCK)
JOIN tbl_CustomerMaster CM WITH (NOLOCK) ON (IM.fld_intCustomerID = CM.fld_intCustomerID)
JOIN tbl_LocationMaster LM WITH (NOLOCK) ON (IM.fld_intCurrentLocationID = LM.fld_intLocationID)
JOIN tbl_RegionCodes R with (NOLOCK)ON (R.fld_strCURegionCode = CM.fld_strRegionName)
where IM.fld_strStatus in (select list_item from adm_fn_GetList(@strStatuses))
AND IM.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
AND (IM.fld_intSupplierID in (select list_item from adm_fn_GetList(@strSupplierIDs)) OR ISNULL(@strSupplierIDs,'') in ('', ',,', ',', ',0,', ',-1,'))
AND (IM.fld_intCurrentLocationID in (select list_item from adm_fn_GetList(@strLocationIDs)) OR ISNULL(@strLocationIDs,'') = '')
AND (IM.fld_strCurrentContentPartNo in /*(select list_item from adm_fn_GetList(@strCurrentContentPartNos))*/
(select distinct fld_strCurrentContentPartNo
from tbl_InventoryMaster IM2 WITH (NOLOCK)
JOIN (select list_item from adm_fn_GetList(','+@strCurrentContentPartNos+',')) as SuppParts
ON (IM2.fld_strCurrentContentPartNo LIKE '%' + RTRIM(LTRIM(SuppParts.list_item)) + '%')
where IM2.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strCurrentContentPartNos, '') = ''
)
AND (IM.fld_strCustomerPartNo in /*(select list_item from adm_fn_GetList(@strCustomerPartNos))*/
(select distinct fld_strCustomerPartNo
from tbl_InventoryMaster IM3 WITH (NOLOCK)
JOIN (select list_item from adm_fn_GetList(','+@strCustomerPartNos+',')) as CustParts
ON (IM3.fld_strCurrentContentPartNo LIKE '%' + RTRIM(LTRIM(CustParts.list_item)) + '%')
where IM3.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strCustomerPartNos, '') = ''
)
AND (IM.fld_intProductSizeID in (select list_item from adm_fn_GetList(@intProductSizeIDs)) OR ISNULL(@intProductSizeIDs,'') in ('', ',,', ',', ',0,', ',-1,'))
AND (IM.fld_strHazardClass IN (Select fld_strHazardClassName FROM tbl_ProductHazardClass
WHERE fld_intHazardClassID IN (select list_item from adm_fn_getlist(@strHazardClassIDs))
)
OR ISNULL(@strHazardClassIDs,'') in ('', ',,', ',', ',0,', ',-1,')
)
AND (
(@intAuditFlag = 1 AND EXISTS (SELECT 1 FROM tbl_AuditItems AI WITH (NOLOCK) WHERE AI.fld_intInventoryID = IM.fld_intInventoryID AND ISNULL(fld_bitAuditFlag,0) > 0)
)
OR (@intAuditFlag = 2 AND NOT EXISTS (SELECT 1 FROM tbl_AuditItems AI WITH (NOLOCK) WHERE AI.fld_intInventoryID = IM.fld_intInventoryID AND ISNULL(fld_bitAuditFlag,0) > 0)
)
OR ISNULL(@intAuditFlag, 0) = 0
)
AND (IM.fld_intOwner in (select list_item from adm_fn_GetList(@intOwnerTypes)) OR ISNULL(@intOwnerTypes,'') in ('', ',,', ',', ',0,', ',-1,'))
AND (
(ISNULL(R.fld_bitIsMigratedToSAP,0) = 0 AND ISNULL(LM.fld_strCUAccountNo,CM.fld_strCUAccountNo) IN(select list_item from adm_fn_getlist(','+@strCuAccountNos+',')))
OR (ISNULL(R.fld_bitIsMigratedToSAP,0) > 0 AND ISNULL(LM.fld_strSAPAccountNo,CM.fld_strSAPAccountNo) IN(select list_item from adm_fn_getlist(','+@strCuAccountNos+',')))
OR ISNULL(@strCuAccountNos,'') = ''
)
AND (
(
@strBarCodes LIKE '%,%' AND ( IM.fld_strBarCode IN (select list_item from adm_fn_getlist(','+@strBarCodes+',')) )
)
OR ( LEN(@strBarCodes) > 0 AND (IM.fld_strBarCode LIKE '%'+@strBarCodes+'%') )
OR ISNULL(@strBarCodes, '') = ''
)
AND (
(
@strSerialNos LIKE '%,%' AND (IM.fld_strSerialNo IN (select list_item from adm_fn_getlist(','+@strSerialNos+',')) )
)
OR ( LEN(@strSerialNos) > 0 AND (IM.fld_strSerialNo LIKE '%'+@strSerialNos+'%') )
OR ISNULL(@strSerialNos, '') = ''
)
AND (IM.fld_strLOTNumber LIKE '%'+@strLOTNumber+'%' OR ISNULL(@strLOTNumber, '') = '')
AND (IM.fld_strAIMOrderNo LIKE '%'+@strAIMOrderNo+'%' OR ISNULL(@strAIMOrderNo, '') = '')
AND (IM.fld_strSupplierOrderNo LIKE '%'+@strSupplierOrderNo+'%' OR ISNULL(@strSupplierOrderNo, '') = '')
AND (IM.fld_strSAPDeliveryDocNo LIKE '%'+@deliveryDocNo+'%' OR ISNULL(@deliveryDocNo, '') = '')
AND (
DATEDIFF(d, IM.fld_dtCylinderExpirationDate, @dtCylinderExpirationDate) = 0
OR ISNULL(@dtCylinderExpirationDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (
DATEDIFF(d, IM.fld_dtAnalyticalDate, @dtAnalyticalDate) = 0
OR ISNULL(@dtAnalyticalDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (
DATEDIFF(d, IM.fld_dtHydrotestDate, @dtHydrotestDate) = 0
OR ISNULL(@dtHydrotestDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (IM.fld_strStatus in (select list_item from adm_fn_GetList(','+@strStatus+','))
OR ISNULL(@strStatus,'') = ''
)
AND (IM.fld_intTypeOfEquipment in (select list_item from adm_fn_GetList(@intTypeOfEquipment))
OR ISNULL(@intTypeOfEquipment,'') in ('', ',,', ',', ',0,', ',-1,')
)
AND (IM.fld_intThirdPartyOwner = @intThirdPartyOwner OR ISNULL(@intThirdPartyOwner, 0) <= 0)
AND (IM.fld_strCurrentContentPartNo in
(select distinct fld_strSupplierPartNo
from tbl_OrderLines OL WITH (NOLOCK)
JOIN tbl_OrderHeader OH WITH (NOLOCK) ON (OH.fld_strAIMOrderNo = OL.fld_strAIMOrderNo)
where OL.fld_strMSDSNo LIKE '%' + @strMSDSNo + '%'
AND OH.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strMSDSNo, '') = ''
)
AND IM.fld_intTypeOfEquipment in (1,4,5)
AND DATEDIFF(d, IM.fld_dtModifiedDate, @dtEndDate) >= 0 ---->DATE CONDITION


UNION ALL


select IH.fld_intInventoryID, IH.fld_intCustomerID, IH.fld_intLocationID, IH.fld_strPartNo, IH.fld_intTypeOfEquipment, IH.fld_strStatus,
fld_intQuantity = 1, min(IH.fld_dtFromDate) as fld_dtFromDate, max(IH.fld_dtToDate) as fld_dtToDate, IH.fld_dtDate,
'IH' as fld_strFromTable
FROM tbl_InventoryHistory IH WITH (NOLOCK)
JOIN tbl_CustomerMaster CM WITH (NOLOCK) ON (IH.fld_intCustomerID = CM.fld_intCustomerID)
JOIN tbl_LocationMaster LM WITH (NOLOCK) ON (IH.fld_intLocationID = LM.fld_intLocationID)
JOIN tbl_RegionCodes R with (NOLOCK)ON (R.fld_strCURegionCode = CM.fld_strRegionName)
WHERE IH.fld_strStatus in (select list_item from adm_fn_GetList(@strStatuses))
AND IH.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
AND (IH.fld_intSupplierID in (select list_item from adm_fn_GetList(@strSupplierIDs)) OR ISNULL(@strSupplierIDs,'') = '')
AND (IH.fld_intLocationID in (select list_item from adm_fn_GetList(@strLocationIDs)) OR ISNULL(@strLocationIDs, '') = '')
AND (IH.fld_strPartNo in /*(select list_item from adm_fn_GetList(@strCurrentContentPartNos))*/
(select distinct fld_strPartNo
from tbl_InventoryHistory IH2 WITH (NOLOCK)
JOIN (select list_item from adm_fn_GetList(','+@strCurrentContentPartNos+',')) as SuppParts
ON (IH2.fld_strPartNo LIKE '%' + RTRIM(LTRIM(SuppParts.list_item)) + '%')
where IH2.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strCurrentContentPartNos,'') = ''
)
AND (IH.fld_strCustomerPartNo in /*(select list_item from adm_fn_GetList(@strCustomerPartNos))*/
(select distinct fld_strCustomerPartNo
from tbl_InventoryHistory IH3 WITH (NOLOCK)
JOIN (select list_item from adm_fn_GetList(','+@strCustomerPartNos+',')) as CustParts
ON (IH3.fld_strCustomerPartNo LIKE '%' + RTRIM(LTRIM(CustParts.list_item)) + '%')
where IH3.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strCustomerPartNos, '') = ''
)
AND (IH.fld_intProductSizeID in (select list_item from adm_fn_GetList(@intProductSizeIDs)) OR ISNULL(@intProductSizeIDs,'') in ('', ',,', ',', ',0,', ',-1,'))
AND (IH.fld_strHazardClass IN (Select fld_strHazardClassName FROM tbl_ProductHazardClass
WHERE fld_intHazardClassID IN (select list_item from adm_fn_getlist(@strHazardClassIDs))
)
OR ISNULL(@strHazardClassIDs,'') in ('', ',,', ',', ',0,', ',-1,')
)
AND (
(@intAuditFlag = 1 AND EXISTS (SELECT 1 FROM tbl_AuditItems AI WITH (NOLOCK) WHERE AI.fld_intInventoryID = IH.fld_intInventoryID AND ISNULL(fld_bitAuditFlag,0) > 0)
)
OR (@intAuditFlag = 2 AND NOT EXISTS (SELECT 1 FROM tbl_AuditItems AI WITH (NOLOCK) WHERE AI.fld_intInventoryID = IH.fld_intInventoryID AND ISNULL(fld_bitAuditFlag,0) > 0)
)
OR ISNULL(@intAuditFlag, 0) = 0
)
AND (IH.fld_intOwner in (select list_item from adm_fn_GetList(@intOwnerTypes)) OR ISNULL(@intOwnerTypes,'') in ('', ',,', ',', ',0,', ',-1,'))
AND (
(ISNULL(R.fld_bitIsMigratedToSAP,0) = 0 AND ISNULL(LM.fld_strCUAccountNo,CM.fld_strCUAccountNo) IN(select list_item from adm_fn_getlist(','+@strCuAccountNos+',')))
OR (ISNULL(R.fld_bitIsMigratedToSAP,0) > 0 AND ISNULL(LM.fld_strSAPAccountNo,CM.fld_strSAPAccountNo) IN(select list_item from adm_fn_getlist(','+@strCuAccountNos+',')))
OR ISNULL(@strCuAccountNos,'') = ''
)
AND (
(
@strBarCodes LIKE '%,%' AND ( IH.fld_strBarCode IN (select list_item from adm_fn_getlist(','+@strBarCodes+',')) )
)
OR ( LEN(@strBarCodes) > 0 AND (IH.fld_strBarCode LIKE '%'+@strBarCodes+'%') )

OR ISNULL(@strBarCodes, '') = ''
)
AND (
(
@strSerialNos LIKE '%,%' AND ( IH.fld_strSerialNo IN (select list_item from adm_fn_getlist(','+@strSerialNos+',')) )
)
OR ( LEN(@strSerialNos) > 0 AND (IH.fld_strSerialNo LIKE '%'+@strSerialNos+'%') )

OR ISNULL(@strSerialNos, '') = ''
)
AND (IH.fld_strLOTNumber LIKE '%'+@strLOTNumber+'%' OR ISNULL(@strLOTNumber, '') = '')
AND (IH.fld_strAIMOrderNo LIKE '%'+@strAIMOrderNo+'%' OR ISNULL(@strAIMOrderNo, '') = '')
AND (IH.fld_strSupplierOrderNo LIKE '%'+@strSupplierOrderNo+'%' OR ISNULL(@strSupplierOrderNo, '') = '')
AND (IH.fld_strSAPDeliveryDocNo LIKE '%'+@deliveryDocNo+'%' OR ISNULL(@deliveryDocNo, '') = '')
AND (
DATEDIFF(d, IH.fld_dtCylinderExpirationDate, @dtCylinderExpirationDate) = 0
OR ISNULL(@dtCylinderExpirationDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (
DATEDIFF(d, IH.fld_dtAnalyticalDate, @dtAnalyticalDate) = 0
OR ISNULL(@dtAnalyticalDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (
DATEDIFF(d, IH.fld_dtHydrotestDate, @dtHydrotestDate) = 0
OR ISNULL(@dtHydrotestDate, cast('1/1/1900' as datetime)) = cast('1/1/1900' as datetime)
)
AND (IH.fld_strStatus in (select list_item from adm_fn_GetList(','+@strStatus+','))
OR ISNULL(@strStatus,'') = ''
)
AND (IH.fld_intTypeOfEquipment in (select list_item from adm_fn_GetList(@intTypeOfEquipment))
OR ISNULL(@intTypeOfEquipment,'') in ('', ',,', ',', ',0,', ',-1,')
)
AND (IH.fld_intThirdPartyOwner = @intThirdPartyOwner OR ISNULL(@intThirdPartyOwner, 0) <= 0)
AND (IH.fld_strPartNo in
(select distinct fld_strSupplierPartNo
from tbl_OrderLines OL WITH (NOLOCK)
JOIN tbl_OrderHeader OH WITH (NOLOCK) ON (OH.fld_strAIMOrderNo = OL.fld_strAIMOrderNo)
where OL.fld_strMSDSNo LIKE '%' + @strMSDSNo + '%'
AND OH.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
)
OR ISNULL(@strMSDSNo, '') = ''
)
AND IH.fld_intTypeOfEquipment in (1,4,5)
AND ( DATEDIFF(d, IH.fld_dtDate, @dtEndDate) >= 0 AND DATEDIFF(d, IH.fld_dtToDate, @dtStartDate) < 0 ) ---->DATE CONDITION

group by IH.fld_intCustomerID, IH.fld_intLocationID, IH.fld_intInventoryID, IH.fld_strPartNo, IH.fld_intTypeOfEquipment, IH.fld_strStatus, IH.fld_dtDate
) AS A
--GROUP BY fld_intInventoryID, fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtDate

) as X
JOIN @datesTable DT ON (DATEDIFF(d, X.fld_dtDate, DT.fld_dtAsOnDate) >= 0
AND DATEDIFF(d, ISNULL(X.fld_dtToDate, cast('1/1/2999' as datetime)), DT.fld_dtAsOnDate) < 0
AND X.SNo = 1)
WHERE X.SNo = 1
--group by fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, DT.fld_dtAsOnDate
-------------------------------end of cylinders, bul, bulk stationary


--select * from @inventoryTable
--select distinct fld_intInventoryID, fld_strFromTable, fld_dtFromDate, fld_dtToDate, fld_strStatus
--from @inventoryTable
--order by 1,3,4,2

--Now group all the returns
UPDATE @inventoryTable SET fld_strStatus = 'RETURNS' where (fld_strStatus like 'RT%' OR fld_strStatus like 'WP%')

--Now group all the issues
UPDATE @inventoryTable SET fld_strStatus = 'ISSUES' where fld_strStatus in ('AIN','AI','IU')

--NOW PUT THE TOTALS INTO THE RESULT OBJECT
insert into @balancesTable
(
fld_intCustomerID,
fld_intLocationID,
fld_strPartNo,
fld_intTypeOfEquipment,
fld_strStatus,
fld_intBalance,
fld_dtAsOnDate
)
select fld_intCustomerID,
fld_intLocationID,
fld_strPartNo,
fld_intTypeOfEquipment,
fld_strStatus,
SUM(fld_intQuantity),
fld_dtAsOnDate
from @inventoryTable
group by fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus, fld_dtAsOnDate


declare @invIDs varchar(max)
set @invIDs = ','
select @invIDs = @invIDs + ISNULL(fld_intInventoryID + ',', ',') from (select distinct cast(fld_intInventoryID as varchar(8)) as fld_intInventoryID from @inventoryTable) AS A
declare @locations varchar(max)
set @locations = ','
select @locations = @locations + ISNULL(fld_intLocationID + ',', ',') from (select distinct cast(fld_intLocationID as varchar(8)) as fld_intLocationID from @inventoryTable) AS A

declare @partNos varchar(max)
set @partNos = ','
select @partNos = @partNos + ISNULL(fld_strPartNo + ',', ',') from (select distinct fld_strPartNo from @inventoryTable) AS A
--------

DECLARE @TotalShipmentsTable TABLE
(
fld_intCustomerID int,
fld_intLocationID int,
fld_strPartNo varchar(100),
--fld_strStatus varchar(10),
fld_intIssues int,
fld_intReturns int
)

Declare @shipmentsTable TABLE
(
fld_intCustomerID int,
fld_intLocationID int,
fld_strPartNo varchar(100),
fld_strStatus varchar(10),
fld_intShipments int
)
declare @fromDate datetime
set @fromDate = dbo.fn_getDatePart(@dtStartDate);
declare @toDate datetime
set @toDate = dbo.fn_getDatePart(@dtEndDate);

Declare @strStatusesFN varchar(20)
set @strStatusesFN = ',IU,AI,AIN,WP,WP-FULL,WP-RGA,RT,RT-FULL,RT-RGA,';


INSERT INTO @shipmentsTable(fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus,fld_intShipments)
SELECT LM.fld_intCustomerID, ISD.fld_intDeliveryLocationID, ISD.fld_strSupplierPartNo, ISD.fld_strType,
fld_intShipments = 1--(CASE WHEN (IM.fld_intTypeOfEquipment in (1,4,5)) then 1 else ISNULL(ISD.fld_intUPCQty,1) END)
FROM tbl_IssuesAndReturnsDetails ISD WITH (NOLOCK)
JOIN tbl_InventoryMaster IM WITH(NOLOCK) ON (ISD.fld_intInventoryID = IM.fld_intInventoryID /*AND IM.fld_intTypeOfEquipment in (1,4,5)*/)
JOIN tbl_LocationMaster LM WITH(NOLOCK) ON (LM.fld_intLocationID = ISD.fld_intDeliveryLocationID)
WHERE ISD.fld_strType in (select list_item from adm_fn_GetList(@strStatusesFN))
-- AND dbo.fn_getDatePart(ISD.fld_dtShipReturnDate) >= @fromDate AND dbo.fn_getDatePart(ISD.fld_dtShipReturnDate) <= @toDate
AND (dbo.fn_getDatePart(ISD.fld_dtShipReturnDate) BETWEEN @fromDate AND @toDate)
AND LM.fld_intCustomerID in (select list_item from adm_fn_GetList(@strCustomerIDs))
AND ISD.fld_intDeliveryLocationID in (select list_item from adm_fn_GetList(@locations))
AND ISD.fld_strSupplierPartNo in (select list_item from adm_fn_GetList(@partNos))
AND IM.fld_intTypeOfEquipment in (1,4,5/*,3 is this 3 needed?*/)
AND (IM.fld_intSupplierID in (select list_item from adm_fn_GetList(@strSupplierIDs)) OR ISNULL(@strSupplierIDs, '') = '')
AND (ISD.fld_intInventoryID in (select list_item from adm_fn_GetList(@invIDs)) OR ISNULL(@invIDs, '') = '' )

--Now group all the returns
UPDATE @shipmentsTable SET fld_strStatus = 'RETURNS' where (fld_strStatus like 'RT%' OR fld_strStatus like 'WP%')

--Now group all the issues
UPDATE @shipmentsTable SET fld_strStatus = 'ISSUES' where fld_strStatus in ('AIN','AI','IU')

--Put the total into the results table
INSERT INTO @TotalShipmentsTable
(
fld_intCustomerID ,
fld_intLocationID ,
fld_strPartNo ,
--fld_strStatus,
fld_intIssues,
fld_intReturns
)
select ISNULL(Iss.fld_intcustomerID, Rets.fld_intcustomerID),
ISNULL(Iss.fld_intLocationID, Rets.fld_intLocationID),
ISNULL(Iss.fld_strPartNo, Rets.fld_strPartNo),
Iss.fld_intShipments,
Rets.fld_intShipments
From
(
select fld_intcustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus, SUM(fld_intShipments) as fld_intShipments
FROM @shipmentsTable
where fld_strStatus = 'ISSUES'
GROUP BY fld_intcustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus
) as Iss
FULL OUTER JOIN
(
select fld_intcustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus, SUM(fld_intShipments) as fld_intShipments
FROM @shipmentsTable
where fld_strStatus = 'RETURNS'
GROUP BY fld_intcustomerID, fld_intLocationID, fld_strPartNo, fld_strStatus
) as Rets
ON (Iss.fld_intcustomerID = Rets.fld_intcustomerID AND
Iss.fld_intLocationID = Rets.fld_intLocationID AND
Iss.fld_strPartNo = Rets.fld_strPartNo
)

-------------------------------------
select ISNULL(BAL.fld_intCustomerID, SHP.fld_intCustomerID),
ISNULL(BAL.fld_intLocationID, SHP.fld_intLocationID),
ISNULL(BAL.fld_strPartNo, SHP.fld_strPartNo),
BAL.minBal as MinBalance, BAL.MaxBal as MaxBalance, BAL.avgForPeriod as AvgBalance,
SHP.fld_intIssues as QtyIssued,
SHP.fld_intReturns as QtyReturned,
fld_intTurnsRatio = Cast(
(
(Cast(BAL.avgForPeriod as Decimal(18,2)) * @days) /
(Cast(SHP.fld_intIssues as Decimal(18,2)) * 30)
) as Decimal(18,2)
),
OPENBAL.fld_intBalance as OpeningBalance
FROM
(
select fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus,
MIN(fld_intBalance) as minBal, MAX(fld_intBalance) as maxBal,
cast(AVG(cast(fld_intBalance as decimal(18,2))) as decimal(18,2)) as avgBal,
SUM(fld_intBalance) as sumBal, COUNT(*) as noOfDaysUtilized,
cast((cast(SUM(fld_intBalance) as decimal(18,2))/cast(@days+1 as decimal(18,2))) as decimal(18,2)) as avgForPeriod
from @balancesTable
group by fld_intCustomerID, fld_intLocationID, fld_strPartNo, fld_intTypeOfEquipment, fld_strStatus
) as BAL
FULL OUTER JOIN @TotalShipmentsTable /*dbo.rpt_fn_GetShipmentsAndReturnsInPeriod(@strCustomerIDs,@locations,@partNos,@strSupplierIDs,
NULL, @dtStartDate, @dtEndDate, @invIDs, 'LOCATION'
) as*/ SHP
ON (SHP.fld_intCustomerID = BAL.fld_intCustomerID
AND SHP.fld_intLocationID = BAL.fld_intLocationID
AND SHP.fld_strPartNo = BAL.fld_strPartNo
--AND SHP.fld_strStatus = BAL.fld_strStatus
)
LEFT OUTER JOIN (select * from @balancesTable where fld_dtAsOnDate = @dtStartDate and fld_strStatus = 'ISSUES') as OPENBAL
ON (OPENBAL.fld_intCustomerID = BAL.fld_intCustomerID AND
OPENBAL.fld_intLocationID = BAL.fld_intLocationID AND
OPENBAL.fld_strPartNo = BAL.fld_strPartNo AND
OPENBAL.fld_strStatus = BAL.fld_strStatus
)




--END


gimme suggetions to improve performance of above query..

thankx









ivbalaram
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2012-03-30 : 05:59:16
Wow. That size of procedure would likely take me a day or two to tune properly

First thing, do some analysis and find out what statements in there are the slow ones. [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
Also consider changing the table variables to temp tables and see if that makes a difference.

Lose the nolock hints, they are not a 'go faster' switch. Instead they are an 'incorrect data acceptable' switch. See - [url]http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx[/url]

Check the split function (adm_fn_GetList), make sure they're not using slow cursors. This article shows a very fast spit function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

--
Gail Shaw
SQL Server MVP
Go to Top of Page

balaram19
Starting Member

9 Posts
Posted - 2012-03-31 : 03:17:16
https is blocked in my organization.. sqlservercentral links were not opening.. can you pls gimme suggetions to improve query performance......

thankx

ivbalaram
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2012-03-31 : 05:04:30
I have given you suggestions. Work through what I've listed above (use your phone or home internet if you can't get SSC at word)

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site