Query Optimization

Question

Below query is taking long to execute in SSMS 2008 . Is there a way to tune . I know below code is the problem . 

   ,(SELECT CONVERT(varchar(20),ISNULL(pv1.field_value,''))   
      FROM Property p1 (NOLOCK)  
      INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
      INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
      WHERE pf1.field_name = 'FCL_Bid_Dt'  
      AND p1.Property_ID = fex.Property_ID) as FCL_Schedule_Dt   
     ,(SELECT CONVERT(varchar(20),ISNULL(pv2.field_value,''))   
      FROM Property p2 (NOLOCK)  
      INNER JOIN Property_Value pv2 (NOLOCK) ON pv2.Property_ID = p2.Property_ID  
      INNER JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv2.Field_ID   
      WHERE pf2.field_name = 'fc_sale_date'  
      AND p2.Property_ID = fex.Property_ID) as FCL_Actual_Dt     
     ,(SELECT CONVERT(varchar(20),ISNULL(pv3.field_value,''))   
      FROM Property p3 (NOLOCK)  
      INNER JOIN Property_Value pv3 (NOLOCK) ON pv3.Property_ID = p3.Property_ID  
      INNER JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID   
      WHERE pf3.field_name = 'redemption_date'  
      AND p3.Property_ID = fex.Property_ID) as FCL_Redemption_Dt  

SELECT DISTINCT fex.Property_ID  
     ,l.Public_Name as Seller_Name   
     ,p.Loan_Number  
     ,a.Address_1 as Address  
     ,a.City  
     ,a.State  
     ,UPPER(tx.type_value) as Property_Status  
     ,(SELECT COUNT(1)   
       FROM File_External_XREF fex1 (NOLOCK)   
       WHERE fex1.File_Status = @File_Status  
       AND fex1.Property_ID = fex.Property_ID    
       AND EXISTS (  
        SELECT 1 FROM File_External_Type fet (NOLOCK)   
        WHERE fet.External_Doc_Type = fex1.Doc_type   
        AND fet.External_Doc_Type != '')) as Received_Count         
     ,(SELECT CONVERT(varchar(20),ISNULL(pv1.field_value,''))   
      FROM Property p1 (NOLOCK)  
      INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
      INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
      WHERE pf1.field_name = 'FCL_Bid_Dt'  
      AND p1.Property_ID = fex.Property_ID) as FCL_Schedule_Dt   
     ,(SELECT CONVERT(varchar(20),ISNULL(pv2.field_value,''))   
      FROM Property p2 (NOLOCK)  
      INNER JOIN Property_Value pv2 (NOLOCK) ON pv2.Property_ID = p2.Property_ID  
      INNER JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv2.Field_ID   
      WHERE pf2.field_name = 'fc_sale_date'  
      AND p2.Property_ID = fex.Property_ID) as FCL_Actual_Dt     
     ,(SELECT CONVERT(varchar(20),ISNULL(pv3.field_value,''))   
      FROM Property p3 (NOLOCK)  
      INNER JOIN Property_Value pv3 (NOLOCK) ON pv3.Property_ID = p3.Property_ID  
      INNER JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID   
      WHERE pf3.field_name = 'redemption_date'  
      AND p3.Property_ID = fex.Property_ID) as FCL_Redemption_Dt  
    FROM File_External_XREF fex (NOLOCK)  
    INNER JOIN Property p (NOLOCK) on p.Property_ID = fex.Property_ID  
    INNER JOIN Lender l (NOLOCK) on l.Lender_ID = p.Lender_ID  
    INNER JOIN Address a (NOLOCK) on a.Object_ID = p.Property_ID AND a.Address_Type = 'Property'  
    INNER JOIN Type_XREF tx (NOLOCK) on tx.type_id = p.Status  
    INNER JOIN File_External_Type fet (NOLOCK) on fet.External_Doc_Type = fex.Doc_type AND fet.External_Doc_Type != ''  
    WHERE fex.file_status IN (@File_Status)  
    AND p.Lender_ID = @Lender_id  

Answer 1

