Forum > Databases

In Memory Database

(1/3) > >>

Weitentaaal:
Hello,

i have a Large Database (SQLITE) and my Application is getting slowed down by it. I Have Indexed the Tables and Selected a Primary Key but its still kind of slow. I was thinking about Copying the Database into RAM and after finishing all tasks, copying it back. How would i achieve this ?

rvk:

--- Quote from: Weitentaaal on May 15, 2024, 10:02:47 am ---I Have Indexed the Tables and Selected a Primary Key but its still kind of slow.

--- End quote ---
Are you sure the indexes are really used for the SQL statement you pass?

You can check this by examining the PLAN (in combination with the actual SQL).
(Those should be visible with a normal DB Manager when executing the SQL)

https://www.sqlite.org/eqp.html

Weitentaaal:
i got the following as Result: "SCAN TABLE Equip USING INDEX sqlite_autoindex_Equip_1"

The query i used was: "EXPLAIN QUERY PLAN SELECT * FROM Equip ORDER by TeilIndex"

Teilindex is my Primary Key.

what does that mean ? i then continue using TeilIndex in the Data Table which contains all items with its Values and Descriptions. The TeilIndex is the "link" to the Data Table.

rvk:
It means for the SQL "SELECT * FROM Equip ORDER by TeilIndex" the index sqlite_autoindex_Equip_1 is used.
Which is good. That should be fast.

If you say it is slow... then something else is slow.
For example... if you use a different SQL (or at a WHERE to it).
Or... if you do a SQL.Last (which results in fetching ALL records at one).

So it also depends on what you do with that dataset.
If you retrieve it and NEED to traverse through ALL the records... that's always going to be slow.

If you do something with it which results in retrieving too much data (which isn't used directly), then you waste a lot of bandwidth and cpu which isn't needed.

And what do you call "slow" ?
What components are slow getting the data?
(are you using TSQLQuery or another component?)

Weitentaaal:
I do use TSQLQUERY. i just meassured and it took 8 Seconds to fully load all my data into my Application. i use only Select Statements like: "Select THISANDTHAT FROM TABLE Where INDEX = XY"

after that i do assign my Values like this: ValueXY:= SQLQUERY.Fieldbyname('XY').AsWhatever;
i have a lot objects that get the Data like this.

i create the TSQLQUery Object like this:


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---initialization   QUERY:= TSQLQuery.Create(nil);finalization   QUERY.Free; 
i know it's not the safest way of doing it like this.

it's just that i have some linked Lists which contain Objects and for each object in each linked list i do load the Data like i showed previous.
I will try to optimize the query's even further. is there anything else i could do ? maybe join the data would be faster ?

Navigation

[0] Message Index

[#] Next page

Go to full version