Adding Distinct() causing huge performance issue (Sometimes only)

Question

I am using .net MVC as server side ,Entity Framework as ORM and ORACLE 11g as Database

The issue is one of our query takes long time to run, this is not happening always sometimes only this happen. If its normal case then it will get completed in milliseconds. But some time it takes 4-5 min to complete.

var alphaList= _dBontext.SampleVIEW
                    .Where(it => it.CODE == code)
                    .Select(it => it.NAME.Substring(0, 1)).Distinct()
                    .ToArray();

Because of this performance issues I have split it up the above query into two parts like below

            var discList = _dBontext.SampleVIEW
                .Where(it => it.CODE == code)
                .Select(it => it.NAME).ToList();

  var alphaList = discList.Select(s => s.Substring(0, 1)).Distinct().ToArray();

This time i dont see any huge performance issues.

Then for testing purpose I just added a Distinct() into the above query like below

 var discLists =  _dBontext.SampleVIEW
                    .Where(it => it.CODE == code)
                    .Select(it => it.NAME).Distinct().ToList();

This time again the performance issue is coming. The problem is i cannot find the performance issues in this query always. Rarely only this happen. And then it takes 4-5 min to execute this query.

I know using Distinct() will try to order the items and this ordering may be costly compared to normal query. But my doubt is if the issue is related to Distinct then why its not coming always ?? If the issue is not related to Distinct() then what all are the other possibilities ?

Note : I don't have much data in this View . Maximum 5k data only there now.

I already went through This Question. But my context is different And the query performance issue is not coming always.

Update :

Below is the execution plan of the query

"PLAN_TABLE_OUTPUT"
"Plan hash value: 1907157887"
" "
"------------------------------------------------------------------------------------------------------------------"
"| Id  | Operation                 | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |"
"------------------------------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT          |                              | 39901 |  3312K|       |  1664   (6)| 00:00:01 |"
"|   1 |  HASH UNIQUE              |                              | 39901 |  3312K|  3640K|  1664   (6)| 00:00:01 |"
"|*  2 |   HASH JOIN RIGHT OUTER   |                              | 39901 |  3312K|       |   857   (8)| 00:00:01 |"
"|   3 |    TABLE ACCESS FULL      | BAL_TBL       |   980 |  9800 |       |    14   (0)| 00:00:01 |"
"|*  4 |    HASH JOIN              |                              |  4072 |   298K|       |   840   (8)| 00:00:01 |"
"|*  5 |     INDEX FAST FULL SCAN  | CVB_USERS_PRI               |  3432 | 58344 |       |     7   (0)| 00:00:01 |"
"|*  6 |     HASH JOIN OUTER       |                              | 11865 |   672K|       |   832   (8)| 00:00:01 |"
"|   7 |      NESTED LOOPS         |                              | 11681 |   501K|       |   272   (9)| 00:00:01 |"
"|   8 |       MERGE JOIN CARTESIAN|                              | 11681 |   387K|       |   262   (6)| 00:00:01 |"
"|   9 |        INDEX FULL SCAN    | TESTV_PROFILE_PRI             |     1 |     9 |       |     1   (0)| 00:00:01 |"
"|  10 |        BUFFER SORT        |                              | 11681 |   285K|       |   261   (6)| 00:00:01 |"
"|  11 |         TABLE ACCESS FULL | PDETAILS_TBL_OTHERS_TAX    | 11681 |   285K|       |   261   (6)| 00:00:01 |"
"|* 12 |       INDEX UNIQUE SCAN   | XYZ_CTL_LIST_OTH_TAX_PR2_IDX |     1 |    10 |       |     0   (0)| 00:00:01 |"
"|* 13 |      TABLE ACCESS FULL    | P_INFO            | 26432 |   361K|       |   557   (7)| 00:00:01 |"
"------------------------------------------------------------------------------------------------------------------"
" "
"Query Block Name / Object Alias (identified by operation id):"
"-------------------------------------------------------------"
" "
"   1 - SEL$3258542E"
"   3 - SEL$3258542E / ACCTBAL@SEL$2"
"   5 - SEL$3258542E / VUSERS@SEL$4"
"   9 - SEL$3258542E / VPRO@SEL$4"
"  11 - SEL$3258542E / A@SEL$3"
"  12 - SEL$3258542E / B@SEL$3"
"  13 - SEL$3258542E / C@SEL$3"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
"   2 - access(TRIM(""ACCTBAL"".""ID""(+))=TRIM(""A"".""ID""))"
"   4 - access(TRIM(TO_CHAR(""C"".""ID1""))=TRIM(""VUSERS"".""ID1"") AND "
"              TRIM(""VPRO"".""SAMPLECODE"")=TRIM(""VUSERS"".""SAMPLECODE""))"
"   5 - filter(""VUSERS"".""SAMPLECODE""='ABCTESTV1')"
"   6 - access(""A"".""ID""=""C"".""ID""(+))"
"  12 - access(""A"".""ID""=""B"".""ID"")"
"  13 - filter(""C"".""ID""(+) IS NOT NULL)"
" "
"Column Projection Information (identified by operation id):"
"-----------------------------------------------------------"
" "
"   1 - (#keys=1) ""LNAME""||', '||""FNAME""[62]"
"   2 - (#keys=1) ""LNAME""[VARCHAR2,30], ""FNAME""[VARCHAR2,30]"
"   3 - ""ACCTBAL"".""ID""[CHARACTER,9]"
"   4 - (#keys=2) ""A"".""ID""[CHARACTER,9], ""LNAME""[VARCHAR2,30], ""FNAME""[VARCHAR2,30]"
"   5 - ""VUSERS"".""SAMPLECODE""[VARCHAR2,20], ""VUSERS"".""ID1""[VARCHAR2,10]"
"   6 - (#keys=1) ""A"".""ID""[CHARACTER,9], ""VPRO"".""SAMPLECODE""[VARCHAR2,20], ""LNAME""[VARCHAR2,30], "
"       ""FNAME""[VARCHAR2,30], ""C"".""ID1""[NUMBER,22]"
"   7 - (#keys=0) ""VPRO"".""SAMPLECODE""[VARCHAR2,20], ""A"".""ID""[CHARACTER,9], ""LNAME""[VARCHAR2,30], "
"       ""FNAME""[VARCHAR2,30]"
"   8 - (#keys=0) ""VPRO"".""SAMPLECODE""[VARCHAR2,20], ""A"".""ID""[CHARACTER,9], ""LNAME""[VARCHAR2,30], "
"       ""FNAME""[VARCHAR2,30]"
"   9 - ""VPRO"".""SAMPLECODE""[VARCHAR2,20]"
"  10 - (#keys=0) ""A"".""ID""[CHARACTER,9], ""LNAME""[VARCHAR2,30], ""FNAME""[VARCHAR2,30]"
"  11 - (rowset=200) ""A"".""ID""[CHARACTER,9], ""LNAME""[VARCHAR2,30], ""FNAME""[VARCHAR2,30]"
"  13 - ""C"".""ID""[CHARACTER,9], ""C"".""ID1""[NUMBER,22]"
" "
"Note"
"-----"
"   - dynamic statistics used: dynamic sampling (level=2)"
"   - 3 Sql Plan Directives used for this statement"

Well you'll have to post 1) the actual query going to the database, 2) the view definition (which is aparently not trivial when sorting 5K row takes minutes) and the execution plan (possible you have two of them the *good* and the *bad* one). See some hints [here](https://stackoverflow.com/a/34975420/4808122) — Marmite Bomber, May 29 '20 at 06:21
@MarmiteBomber actual query is select distinct Name from viewname; — Arunprasanth K V, May 29 '20 at 07:17
@MarmiteBomber SInce its production item I cannot provide more details. But i will come up with the details as much possible — Arunprasanth K V, May 29 '20 at 07:21
It sould be at least possible to post the *execution plan* of the query (with anonymized table and index names) — Marmite Bomber, May 29 '20 at 08:25
The query *should be* something like this `select distinct Name from viewname where code = ?`. If the `WHERE` is realy missing you have already the answer;) — Marmite Bomber, May 29 '20 at 08:34
@MarmiteBomber added the execution plan of the query . Please have a look — Arunprasanth K V, May 31 '20 at 04:35

Adding Distinct() causing huge performance issue (Sometimes only)

0 Answers0