I would suggest that you check out the query plan and proceed from there.

Ensure that you have appropriate indexes on columns used to filter the data with relevant columns in the INCLUDE clause.

Answer 2

Cut down your query and store it to temporary table.

Then join Query with remaining Query and finally if you need where condition then first store entire result in temporary table and apply where condition on that...

I am currently using this way that's why i am suggesting you.

you can use 4 to 5 temporary table...

you can use more but it might be decrease performance...

Hope it helps you...

Answer 3

Very similar to scalar udf  execution , therefore as other already suggested have a temporary table and store the data into.. then just JOIN the #tmp with the main SELECT and it should be running faster. Let us know how was it going

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!

Answer 5

I changed my query as follows . Its taking more time . Do you have any suggestions ?

CREATE Table #temp(field_value varchar(20),property_id int)
   INSERT INTO #temp SELECT ISNULL(pv1.field_value,''),p1.Property_ID    
      FROM Property p1 (NOLOCK)  
      INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
      INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
      WHERE pf1.field_name = 'FCL_Bid_Dt' 
      
      CREATE Table #temp1(field_value varchar(20),property_id int)
   INSERT INTO #temp1 SELECT ISNULL(pv1.field_value,''),p1.Property_ID    
      FROM Property p1 (NOLOCK)  
      INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
      INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
      WHERE pf1.field_name = 'fc_sale_date' 
      
       CREATE Table #temp2(field_value varchar(20),property_id int)
   INSERT INTO #temp2 SELECT ISNULL(pv1.field_value,''),p1.Property_ID    
      FROM Property p1 (NOLOCK)  
      INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
      INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
      WHERE pf1.field_name = 'redemption_date' 
      
  create index temp ON #temp(property_id)
  create index temp1 ON #temp1(property_id)
  create index temp2 ON #temp2(property_id)
  
  
      
       
   SELECT *  
   FROM (  
    SELECT DISTINCT fex.Property_ID  
     ,l.Public_Name as Seller_Name   
     ,p.Loan_Number  
     ,a.Address_1 as Address  
     ,a.City  
     ,a.State  
     ,UPPER(tx.type_value) as Property_Status  
     ,(SELECT COUNT(1)   
       FROM File_External_XREF fex1 (NOLOCK)   
       WHERE fex1.File_Status = @File_Status  
       AND fex1.Property_ID = fex.Property_ID    
       AND EXISTS (  
        SELECT 1 FROM File_External_Type fet (NOLOCK)   
        WHERE fet.External_Doc_Type = fex1.Doc_type   
        AND fet.External_Doc_Type != '')) as Received_Count 
        ,t.field_value AS FCL_Schedule_Dt 
        ,t1.field_value AS FCL_Actual_Dt
        ,t2.field_value AS FCL_Redemption_Dt         
     --,(SELECT CONVERT(varchar(20),ISNULL(pv1.field_value,''))   
     -- FROM Property p1 (NOLOCK)  
     -- INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
     -- INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID   
     -- WHERE pf1.field_name = 'FCL_Bid_Dt'  
     -- AND p1.Property_ID = fex.Property_ID) as FCL_Schedule_Dt   
     --,(SELECT CONVERT(varchar(20),ISNULL(pv2.field_value,''))   
     -- FROM Property p2 (NOLOCK)  
     -- INNER JOIN Property_Value pv2 (NOLOCK) ON pv2.Property_ID = p2.Property_ID  
     -- INNER JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv2.Field_ID   
     -- WHERE pf2.field_name = 'fc_sale_date'  
      --AND p2.Property_ID = fex.Property_ID) as FCL_Actual_Dt     
     --,(SELECT CONVERT(varchar(20),ISNULL(pv3.field_value,''))   
     -- FROM Property p3 (NOLOCK)  
     -- INNER JOIN Property_Value pv3 (NOLOCK) ON pv3.Property_ID = p3.Property_ID  
     -- INNER JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID   
     -- WHERE pf3.field_name = 'redemption_date'  
     -- AND p3.Property_ID = fex.Property_ID) as FCL_Redemption_Dt  
    FROM File_External_XREF fex (NOLOCK)  
    INNER JOIN Property p (NOLOCK) on p.Property_ID = fex.Property_ID  
    INNER JOIN Lender l (NOLOCK) on l.Lender_ID = p.Lender_ID  
    INNER JOIN Address a (NOLOCK) on a.Object_ID = p.Property_ID AND a.Address_Type = 'Property'  
    INNER JOIN Type_XREF tx (NOLOCK) on tx.type_id = p.Status  
    INNER JOIN File_External_Type fet (NOLOCK) on fet.External_Doc_Type = fex.Doc_type AND fet.External_Doc_Type != '' 
    INNER JOIN #temp t on t.property_id =p.Property_ID 
    INNER JOIN #temp t1 on t1.property_id =p.Property_ID 
    INNER JOIN #temp t2 on t2.property_id =p.Property_ID 
   
    WHERE fex.file_status = @File_Status
    AND p.Lender_ID = @Lender_id  
   ) x  
   ORDER BY Received_Count DESC  
  
  END

