Very slow OracleDataReader.Read or HasRows ???
Hi,
I’m experiencing huge performance delays when calling Read on OracleDataReader …. It takes for ages ….. I waited for 2 minutes then stopped the execution in my .NET application…
If I run the same SQL statement in Toad for Oracle it returns in less than a second ….. Why ???
The only difference is that in .NET I call ExecuteReader to fetch rows while using parameters …. In Toad I just execute generated SQL statement as seen below.
I’m using System.Data.OracleClient.dll from .NET framework 2.0 …. Tried 3.5 , same result.
SQL:
SELECT TITLE,SUBTITLE,AUTHOR,ART_DATE,MEDIA_CODE,FULLTEXT,ID,STARTTIME,ON_PAGE,COORDINATES,COOR_TEXT,THUMBS_NR,CATEGORY,SHOW,CREATE_DATE,GENRE,USER_LAST,DURATION FROM MS_ARTICLE WHERE CREATE_DATE >= TO_DATE('10.12.2008 09:38:16','dd.MM.yyyy HH24:MI:SS') AND (MEDIA_COUNTRY = 'SLOVENIJA' AND INSTR(CHAR_14, ' METRONET',1,1)>0 )
Execution plan is:
Plan
SELECT STATEMENT ALL_ROWS Cost: 4 Bytes: 550 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE PRESCLIP.MS_ARTICLE Cost: 4 Bytes: 550 Cardinality: 1
1 INDEX RANGE SCAN INDEX PRESCLIP.MS_ARTICLE_CREATE_DATE_INDEX Cost: 3 Cardinality: 1
Any ideas why ?