DataSet Performance issue RRS feed

  • Question

  • HI

    We are using a oracle stored procedure which returns a refcursor. For queries with like search which returns large number records the dataadapter.fill() is really takes time.

    For a case whcih returns 14000 records it took 3000 ms to perform the fill method, eventhou gh we are filtering by 99 recodrs using fill(ds, start, maxrecords).

    As the number of records increasing the time varies even if the filtering happens only for 99 records.

    Any idea how we can improve the performance. Or any other we can call the SP which gives any improvement?

    We cannot modify the SP. And the DAL used by many diffrerent componenets.

    Thanks in advance




    Saturday, December 2, 2006 9:33 AM

All replies

  • Sounds like you've got index trouble, baby. Could be that Oracle's query optimizations are less-than-ideal in this case.

    Have a look at the execution plan for the queries in the SP (using the syntax "explain plan for select * from...") and see if you've got any full table scans.

    If you can't modify the SP, I sure hope you can modify the relevant table indices.

    EDIT: Or maybe I'm misreading. Are there 14000 rows in the table, or are you *selecting* 14000 rows and only showing 99? If the latter, your problem is pretty obvious: You're selecting 14000 rows.
    Saturday, December 2, 2006 10:13 AM
  • Sharmila

        I'm just curious. The application really needs the 14K records in the cursor? Are all of them individually relevant? Assuming the answer is "yes, they are", does the application needs to visit each of them in an order different than the sequential one? If the answer is "No, they are visited in a sequential order" so, the Oracle DataReader is a more suitable manner to deal with them without performance issues

    Sunday, December 3, 2006 4:19 AM
  • Hi

    The command we used to filter the records is

    da.fill(ds, start, maxrecords). Hope this will retireve only maxrecords. We have tried with datareader also , the looping is taking same time as fill() method.

    Any idea, this is because of refcursor?




    Monday, December 4, 2006 7:53 AM
  • Have you checked if it's an index problem?
    Tuesday, December 5, 2006 1:28 AM
  • Sharmila,

    All the 3000 ms may not be for just filling the dataset. I think it is due to the network speed or bandwidth. Transfering 14000 records from database server to front-end will take lot of time. So I feel most of the time in 3000ms is going to transfer data from database to front-end. It is always better to send from database as many records as you can see in the front end in a single page. So if not now, use the paging in stored procedure by requesting the page size and page index from front-end.

    Happy programming.


    Tuesday, December 5, 2006 8:41 AM
  • The call to fill with not improve much by switching to datareader, cause fill uses a datareader internally.

    On what kind of field are you doing the like statement?

    Tuesday, December 5, 2006 11:42 AM
  • Sharmila

       you can do a quick test, in a non-production database try to replicate a sample of records. Not the 14K but those 99 you mentioned you are filtering

       In that testing database, create a stored procedure which, by definition, gives an answer of just the 99 records or so, I mean, just those relevant records you need and nothing else

       If response times are better, as "Formerly Known as ..." suggested, your issue isn't a matter of DataReader, DataSets or so: your "performance-ache" is due to the high volume you are unnecessarily pulling


       If you confirm that, I don't want to hurt the hearth and mind of anyone in your organization... but it seems that the solution will pass through stop using the SP you are using nowadays and start using something with a narrowed outcome

       Don't you have a DBA in your org, able to quantify the stress the database is being submitted and possible workarounds?

    Tuesday, December 5, 2006 5:04 PM