DataSet Performance issue
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
Sharmila
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.
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
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?
Thanks
Sharmila
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.
Suresh
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?
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?