Answer 6

  create index temp ON #temp(property_id)
  create index temp1 ON #temp1(property_id)
  create index temp2 ON #temp2(property_id) 

I suggest you make these indexes clustered instead of non-clustered (the default) in order to avoid the RID lookup.  I can't really say whether or not temp tables are the right approach without knowing more about your data.  Have you reviewed the execution plan to identify the most expensive operators?

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

Hi,

You can make temp table in this way also...

Select

a,

b,

c

into #temp

from Tablename

Inner join.....

and so on...after cut down ur query jst check which part is taking more time...and try to optimize that part...

Answer 8

Uri

I tried with the temp table but its taking more time than the original query . Any other suggestions.

Answer 9

Please post the DDL and indexes for all tables involved in the query and rowcounts for each table.  Use SQL Server Management Studio to script the objects if you don't have them in source code control.

Answer 10

 select COUNT(*) from property --1816059
  select COUNT(1) from File_External_XREF --1831726
  select COUNT(1) from Lender  --230
  select COUNT(1) from Address --4576972
  select COUNT(1) from File_External_Type --515
  select COUNT(1) from Property_Value  --73480416
  select COUNT(1) from Property_Field --1254

There are indexes on almost all the columns of the tables

Answer 11

It's actually easier to break the query into parts which is what you are doing but you're essentially running the same query 3 times for each of the field_name's given.  This can be overcome a little with a pivot which trades IO for a little more CPU usually.

Try filtering by your filters when generating the temp table and then joining in the extra columns you need after you've gotten the filtered result set.  Of course, make sure your indexes are correct.

For example:

select
	pvt.property_id
,	pvt.loan_number
,	pvt.doc_type
,	pvt.[status]
,	pvt.[FCL_Bid_Dt] as FCL_Schedule_dt
,	pvt.[fc_sale_date] as FCL_Actual_Dt
,	pvt.[redemption_date] as FCL_Redemption_Dt
into #temp
from (
		select
			p.property_id
		,	p.loan_number
		,	p.[status]
		,	fex.doc_type
		,	pf.field_name
		,	convert(varchar(20),pv.value) as value
		from File_External_XREF fex
		  inner join Property p
			on p.property_id = fex.property_id
			and p.lender_id = @lender_id
		  inner join Property_value pv
			on pv.Property_ID = p.Property_ID
		  inner join Property_Field pf
			on pf.field_id = pv.field_id
			and pf.field_name in ('FCL_Bid_Dt','fc_sale_date','redemption_date')
		where fex.file_status in (@file_status) -- or "fex-file_Status = @file_Status"
) x
pivot (
	max(x.value) for x.field_name in ([FCL_Bid_Dt],[fc_sale_date],[redemption_date])
) pvt


SELECT DISTINCT
	t.Property_ID  
,	l.Public_Name as Seller_Name   
,	t.Loan_Number  
,	a.Address_1 as Address  
,	a.City  
,	a.State  
,	UPPER(tx.type_value) as Property_Status  
,	(
		SELECT COUNT(1)   
		FROM File_External_XREF fex1 (NOLOCK)   
		WHERE fex1.File_Status = @File_Status  
		AND fex1.Property_ID = fex.Property_ID    
		AND EXISTS (  
			SELECT 1
			FROM File_External_Type fet (NOLOCK)
			WHERE fet.External_Doc_Type = fex1.Doc_type   
			AND fet.External_Doc_Type != ''
		)
	) as Received_Count         
FROM #temp t
  INNER JOIN Lender l (NOLOCK)
	on l.Lender_ID = t.Lender_ID  
  INNER JOIN [Address] a (NOLOCK)
	on a.Object_ID = t.Property_ID
	AND a.Address_Type = 'Property'  
  INNER JOIN Type_XREF tx (NOLOCK)
	on tx.type_id = t.[Status]
  INNER JOIN File_External_Type fet (NOLOCK)
	on fet.External_Doc_Type = t.Doc_type
	AND fet.External_Doc_Type != ''  

Answer 12

Not working ..

Answer 13

There was a syntax error in my original post. It's fixed now.  Feel free to modify the code to make it work depending on the error since I obviously don't have the tables available to me to check syntax.

Answer 14

I looked in the Execution plan and 36 % resources going in Index spool (Lazy Spool) . Any recommendations on that .

Answer 15

Please post the DDL and indexes for all tables involved in the query.  Use SQL Server Management Studio to script the objects if you don't have them in source code control.

All the CREATE TABLEs.  All the CREATE INDEXes.

Answer 16

Below is the DDL and indexes of all the tables

