CODEWITHSUNDEEP
sqloracle-databaseoracle11gquery-optimizationperformance-testing
nnmmss
nnmmss

Reputation: 2974

speeding up records fetch time

I have a SQL sentence which takes around 44 Sec to fetch the data. how can I reduce the time ?

this is the sql

Select AVNR,replace(to_char((CAST(DBTM as timestamp)),'hh24:mi'),'00:00','24:00') as ptime,
        Wert
From e_mw_60min_me
Where to_char((CAST ( DBTM as TIMESTAMP)), 'DD-MM-YYYY') = '13-03-1396'  and
      AVNR In (1141,1142,1144,10335,10336,12016,1146,1147,1149,1129,1130,1132,1134,1135,1137,5895,5896,5900,8906,8907,8909,8901,8902,8904,8940,8941,8943,8951,8952,8954,8972,8973,8975,8830,8831,8833,8835,8836,8838,1113,1982,1984,2314,2315,2317,3272,3273,3275,3267,3268,3270,3262,3263,3265,10231,10136,9066,8779,8780,8782,8774,8775,8777,8320,8321,8323,7696,7697,7699,10486,10487,10489,3329,3330,12018,3322,3328,10132,3320,3321,12017,3222,3223,3225,686,687,689,691,692,694,696,697,699,1,2,4,10527,10528,10529,4911,4912,4914,4917,4918,4920,5162,5163,5166,5157,5158,5160,5168,5169,5171,5449,5450,5452,10116,10117,10119,10120,10121,10123,2271,2272,2279,2266,2267,2269,2259,2260,2262,1292,1293,1295,5380,5381,5383,5374,5375,5377,10545,10546,10547,3281,10126,12031,3244,3245,12030,10983,10984,12033,10987,10989,12032,11073,11074,1125,11079,11080,3333,105,106,108,100,101,103,93,94,96,29,30,32,95,102,1197,124,123,126,12085,12086,12087,2520,2521,2523,2525,2526,2528,2515,2516,2518,2510,2511,2513,5444,5445,5447,2201,2202,12025,3336,3337,3339,3002,3003,12029,1643,1644,1646,1609,1610,1612,1596,1597,1599,9717,9718,9720,9722,9723,9725,2146,2147,2149,2141,2142,2144,2136,2137,2139,2131,2132,2134,2121,2122,2124,2126,2127,2129,2635,2636,2638,2641,2642,2644,7499,7500,7502,4610,4611,4613,4605,4606,4608,4600,4601,4603,9074,9075,9077,9079,9080,9082,9235,9236,9238,9240,9241,9243,9245,9246,9248,8468,8469,8471,5785,5786,5788,5790,5791,5793,5691,5692,5694,5685,5686,5688,8455,8456,8458,11312,11313,4588,7654,7655,7657,9376,9377,9379,9371,9372,9374,9382,9383,9385,5918,5919,5922,5934,5935,5938,5912,5913,5916,10963,7860,10964,135,136,138,10658,10659,10664,10660,10661,10662,5173,5458,5460);

 //these AVNRs are completley Dynamic

the Table is like :

  1. AVNR (Like ID Number)
  2. DBTM( reocrds every time for every points)
  3. Wert (the value)

I want to know any other sql command which makes it faster to fetch

Upvotes: 0

Views: 83

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

If DBTM is a date you don't need to cast it to a timestamp; and if there is an index on that then you should not convert it to a string to compare with another string - leave it as a date and convert the fixed value to a date too, and compare with a range that covers the entire day:

select AVNR,
  replace(to_char(DBTM,'hh24:mi'),'00:00','24:00') as ptime,
  Wert
from e_mw_60min_me
where DBTM >= date '1396-03-13'
and DBTM < date '1396-03-14'
and AVNR In (1141, ...);

I've used date literals but you can use to_date() if you are starting from a variable string:

where DBTM >= to_date('13-03-1396', 'DD-MM-YYYY')
and DBTM < to_date('13-03-1396', 'DD-MM-YYYY') + 1
and AVNR In (1141, ...);

You should also consider creating a collection for all the AVNR values you are looking for and using member of instead if IN, or explode the collection and join to it. It depends where those values are coming from though.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can add an index:

create index idx_e_mw_60min_me_2 on (to_char((CAST ( DBTM as TIMESTAMP)), 'DD-MM-YYYY'), AVNR)

This exactly matches the where clause so it should speed up the query.

Upvotes: 1

Related Questions