I have written below query to retrieve distinct RegNo from two different tables. But below query takes nearly 25 seconds to retrieve results. In Inventory table more than 1.5 million records are there.
Select F.PKID, F.RegNo
From
(
Select E.PKID, E.RegNo
Row_Number() Over(Order By E.RegNo Asc) RowNo
From
(
Select C.PKID, C.RegNo
From
(
Select Pk_Id PKID, LTrim(RTrim(A.Reg_No)) RegNo,
Row_Number() Over(Partition By LTrim(RTrim(A.Reg_No))
Order By (Select Null)) RegRowNo
From dbo.KeyreferenceDetails A (NoLock)
Where A.KeyreferenceStatus = 'L'
And A.Reg_No Like @Value And IsNull(Reg_No, '') <> '' And Not Exists
(
Select 1 From dbo.INVENTORY B (NoLock)
Where A.Reg_No = B.Inv_H_Reg_No
)
) C
Where C.RegRowNo = 1 And IsNull(C.RegNo, '') <> '-'
Union
Select D.PKID, D.RegNo
From
(
Select Pk_ID PKID, LTrim(LTrim(Txt_RegNo)) RegNo,
Row_Number() Over(Partition By LTrim(LTrim(A.Txt_RegNo))
Order By (Select Null)) RegRowNo
From dbo.MobileMessageDetails A (Nolock)
Left Join dbo.PLACE P (Nolock) On P.Place_Shrt_Code = A.Txt_YarddCode
And P.[Status] = 'L'
Left Join dbo.INVENTORY B (Nolock) On A.Txt_RegNo = B.Inv_H_Reg_No
Where A.Txt_INOUT In('IN', 'MOBILE') And IsNull(A.Txt_RegNo, '') <> '' And B.Inv_H_Pk_Id Is Null
And A.[Status] = 'L' And Txt_RegNo Like @Value
) D
Where D.RegRowNo = 1 And IsNull(D.RegNo, '') <> '-'
) E
) F
Where F.RowNo > 0 And F.RowNo <= 20
Query Plan:
Available Indexes:
KeyreferenceDetails table:
Index Name ---------------+ Column Name ----------------- + Index Type
IX_KeyreferenceDetails_I | Reg_No | NONCLUSTERED
IX_KeyreferenceDetails_II | KeyreferenceStatus | NONCLUSTERED
Inventory table:
Index Name ---------------+ Column Name ----------------- + Index Type
IX_Inventory_I | Inv_H_Reg_No | NONCLUSTERED
MobileMessageDetails table:
Index Name --------------- + Column Name ----------------- + Index Type
IX_MobileMessageDetails_I | Txt_RegNo | NONCLUSTERED
IX_MobileMessageDetails_II | Txt_INOUT | NONCLUSTERED
Place table:
Index Name ---------------+ Column Name ----------------- + Index Type
IX_Place_I | Place_Shrt_Code | NONCLUSTERED
IX_Place_I | Status | NONCLUSTERED
I have created required indexes for all the used tables in above query. But query cost is high. How to reduce query running time in SQL server?
Statistics Output:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'INVENTORY'. Scan count 6, logical reads 382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KeyreferenceDetails'. Scan count 15, logical reads 9062, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Mobile_MessageDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TempItemsCount_____________________________________________________________________________________________________0000000118A9'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 20733 ms, elapsed time = 7844 ms.
Table 'INVENTORY'. Scan count 6, logical reads 382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'KeyreferenceDetails'. Scan count 14, logical reads 9062, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Mobile_MessageDetails'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#VehicleRegDetails__________________________________________________________________________________________________0000000118AB'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 21139 ms, elapsed time = 8146 ms.
Table '#TABLE_SCHEMA_______________________________________________________________________________________________________0000000118AA'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Update:
Insert Into #TempItemsCount(TotalCount)
Select Count(E.PKID)
From
(
Select E.PKID, E.RegNo
Row_Number() Over(Order By E.RegNo Asc) RowNo
From
(
Select C.PKID, C.RegNo
From
(
Select Pk_Id PKID, LTrim(RTrim(A.Reg_No)) RegNo,
Row_Number() Over(Partition By LTrim(RTrim(A.Reg_No))
Order By (Select Null)) RegRowNo
From dbo.KeyreferenceDetails A (NoLock)
Where A.KeyreferenceStatus = 'L'
And A.Reg_No Like @Value And IsNull(Reg_No, '') <> '' And Not Exists
(
Select 1 From dbo.INVENTORY B (NoLock)
Where A.Reg_No = B.Inv_H_Reg_No
)
) C
Where C.RegRowNo = 1 And IsNull(C.RegNo, '') <> '-'
Union
Select D.PKID, D.RegNo
From
(
Select Pk_ID PKID, LTrim(LTrim(Txt_RegNo)) RegNo,
Row_Number() Over(Partition By LTrim(LTrim(A.Txt_RegNo))
Order By (Select Null)) RegRowNo
From dbo.MobileMessageDetails A (Nolock)
Left Join dbo.PLACE P (Nolock) On P.Place_Shrt_Code = A.Txt_YarddCode
And P.[Status] = 'L'
Left Join dbo.INVENTORY B (Nolock) On A.Txt_RegNo = B.Inv_H_Reg_No
Where A.Txt_INOUT In('IN', 'MOBILE') And IsNull(A.Txt_RegNo, '') <> '' And B.Inv_H_Pk_Id Is Null
And A.[Status] = 'L' And Txt_RegNo Like @Value
) D
Where D.RegRowNo = 1 And IsNull(D.RegNo, '') <> '-'
) E
)