July 1, 2011 at 7:54 pm
Dear ALL:
I have the query below but the perfromance is not good large number of record return (e.g. 20000 rows +),
the following table contain is the a lot of record
Unit - PK unit_Id ( 500,000 + )
Tenure - PK tenure_id ( 500,000 + )
client - PK client_id (700,000 +)
is there improvement which i can do to the following query?
From CH
select (Case When leaseTenure.Rent_UOM = 'Year' then leaseTenure.Tenure_Amount * 100 / (Case When i.Price_Asking_Sell_Amount = 0 Then null Else i.Price_Asking_Sell_Amount End)
When leaseTenure.Rent_UOM = 'Month' then leaseTenure.Tenure_Amount * 100 / (Case When i.Price_Asking_Sell_Amount = 0 Then null Else i.Price_Asking_Sell_Amount End)
When leaseTenure.Rent_UOM = 'Day' then leaseTenure.Tenure_Amount * 100 / (Case When i.Price_Asking_Sell_Amount = 0 Then null Else i.Price_Asking_Sell_Amount End)
Else Null End
) as Yield,
(Case When Exists(select null from Unit_Component Where Unit_Id = u.Unit_Id and status = 'Active') then 1 else 0 end) as UC_Exist,
(Case When Exists(select null from Unit_Component Where Unit_Id = u.Unit_Id and status = 'Active') then '(*)' else '' end) as UC_Show_Text,
(Case When u.Unit_Name is Null Then u.Unit_Name Else u.Unit_Name End) as Unit_Name, u.Rn_Edit_Date as Unit_Date,
(Case When b4.Bound4_Name is Null Then b4.Bound4_Name Else b4.Bound4_Name End) as Bound4_Name, b5.Bound5_Name as Bound5_Name,
Street=
case
when RTRIM(LTRIM(isnull(b.Street_No_To, '')))='' then b.Street_No_From + ' ' + b5.Bound5_Name
else b.Street_No_From + '-' + b.Street_No_To + ' ' + b5.Bound5_Name
end,
b.Street_No_From , b.Street_No_To, b.Street_No_From_Numeric,
b.Street_No_To_Numeric,
(Case When (b.Building_Name = p.Property_Name and Not b.Building_Name is Null) Then b.Building_Name
When (b.Building_Name <> p.Property_Name and Not b.Building_Name is Null) Then p.Property_Name + ',' + b.Building_Name
When (b.Building_Name = p.Property_Name and Not b.Building_Name is Null) Then b.Building_Name
Else p.Property_Name + ',' + b.Building_Name End ) as Property_Building_Name,
(Case When Not p.Property_Name is null then p.Property_Name Else p.Property_Name End) as Property_Name ,
cast(p.Property_Id as bigint) as Property_Id,
(Case When (b.Building_Name = p.Property_Name and Not b.Building_Name is Null) Then ''
When (b.Building_Name <> p.Property_Name and Not b.Building_Name is Null) Then b.Building_Name
When (b.Building_Name = p.Property_Name and Not b.Building_Name is Null) Then b.Building_Name
Else p.Property_Name + ',' + b.Building_Name End ) as Building_Name,
u.Unit_Floor,
u.Unit_Number_From,
u.Gross_Area_Size,
u.Default_Area_Size,
aet.Area_Type_Display_Name as Area_Type_Display_Name,
mt.Market_Type_Name as Market_Type ,
IsActive=
case
when i.Market_Type_Id is null then 0
else 1
end,
i.Price_Asking_Sell_Amount , i.Asking_Sale_UOM, i.Rent_Ask ,i.Asking_Rent_UOM,
IsNull(Owner.Rn_Descriptor, ' ') as Owner_Name,
cast(u.Owner_Id as bigint) as Owner_Id_Text,(cast (i.Instruction_Id as bigint)) as Instruction_Id,
(cast (u.Unit_id as bigint)) as Unit_Id,
at.Agency_Type_Short_Name as Agency_Code,
(Case When i.Under_Offer_Date is not null Then 1 Else 0 End) as UnderOffer,
i.available_date as available_date ,
leaseTenure.Tenure_Amount as Cur_Rent,
leaseTenure.Lease_Expiry_Date as Expiry,
Case
When u.Instruction_Id is not null
Then
(Case
When (i.Agent_Id is not null or i.Agent_Contact_Id is not null)
Then (Case
When ultimateContact.Client_SysType = ''Company'' Then ''(A)'' + ultimateContact.Comp_Name
Else ''(A)'' + ultimateContact.Rn_Descriptor
End)
Else (Case
When ultimateContact.Client_SysType = ''Company'' Then ultimateContact.Comp_Name
Else ultimateContact.Rn_Descriptor
End)
End)
Else (Case
When UnitOwnerContact.Client_SysType = ''Company'' Then UnitOwnerContact.Comp_Name
Else UnitOwnerContact.Rn_Descriptor
End )
End as Owner_Contact,
Case When Not u.Instruction_Id is Null Then (Cast(i.Ultimate_Contact_Id as BigInt)) Else (Cast(u.Owner_Contact_Id as BigInt)) End as Owner_Contact_Id_Text,
Tenant.Rn_Descriptor as Tenant_Name,
Occupant.Rn_Descriptor as Occupant_Name,
(Cast (u.Tenant_Id as bigint)) as Tenant_Id,
(Cast (u.Occupant_Id as bigint)) as Occupant_Id,
(Select top 1 Case When Description2 is Null Then isNull(cast(Quantity as nvarchar(100)), ''Y'') Else isNull(cast(Quantity as nvarchar(100)), ''Y'') + ''-('' + description2 + '')'' End From Feature_Criteria where Unit_Id = u.Unit_Id and Features_Id in ( select Features_Id from Features where Feature_Type_Name = ''Carpark'')) as CarPark,
i.Remarks,
cast(i.Instruction_Key_Id as BigInt) as Instruction_Key_Id,
Key_No=
case
when i.Instruction_Key_Id is null then ''''
else ''No. '' + (select Instruction_Key.Rn_Descriptor from Instruction_Key where Instruction_Key.Instruction_Key_Id=i.Instruction_Key_Id) + N'', ''
end,
Key_Remarks=
case
when i.Key_Remarks is null then ''''
else cast(i.Key_Remarks as nvarchar(max)) + N'', ''
end,
Quantity=
case
when i.Quantity is null then ''''
else ''total '' + i.Quantity + N'', ''
end,
cast(i.Keyholder_Id as BigInt) as Keyholder_Id,
Keyholder=
case
when i.Keyholder_Id is null then ''''
else ''keep by '' + (select employee.Rn_Descriptor from Employee where Employee.Employee_Id=i.Keyholder_Id) + N'', ''
end,
KeyReceivedOnDesc=
case
when i.Key_Received_Date is null then ''''
else ''received on ''
end,
i.Key_Received_Date,
cast(i.Department_Key_Id as BigInt) as Department_Key_Id
From
((((((((((((((( Unit u With (NoLock)
inner join Building b With (NoLock) on u.Building_Id = b.Building_Id )
inner join Property p With (NoLock) on b.Property_id = p.Property_Id )
inner join Bound5 b5 With (NoLock) on p.Bound5_Id = b5.Bound5_Id )
inner join Bound4 b4 With (NoLock) on b5.Bound4_Id = b4.Bound4_Id )
Left Join Instruction i on i.Instruction_id = u.Instruction_Id ' + dbo.fnc_Instruction_F(@EmployeeId, 'i') + ' and i.Instruction_Status_Id = ( Select Instruction_Status_Id from Instruction_Status Where Status_Internal_Name = ''00''))
Left join Area_Type aet on u.Default_Area_Type_Id= aet.Area_Type_Id)
Left join Market_Type mt on mt.Market_Type_Id = i.Market_Type_Id)
Left Join Tenure leaseTenure With (NoLock) on leaseTenure.Tenure_Id = u.Lease_Tenure_Id )
Left Join Tenure ot With (NoLock) on ot.Tenure_Id = u.Owner_Tenure_Id )
Left Join Client StockOwnerContact With (NoLock) on i.Client_Id = StockOwnerContact.Client_Id)
left Join Client ultimateContact on ultimateContact.Client_Id = i.Ultimate_Contact_Id)
Left Join Client UnitOwnerContact With (NoLock) on i.Client_Id = UnitOwnerContact.Client_Id)
Left Join Client Tenant With (NoLock) on u.Tenant_Id = Tenant.Client_Id)
Left Join Client Occupant With (NoLock) on u.Occupant_Id = Occupant.Client_Id)
Left Join Client Owner with (NoLock) on u.Owner_Id = Owner.Client_Id)
Left Join Agency_Type at With (NoLock) on i.Agency_Type_Id = at.Agency_Type_Id
Where 1=1 and u.Bound4_id = 3
July 1, 2011 at 8:42 pm
Read the second article I reference below in signature block regarding asking for assistance with performance problems. Follow the instructions in that article and people will be more willing to provide assistance.
Also, you may want to consider formatting your code so that it is easier to read. If I were to help the first thing I would do is reformat your code so that I could read it easier.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply