tables into memory
lazarus / 16-Databases

----------------------------------------------------------------------
Hansvb · 2016-01-09 07:35

I want to load 2 large oracle tables into memory and then query them.
What is the best way to start/do this? I was thinking of loading them
into zeos ztable.

----------------------------------------------------------------------
LacaK · 2016-01-09 08:28

You cn use any in memory dataset like TBufDataSet or TMemDataSet, but
they have limited "query" capabilities. You can scan records from
first to last, locate, filter records etc., but you can not issue
"select"

----------------------------------------------------------------------
Hansvb · 2016-01-09 10:20

I found the wiki tbufdatabase. I need to query them with sql. Is there
a way to speed up querys. I know that zeos7.2 is faster then 7.14 but
that is not much faster. I have indexes on the t he tables to speed
things up?

----------------------------------------------------------------------
taazz · 2016-01-09 10:25

Quote from: Hansvb on January 09, 2016, 11:20:30 amI found the wiki
tbufdatabase. I need to query them with sql. Is there a way to speed
up querys. I know that zeos7.2 is faster then 7.14 but that is not
much faster. I have indexes on the t he tables to speed things up?
why? Why do you want to copy data through lan in to your memory, 
create secondary indices in your client's memory, write some sql
parser that supports select (badly usually) and execute selection on
the data? What is wrong with letting the server execution the
selection with any "order by" clause you might need and return the
data ready for view or process?
http://wiki.lazarus.freepascal.org/ZMSQL

----------------------------------------------------------------------
miab3 · 2016-01-09 13:57

ZEOS does SortedFields and Filter locally(on client side) on full
dataset.

Michal

----------------------------------------------------------------------
Hansvb · 2016-01-09 18:30

Now i know. I am looking at the wrong place. The filter is fast.
Create a list with unique items is slow when there a lot of records. I
am doing something wrong when i create a list for the filter. I have
to look at that.

----------------------------------------------------------------------
teos · 2016-01-09 22:19

First Hans, your way to go is with a SQL statement which you run on
the server and which only loads values that are unique and within the
scope of what you want to see.

Fetching all data and filter it locally is just against the whole idea
of the SQL query language: the goal of that is minimize data over
internet.

If your tables have BLOB fields, avoid using them, better retrieve the
blob value when needed. Blob fields are slowing down queries horribly.
So are master-detail relations: if you need master-detail data, use
joins for that.

The sql "group by" statement should return only unique values.

ZQuery component is not readonly so instead of a filter: you can test
your SQL on Oracle and fine-tune your query. That should speed up
things a lot.