Hello!
I wrote an application that lets user select item from a grid (items are rows from an Oracle view). Grid has two important features:
1) Items can be sorted by all columns
2) After opening window with this grid, selected item has to be the same, which was selected when it was last closed
What my app id doing is:
1) Open TSmartQuery (with sorting made by using ORDER BY on Oracle side)
2) Find proper item using FindKey (app remembers primary key value of the last selected item)
And now the problem: everything was fine when there was 2000-5000 records in the Oracle view, but now there is about 25000 items (an will be much more in the future) and opening window with this grid takes up to 1.6 seconds. As I found Open takes 0.15-0.2 seconds, the rest is taken by Locate. It depends on selected item (the further it is in the order the longer it takes). Time is taken mainly by getting data of records between the first and the selected (needed to find the right one) from Oracle server. Is there any way to tell SmartQuery to open and locate record by its ID on the server side, then send data (selected record and few neighbour records visible in the grid) to client computer?
What I was thinkink of is expanding (I'm not sure if its the exact name, its the feature of ODAC that lets me get values of only few fields when I open dataset, and get rest of fields when I need it), but I need to show values of all fields in the grid. I couldn't figure out, how to make SmartQuery to do that (automatically expand rows that are visible in the grid).
Could you help me? If ODAC cannot solve my problem I will appreciate any other hints you would give me.
best regards
Jacek
How to omit Open and Locate
ODAC doesn't have a feature of fetching records starting with specified primary key value.
You should not show so many records in the grid. You can filter records using a filter entered by the user, or limit number of records using ROWNUM:
SELECT * FROM MyTable
WHERE rownum <= 1000
Expanded fields values are shown only when you edit a record. You cannot use this feature for visible records in the grid.
You should not show so many records in the grid. You can filter records using a filter entered by the user, or limit number of records using ROWNUM:
SELECT * FROM MyTable
WHERE rownum <= 1000
Expanded fields values are shown only when you edit a record. You cannot use this feature for visible records in the grid.
I cannot use solution with rownum, becouse I have to find a specified record after showing grid. If the record would be for example 2000th in the order (while my select gets for example 1000 recs), then it would not be found.
I added about 400 000 recs to my table, and still everythig works perfect apart of searching for specified record after Open.
My idea to solve this problem is:
1) get record number:
select r, id_artykulu
from (select v_lista_artykulow.id_artykulu, row_number() over (order by nazwa) r
from v_lista_artykulow where .....
)
WHERE id_Artykulu=30507;
It returns let's say 15000 (which means it is 15000th rec in v_lista_artykulow ordered by field nazwa)
2) somehow tell the DataSet before Opening, to get only 15000th record and few, maybe 500, preceeding and following (that preserves me from waiting to load firts 14500 recs):
select *
from (
select v_lista_artykulow.*, row_number() over (order by nazwa) r
from v_lista_artykulow where .....
)
where r between 14500 and 15500;
3) DataSet should know, how much records is there in the table, and should fetch them when needed.
Is there a way to make SmartQuery (or any other ODAC) to to that?
What I'm thinkink of is to use TVirtualTable to be main DataSet and OraQuery to load data into VirtualTable, but it's to early to say whether it works.
I added about 400 000 recs to my table, and still everythig works perfect apart of searching for specified record after Open.
My idea to solve this problem is:
1) get record number:
select r, id_artykulu
from (select v_lista_artykulow.id_artykulu, row_number() over (order by nazwa) r
from v_lista_artykulow where .....
)
WHERE id_Artykulu=30507;
It returns let's say 15000 (which means it is 15000th rec in v_lista_artykulow ordered by field nazwa)
2) somehow tell the DataSet before Opening, to get only 15000th record and few, maybe 500, preceeding and following (that preserves me from waiting to load firts 14500 recs):
select *
from (
select v_lista_artykulow.*, row_number() over (order by nazwa) r
from v_lista_artykulow where .....
)
where r between 14500 and 15500;
3) DataSet should know, how much records is there in the table, and should fetch them when needed.
Is there a way to make SmartQuery (or any other ODAC) to to that?
What I'm thinkink of is to use TVirtualTable to be main DataSet and OraQuery to load data into VirtualTable, but it's to early to say whether it works.
First of all it is not a good practice to make a full table select. It is only acceptable when you need to run a batch process that need to be applied to all table records.
Most of the applications have an objective when showing records and the user should be able to enclose the search into reasonable limits. Imagine Google exposing all its database records to let you show what you would like to find ..
I guess your "where" clauses can have some control columns to limit the number of records to fetch.
If you may tell us the objective of your application and the sctructure of the tables we might help you in a better way.
Most of the applications have an objective when showing records and the user should be able to enclose the search into reasonable limits. Imagine Google exposing all its database records to let you show what you would like to find ..
I guess your "where" clauses can have some control columns to limit the number of records to fetch.
If you may tell us the objective of your application and the sctructure of the tables we might help you in a better way.
Re: How to omit Open and Locate
I have to get back to this issue, as the problem is getting serious now for me.
First of all - jjeffman - I do not want to perform full table select, ODAC does it. I want ODAC to select all fields only for records visible for user in DBGrid. I do not understand why ODAC fetches all 100000 records when user wants to see only 20 last records. I think it could be solved in quite easy way: there should be two SQL queries, one for selecting key fields for all records, keys are usually number fields, do it would not be much data. Second SQL query would retrieve the rest of fields by key fields found by first query for desired records. In this case DataSet would have to fetch key columns for all records sorted and filtered, when data would need to be displayed DataSet could use second query to retrieve all fields.
Does current version od ODAC provide solution of my problem?
First of all - jjeffman - I do not want to perform full table select, ODAC does it. I want ODAC to select all fields only for records visible for user in DBGrid. I do not understand why ODAC fetches all 100000 records when user wants to see only 20 last records. I think it could be solved in quite easy way: there should be two SQL queries, one for selecting key fields for all records, keys are usually number fields, do it would not be much data. Second SQL query would retrieve the rest of fields by key fields found by first query for desired records. In this case DataSet would have to fetch key columns for all records sorted and filtered, when data would need to be displayed DataSet could use second query to retrieve all fields.
Does current version od ODAC provide solution of my problem?
Re: How to omit Open and Locate
The described functionality is already available in ODAC for a long time. For this, use the FetchAll property: https://www.devart.com/odac/docs/?devar ... tchall.htm