CREATE TABLE [dbo].[Address](
[Object_ID] [dbo].[id] NOT NULL,
[Address_Type] [varchar](20) NOT NULL,
[Address_Primary] [int] NULL,
[Address_1] [varchar](50) NULL,
[Address_2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [char](2) NULL,
[Postal_Code] [varchar](10) NULL,
[Phone] [varchar](50) NULL,
[Fax] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[CBSA_code] [varchar](10) NULL,
[Latitude] [decimal](18, 15) NULL,
[Longitude] [decimal](18, 15) NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
[Object_ID] ASC,
[Address_Type] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


 --Indexes----
 
 CREATE NONCLUSTERED INDEX [_dta_index_Address_5_1847677630__K2_K1_K8_K6_K7_K4_K5_K11_K10_K9_3_12] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Object_ID] ASC,
[Postal_Code] ASC,
[City] ASC,
[State] ASC,
[Address_1] ASC,
[Address_2] ASC,
[Company] ASC,
[Fax] ASC,
[Phone] ASC
)
INCLUDE ( [Address_Primary],
[CBSA_code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Address_5_1847677630__K2_K7_K8_1_4_6_9_10_11_12] ON [dbo].[Address] 
(
[Address_Type] ASC,
[State] ASC,
[Postal_Code] ASC
)
INCLUDE ( [Object_ID],
[Address_1],
[City],
[Phone],
[Fax],
[Company],
[CBSA_code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Address_6_1713597343__K7_K2_K1_K4_6_8] ON [dbo].[Address] 
(
[State] ASC,
[Address_Type] ASC,
[Object_ID] ASC,
[Address_1] ASC
)
INCLUDE ( [City],
[Postal_Code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_Address_6_1847677630__K2_K1_K10] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Object_ID] ASC,
[Phone] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Address_i3] ON [dbo].[Address] 
(
[Postal_Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

  CREATE NONCLUSTERED INDEX [missing_index_110411] ON [dbo].[Address] 
(
[Address_Type] ASC,
[City] ASC,
[State] ASC,
[Postal_Code] ASC,
[Address_1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [missing_index_17978] ON [dbo].[Address] 
(
[Address_Type] ASC,
[State] ASC
)
INCLUDE ( [Object_ID],
[Address_Primary],
[Address_1],
[Address_2],
[City],
[Postal_Code],
[Phone],
[Fax],
[Company],
[CBSA_code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 30) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [missing_index_7331] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Address_1] ASC
)
INCLUDE ( [Object_ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 30) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [missing_index_Address_04_01_2010_1A] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Postal_Code] ASC
)
INCLUDE ( [Object_ID],
[Address_1],
[City],
[State]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

 CREATE NONCLUSTERED INDEX [missing_index_Address_04_02_2010_1A] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Postal_Code] ASC,
[Latitude] ASC,
[Longitude] ASC
)
INCLUDE ( [Object_ID],
[Address_1],
[City],
[State],
[CBSA_code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [missing_index_Address_06_03_2010_BY_Ananth] ON [dbo].[Address] 
(
[Address_Type] ASC,
[Latitude] ASC
)
INCLUDE ( [Object_ID],
[Address_1],
[City],
[State],
[Postal_Code]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
 ALTER TABLE [dbo].[Address] ADD  CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
[Object_ID] ASC,
[Address_Type] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

---Type_XREF-----------
CREATE TABLE [dbo].[Type_XREF](
[type_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[type_group] [varchar](50) NOT NULL,
[type_value] [varchar](50) NOT NULL,
[type_descr] [varchar](200) NULL,
[type_sort] [int] NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
[type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-----Indexes------------------
  CREATE NONCLUSTERED INDEX [ixtypegroup] ON [dbo].[Type_XREF] 
(
[type_group] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

 CREATE NONCLUSTERED INDEX [ixtypevalue] ON [dbo].[Type_XREF] 
(
[type_value] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

 ALTER TABLE [dbo].[Type_XREF] ADD  CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
[type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


--File_External_type----------------

CREATE TABLE [dbo].[File_External_Type](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Lender_ID] [int] NULL,
[Event_key] [varchar](50) NULL,
[Field_Name] [varchar](50) NULL,
[Flag_Send_to_lender] [varchar](10) NULL,
[External_Doc_Type] [varchar](200) NULL,
[External_Document_Name] [varchar](200) NULL,
[Flag_Sensitive_Info] [varchar](10) NULL,
[Comment] [varchar](200) NULL,
[Flag_Multifile] [bit] NULL,
[Form_field_id] [int] NULL,
 CONSTRAINT [PK_File_External_Type] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

------Indexes-----------

   ALTER TABLE [dbo].[File_External_Type] ADD  CONSTRAINT [PK_File_External_Type] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

---Property_Field-------
CREATE TABLE [dbo].[Property_Field](
[field_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[field_name] [nvarchar](100) NOT NULL,
[field_display] [nvarchar](200) NOT NULL,
[field_description] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK_Property_Field] PRIMARY KEY CLUSTERED 
(
[field_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO
---Indexes-------------
CREATE NONCLUSTERED INDEX [idxField_Name] ON [dbo].[Property_Field] 
(
[field_name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [PF_ixfield_name] ON [dbo].[Property_Field] 
(
[field_name] ASC,
[field_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Property_Field] ADD  CONSTRAINT [PK_Property_Field] PRIMARY KEY CLUSTERED 
(
[field_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

----Property_value---
CREATE TABLE [dbo].[Property_Value](
[Row_ID] [dbo].[id] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Field_ID] [int] NOT NULL,
[Property_ID] [dbo].[id] NOT NULL,
[field_value] [varchar](500) NOT NULL,
 CONSTRAINT [PK_Property_Value] PRIMARY KEY CLUSTERED 
(
[Row_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [AK1_Property_Value] UNIQUE NONCLUSTERED 
(
[Property_ID] ASC,
[Field_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Property_Value]  WITH NOCHECK ADD  CONSTRAINT [FK_Property_Field_Property_Value] FOREIGN KEY([Field_ID])
REFERENCES [dbo].[Property_Field] ([field_id])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Property_Value] CHECK CONSTRAINT [FK_Property_Field_Property_Value]
GO

ALTER TABLE [dbo].[Property_Value]  WITH NOCHECK ADD  CONSTRAINT [FK_Property_Property_Value] FOREIGN KEY([Property_ID])
REFERENCES [dbo].[Property] ([Property_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[Property_Value] CHECK CONSTRAINT [FK_Property_Property_Value]
GO


---Indexes---------
  ALTER TABLE [dbo].[Property_Value] ADD  CONSTRAINT [AK1_Property_Value] UNIQUE NONCLUSTERED 
(
[Property_ID] ASC,
[Field_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


 CREATE NONCLUSTERED INDEX [idx_property_value_PID_FID_FV] ON [dbo].[Property_Value] 
(
[Property_ID] ASC,
[Field_ID] ASC,
[field_value] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [IDX_Group2]
GO
 CREATE NONCLUSTERED INDEX [missing_index_275765] ON [dbo].[Property_Value] 
(
[Field_ID] ASC,
[field_value] ASC
)
INCLUDE ( [Property_ID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [IDX_Group2]
GO

 ALTER TABLE [dbo].[Property_Value] ADD  CONSTRAINT [PK_Property_Value] PRIMARY KEY CLUSTERED 
(
[Row_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


CREATE UNIQUE NONCLUSTERED INDEX [Property_Value_i1] ON [dbo].[Property_Value] 
(
[Field_ID] ASC,
[Property_ID] ASC,
[field_value] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [IDX_Group2]
GO

Answer 17

Try replacing the three correlated queries with 3 additional sets of joins (potentially left joins, if it's possible that no matches will be found).  It will be very likely be faster.  The repetition might look wrong to you, but keep in mind you have repetition already via 3 correlated subqueries.  To avoid confusing the issue, put them at the end (after the final Inner join).

Then, where you currently reference the correlated subquery's output, you reference the pv1/2/3 fields (now produced by the new joins, not via subqueries).  Assuming no matches found is still a possibility, keep the IsNull() function call.

You can probably squeeze a little more efficiency out of that first correlated subquery too, by JOINING FILE_EXTERNAL_TYPE to FILE_EXTERNAL_XREF instead of the EXISTS clause.  (Subqueries that return COUNTS are often reasonably well optimized automatically anyway, so that's probably less important to reframe as a join).

EDIT:  Here's a stab at the SQL.  It is one of those relatively rare cases where parentheses are required (or at least, really really helpful) within a JOIN.  I also did this mostly from memory, basing on one of my own rare cases, so given that I can't test this, you may discover an error or two).

SELECT DISTINCT fex.Property_ID  
     ,l.Public_Name as Seller_Name   
     ,p.Loan_Number  
     ,a.Address_1 as Address  
     ,a.City  
     ,a.State  
     ,UPPER(tx.type_value) as Property_Status  
     
     ,(SELECT COUNT(1)   
       FROM File_External_XREF fex1 (NOLOCK)   
       WHERE fex1.File_Status = @File_Status  
       AND fex1.Property_ID = fex.Property_ID    
       AND EXISTS (  
        SELECT 1 FROM File_External_Type fet (NOLOCK)   
        WHERE fet.External_Doc_Type = fex1.Doc_type   
        AND fet.External_Doc_Type != '')) as Received_Count
                 
     , CONVERT(varchar(20),ISNULL(pv1.field_value,'')) as FCL_Schedule_Dt   /* refers to join now */
      
     , CONVERT(varchar(20),ISNULL(pv2.field_value,'')) as FCL_Actual_Dt    /* refers to join now */ 
      
     , CONVERT(varchar(20),ISNULL(pv3.field_value,'')) as FCL_Redemption_Dt  /* refers to join now */
      
    FROM File_External_XREF fex (NOLOCK)  
    INNER JOIN Property p (NOLOCK) on p.Property_ID = fex.Property_ID  
    INNER JOIN Lender l (NOLOCK) on l.Lender_ID = p.Lender_ID  
    INNER JOIN Address a (NOLOCK) on a.Object_ID = p.Property_ID AND a.Address_Type = 'Property'  
    INNER JOIN Type_XREF tx (NOLOCK) on tx.type_id = p.Status  
    INNER JOIN File_External_Type fet (NOLOCK) on fet.External_Doc_Type = fex.Doc_type AND fet.External_Doc_Type != ''  
 
    LEFT JOIN  
      ( Property p1 (NOLOCK) 
        INNER JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p1.Property_ID  
        INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID AND pf1.field_name = 'FCL_Bid_Dt'  
      )  on P1.PropertyID fex.Property_ID

    LEFT JOIN
      ( Property p2 (NOLOCK) 
        INNER JOIN Property_Value pv2 (NOLOCK) ON pv2.Property_ID = p1.Property_ID  
        INNER JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv2.Field_ID AND pf2.field_name = 'fc_sale_date'  /* EDIT - had pv1 where should have been pv2 */
      )  on P2.PropertyID fex.Property_ID

    LEFT JOIN
      ( Property p3 (NOLOCK) 
        INNER JOIN Property_Value pv3 (NOLOCK) ON pv3.Property_ID = p3.Property_ID  
        INNER JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID AND pf3.field_name = 'redemption_date'  
      )  on P3.PropertyID fex.Property_ID

    
    WHERE fex.file_status IN (@File_Status)  
    AND p.Lender_ID = @Lender_id  

Answer 18

 

I tried Replacing the subquery with the LEFT JOINS but its still taking same time

 SELECT *    
   FROM (    
    SELECT DISTINCT fex.Property_ID    
     ,l.Public_Name as Seller_Name     
     ,p.Loan_Number    
     ,a.Address_1 as Address    
     ,a.City    
     ,a.State    
     ,UPPER(tx.type_value) as Property_Status    
     ,(SELECT COUNT(1)     
       FROM File_External_XREF fex1 (NOLOCK)     
       WHERE fex1.File_Status = @File_Status    
       AND fex1.Property_ID = fex.Property_ID      
       AND EXISTS (    
        SELECT 1 FROM File_External_Type fet (NOLOCK)     
        WHERE fet.External_Doc_Type = fex1.Doc_type     
        AND fet.External_Doc_Type > '')) as Received_Count  
        --,pv1.field_value AS FCL_Schedule_Dt 
        --,pv2.field_value AS FCL_Actual_Dt
        --,pv3.field_value AS FCL_Redemption_Dt

    FROM File_External_XREF fex (NOLOCK)    
    INNER JOIN Property p (NOLOCK) on p.Property_ID = fex.Property_ID    
    INNER JOIN Lender l (NOLOCK) on l.Lender_ID = p.Lender_ID    
    INNER JOIN Address a (NOLOCK) on a.Object_ID = p.Property_ID AND a.Address_Type = 'Property'    
    INNER JOIN Type_XREF tx (NOLOCK) on tx.type_id = p.Status    
    INNER JOIN File_External_Type fet (NOLOCK) on fet.External_Doc_Type = fex.Doc_type AND fet.External_Doc_Type > ''   
LEFT  JOIN Property_Value pv1 (NOLOCK) ON pv1.Property_ID = p.Property_ID    
      LEFT JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID AND  pf1.field_name = 'FCL_Bid_Dt'
 LEFT JOIN Property_Value pv2 (NOLOCK) ON pv2.Property_ID = p.Property_ID    
      LEFT JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv1.Field_ID AND  pf2.field_name = 'fc_sale_date'
 LEFT JOIN Property_Value pv3 (NOLOCK) ON pv3.Property_ID = p.Property_ID    
      LEFT JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID AND  pf3.field_name = 'redemption_date'


    WHERE fex.file_status =@File_Status
    AND p.Lender_ID = @Lender_id    
   ) x    
   ORDER BY Received_Count DESC    

  END 

        

Answer 19

Well, on closer inspection, I saw (a) a typo in my earlier reply, and (b) realized there are two unnecessary joins on Property Value, so at the very least, this (below) eliminates a couple extra joins: 

SELECT DISTINCT fex.Property_ID  
     ,l.Public_Name as Seller_Name   
     ,p.Loan_Number  
     ,a.Address_1 as Address  
     ,a.City  
     ,a.State  
     ,UPPER(tx.type_value) as Property_Status  
     
     ,(SELECT COUNT(1)   
       FROM File_External_XREF fex1 (NOLOCK)   
       WHERE fex1.File_Status = @File_Status  
       AND fex1.Property_ID = fex.Property_ID    
       AND EXISTS (  
        SELECT 1 FROM File_External_Type fet (NOLOCK)   
        WHERE fet.External_Doc_Type = fex1.Doc_type   
        AND fet.External_Doc_Type != '')) as Received_Count
                 
     , CONVERT(varchar(20),ISNULL(pv1.field_value,'')) as FCL_Schedule_Dt   
      
     , CONVERT(varchar(20),ISNULL(pv2.field_value,'')) as FCL_Actual_Dt     
      
     , CONVERT(varchar(20),ISNULL(pv3.field_value,'')) as FCL_Redemption_Dt  
      
    FROM File_External_XREF fex (NOLOCK)  
    INNER JOIN Property p (NOLOCK) on p.Property_ID = fex.Property_ID  
    INNER JOIN Lender l (NOLOCK) on l.Lender_ID = p.Lender_ID  
    INNER JOIN Address a (NOLOCK) on a.Object_ID = p.Property_ID AND a.Address_Type = 'Property'  
    INNER JOIN Type_XREF tx (NOLOCK) on tx.type_id = p.Status  
    INNER JOIN File_External_Type fet (NOLOCK) on fet.External_Doc_Type = fex.Doc_type AND fet.External_Doc_Type != ''  
 
    LEFT JOIN Property_Value pv (NOLOCK)  on PV.PropertyID fex.PropertyID
    LEFT JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv.Field_ID AND pf1.field_name = 'FCL_Bid_Dt'  
    LEFT JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv.Field_ID AND pf2.field_name = 'fc_sale_date'  
    LEFT JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv.Field_ID AND pf3.field_name = 'redemption_date'  

    
    WHERE fex.file_status IN (@File_Status)  
    AND p.Lender_ID = @Lender_id  

Answer 20

Also (one battle at a time though), this looks like a way to eliminate the EXISTS clause in the first subquery.

     ,(SELECT COUNT(1)   
         FROM File_External_XREF fex1 (NOLOCK)   
        INNER JOIN File_External_Type fet (NOLOCK)   ON fet.External_Doc_Type = fex1.Doc_type   
			                                        AND fet.External_Doc_Type != ''
        WHERE fex1.File_Status = @File_Status  
          AND fex1.Property_ID = fex.Property_ID    
	   ) as Received_Count

Answer 21

From where I will get PV2.field_value ,Pv3.field_value of if we are only joining property_value 1 time
, CONVERT(varchar(20),ISNULL(pv1.field_value,'')) as FCL_Schedule_Dt   
      
     , CONVERT(varchar(20),ISNULL(pv2.field_value,'')) as FCL_Actual_Dt     
      
     , CONVERT(varchar(20),ISNULL(pv3.field_value,'')) as FCL_Redemption_Dt  

Answer 22

Oh, sorry about that:  Ok, take two steps back and one step forward:  Try this (and the parentheses will be required).  (This may not speed things up much, but I bet the results had veered off course with the dual-left-joins.)

    LEFT JOIN 
      ( Property_Value pv1 (NOLOCK)  
        INNER JOIN Property_Field pf1 (NOLOCK) ON pf1.field_id = pv1.Field_ID AND pf1.field_name = 'FCL_Bid_Dt'  
      )  on PV1.PropertyID fex.Property_ID

    LEFT JOIN
      ( Property_Value pv2 (NOLOCK) 
        INNER JOIN Property_Field pf2 (NOLOCK) ON pf2.field_id = pv2.Field_ID AND pf2.field_name = 'fc_sale_date'  
      )  on PV2.PropertyID fex.Property_ID

    LEFT JOIN
      ( Property_Value pv3 (NOLOCK)  
        INNER JOIN Property_Field pf3 (NOLOCK) ON pf3.field_id = pv3.Field_ID AND pf3.field_name = 'redemption_date'  
      )  on PV3.PropertyID fex.Property_ID

Answer 23

Thanks John . It worked 

Answer 24

Awesome!  What sort of speed difference from before to after?  It's always good to know what did or didn't work.

Answer 25

Query improved from 2:43 to 43 seconds