Speed up Database Operations
lazarus / 16-Databases

----------------------------------------------------------------------
Weitentaaal · 2024-11-27 10:26

Hello,

would someone tell me how i should refactor this code for a optional
Saving/Loading approach:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 RsDB.Clear;   RsDB.UsePrimaryKeyAsKey:= False;   RsDB.DataBase:=
fDatabase;   RsDB.SQL.Text:= 'SELECT * FROM Persons WHERE Name =
"'+fName+'" AND Age>=30 AND City = "'+fCity.toString+'"'; 
 RsDB.Open;   RsDB.Edit;   RsDB.FieldByName('X').AsInteger:= fX; 
 RsDB.FieldByName('Y').AsInteger:= fY; 
 RsDB.FieldByName('Z').AsInteger:= fZ; 
 RsDB.FieldByName('Typ').AsInteger:= ord(fTyp);   //.....     
 RsDB.Post;   RsDB.ApplyUpdates;   Trans.CommitRetaining; 
Persons Table is just an  example. i have a large Table with nearly 50
Columns and a lot of rows. Besides of Normalizing and Indexing of the
Database. is there anything else i should optimize / change.

Thanks in advance

----------------------------------------------------------------------
af0815 · 2024-11-27 10:34

Depending of the server - use Parameters (deal not with
stringoperation in sql) and direct Insert/update with parameters and
where clause. Avoid the using of strings in the where will speed up
things.

50 Columns and serveral millions of rows is not shocking for me.

----------------------------------------------------------------------
marcov · 2024-11-27 11:00

Also, bulk operations/insertions are a special case. It is hard to say
with database information, but for MSSQL it was important to manage
your transactions. Don't do them too fine (every operation in a
transaction), nor too coarse (everything in one big transaction). Dose
it in batches of hundreds or so.

Store the indexes to your parameters and fields, and reuse them
instead of always using fieldbyname. Use a primary key, most databases
prefer that, put indexes on fields used a lot in selects.

----------------------------------------------------------------------
Weitentaaal · 2024-11-27 11:20

I am using SQLite and the libraries provided by Lazarus (sqlite3conn,
sqldb, db).

I will try to make a parameterized query. If i got this right then i
can just execute the query like this (following is just a simple
example):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.SQL.Text := 'SELECT * FROM cities WHERE country_code =
:CountryCode AND population > :Population'; 
and then insert/update my rows like this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Query.Params.ParamByName('CountryCode').AsString :=
'USA';Query.Params.ParamByName('Population').AsInteger :=
1000000;Query.ExecSQL; 
i still need to check if the record does exists right ?
need the query to do an "UPSERT" (insert if not exists else update).

i was checking on "ON CONFLICT" too, but that would give me very long
querys(because of a lot of columns). Is it normal to have very long
querys ? Example (again just simple example):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);INSERT INTO
phonebook(name,phonenumber) VALUES('Alice','704-555-1212')  ON
CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;

----------------------------------------------------------------------
Zvoni · 2024-11-27 13:12

Quote from: Weitentaaal on November 27, 2024, 12:20:12 pmI am using
SQLite and the libraries provided by Lazarus (sqlite3conn, sqldb, db).


i still need to check if the record does exists right ?No, if you
have/use correct constraints it's not necessary

Quoteneed the query to do an "UPSERT" (insert if not exists else
update).

i was checking on "ON CONFLICT" too, but that would give me very long
querys(because of a lot of columns). Is it normal to have very long
querys ? Example (again just simple example):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);INSERT INTO
phonebook(name,phonenumber) VALUES('Alice','704-555-1212')  ON
CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber; Yes,
because usually you only write them once.

My last "monster" is some 200 lines long in my Query-Editor

----------------------------------------------------------------------
Weitentaaal · 2024-11-27 13:36

ok thank you. I tried to avoid long queries.

----------------------------------------------------------------------
egsuh · 2024-11-28 07:07

SQLite does not support stored procedures.
I use Firebird and save frequently used queries as stored procedures,
especially when some complex operations are done within database.
Somewhere I've read that the difference between stored procedures and
normal SQLs is whether it has been compiled or not. So I think if you
have to do carry out various operations (like comparing, and inserting
or updating based on the result, modify related other tables, etc.)
stored procedures would add some speed.
With SQLite, this is not possible. But I wonder if the codes are moved
to triggers then there would be performance gains.

----------------------------------------------------------------------
Thaddy · 2024-11-28 11:35

Quote from: egsuh on November 28, 2024, 08:07:59 amSQLite does not
support stored procedures.
Well, not with that name, but it supports user defined functions which
is effectively the same in all but name when used properly. I use them
a lot.

----------------------------------------------------------------------
alpine · 2024-11-28 12:02

Specifically for SQLite great impact on the execution speed have the
PRAGMA synchronous=
Also it is critical to enclose batches into a transaction - it speeds
up the operations significantly.

----------------------------------------------------------------------
Zvoni · 2024-11-28 13:08

Quote from: Thaddy on November 28, 2024, 12:35:45 pmQuote from: egsuh
on November 28, 2024, 08:07:59 amSQLite does not support stored
procedures.
Well, not with that name, but it supports user defined functions which
is effectively the same in all but name when used properly. I use them
a lot.

You sure? On first glance i'd disagree.
an SP is a "custom" Procedure stored within the Database, usually used
to execute complex and/or long-running SQL-Statements.
In the context of a Database it's its own "Object" within the Database
(like a Table, View, Trigger...), and is usually called with a Syntax
like
CALL MyStoredProcedure(SomeParam)

a UDF in the context of SQLite is a custom Function, which has to be
registered with SQLite, and is used WITHIN an SQL-Statement, but is
executed "outside" the sqlite-engine
The "classic" being the "regexp"-Function.
e.g.
SELECT MyFunc(SomeField) FROM SomeTable
in this example "MyFunc" must be registered from outside the sqlite-
engine (think passing a Function-pointer), and when the SQL-Parser of
sqlite encounters this token it calls the function, which is outside
its engine
(similiar to a callback)

----------------------------------------------------------------------
Weitentaaal · 2024-11-28 13:59

My Table does not have any Primary key or Unique Constraint. is there
a way to do a UPSERT without primary key ? i know my Table shoud have
atleast a primary Key but its a Project at my Workplace where i work
with other people and the Databse is a bit of a Mess. Should i add a
dummy PK wich is Autoincrement and expand my Objects with a for
example"DatabaseID" ?

i tried the following but could not get it to work:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMainView.DoUpsert(RowID: integer);begin   StartTimeCheck;   if not
fTrans.Active then fTrans.StartTransaction;   try      fQuery.Close; 
   //fQuery.SQL.Text:= 'Insert or Ignore Into Geraet(ZNr, Position,
Zeichnung) VALUES(:ZNr, :Position, :Zeichnung);'         //+'Update
Geraet set ZNr=:ZNr, Position=:Position, Zeichnung=:Zeichnung where
changes()=0 and ZNR = :ZNr;';      //fQuery.SQL.Text:= 'Insert or
Ignore Into Geraet(ZNr, Position, Anlage) VALUES(:ZNr, :Position,
:Zeichnung)'         //+' Select :ZNr, :Position, :Zeichnung WHERE NOT
EXISTS(SELECT changes() as Change FROM Geraet WHERE change <> 0) ';  
   fQuery.SQL.Text:= 'Update Geraet SET Anlage=:Anlage,
Zeichnung=:Zeichnung WHERE ZNr=:ZNr; '         +'INSERT INTO
Geraet(ZNr, Anlage, Zeichnung) SELECT :ZNr as ZNr, :Anlage as Anlage,
:Zeichnung as Zeichnung '         +'FROM Geraet WHERE NOT
EXISTS(SELECT * FROM Geraet WHERE ZNr = :ZNr) LIMIT 1;';       
fQuery.Prepare;      fQuery.ParamByName('ZNr').AsInteger:= RowID;    
fQuery.ParamByName('Anlage').AsString:= 'TEST';     
fQuery.ParamByName('Zeichnung').AsInteger:= 1;      fQuery.ExecSQL;  
  fQuery.Close;   finally fTrans.Commit; end; 
 EndTimeCheck('UPSERT');end;

----------------------------------------------------------------------
alpine · 2024-11-28 14:18

The PK is highly recommended, not only for referring by a foreign
keys, but also because the physical B-tree will be structured around
it. Otherwise the table will be known as "heap" and the row extraction
will be performed in a linear manner. As a last resort you can use an
autoinc for a surrogate PK if you really can't find/decide for a
natural PK. There is a big debate which one is better - natural or
surrogate PK - but that is another story.

----------------------------------------------------------------------
Zvoni · 2024-11-28 14:23

a UNIQUE column is an absolute minimum requirement for INSERT ... ON
CONFLICT to work properly

Next: IIRC, you cannot use multiple SQL-Statements separated by
semicolon within a single TSQLQuery-Object

Going by your sample above, my first try would be to declare "ZNr" as
UNIQUE, and then use INSERT... ON DUPLICATE

ZNr being at least UNIQUE (better UNIQUE and NOT NULL which equates to
PRIMARY KEY)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
Geraet(ZNr, Anlage, Zeichnung) VALUES(:ZNr,:Anlage,:Zeichnung) ON
CONFLICT(ZNr) DO UPDATE SET Anlage=excluded.Anlage,
Zeichnung=excluded.Zeichnung;

----------------------------------------------------------------------
Weitentaaal · 2024-11-28 14:27

it would be easy to decide the PK but the Table is not in normalized
form which makes it hard to use a PK. I did ask my colleagues to bring
the whole Database to 3NF (Third normal form), but without luck. So i
will add a PK witch autoincrement on, simply to have one.

EDIT:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DO UPDATE
SET Anlage=excluded.Anlage, Zeichnung=excluded.Zeichnung; 
what does the excluded.XYZ mean ? could i also use

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DO UPDATE
SET Anlage=:Anlage, Zeichnung=:Zeichnung;

----------------------------------------------------------------------
Zvoni · 2024-11-28 14:28

Quote from: alpine on November 28, 2024, 03:18:13 pmThe PK is highly
recommended, not only for referring by a foreign keys, but also
because the physical B-tree will be structured around it. Otherwise
the table will be known as "heap" and the row extraction will be
performed in a linear manner. As a last resort you can use an autoinc
for a surrogate PK if you really can't find/decide for a natural PK.
There is a big debate which one is better - natural or surrogate PK -
but that is another story.
Have to disagree regarding SQLite: Even if you don't declare an
explicit Primary-Key-Column, you'd still have the hidden "rowid"

If you do declare a Primary Key, you don't need the AUTOINCREMENT
modifier for a surrogate Integer-PK.

Though i do agree, that any table in any Database should always have a
Primary Key (surrogate or natural)

SideNote: Even if you don't have a Primary Key, BUT you have a UNIQUE
column, you can create an Index based on that unique Column, which
should speed up things in a remarkable way

----------------------------------------------------------------------
Zvoni · 2024-11-28 14:29

Quote from: Weitentaaal on November 28, 2024, 03:27:12 pmit would be
easy to decide the PK but the Table is not in normalized form which
makes it hard to use a PK. I did ask my colleagues to bring the whole
Database to 3NF (Third normal form), but without luck. So i will add a
PK witch autoincrement on, simply to have one.
Can you post your current structure? and some sample entries
We might see something

----------------------------------------------------------------------
Zvoni · 2024-11-28 14:38

Quote from: Weitentaaal on November 28, 2024, 03:27:12 pmEDIT:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DO UPDATE
SET Anlage=excluded.Anlage, Zeichnung=excluded.Zeichnung; 
what does the excluded.XYZ mean ? could i also use

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DO UPDATE
SET Anlage=:Anlage, Zeichnung=:Zeichnung; "excluded" --> it would be
the DISCARDED (in other words: the NEW) Value due to the conflict, and
in a ON CONFLICT you MUST use "excluded"

EDIT Just saw that you would use the Parameters. Yes, you could, but
just ... DON'T.... with the "excluded" you already have the Value, so
there is no need

btw: Going by your description by "not normalized" i'm smelling
something here (and no... it's not my unwashed socks).
If you can post your structure and some sample data, specifically
explaining what's a legal entry (INSERT works), and what's an entry
you'd like the UPDATE to execute.
I have an idea, but i'd need to see structure and data

----------------------------------------------------------------------
Zvoni · 2024-11-28 14:44

example for "excluded"
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE Test (MyName TEXT UNIQUE, MyValue INTEGER); INSERT INTO
Test(MyName, MyValue) VALUES('Zvoni', 42); INSERT INTO Test(MyName,
MyValue) VALUES('Zvoni', 84) ON CONFLICT(MyName) DO UPDATE SET
MyValue=excluded.MyValue;
In above sample, the first INSERT works, and MyValue gets "42"
the second INSERT runs into conflict with 'Zvoni' for MyName therefore
jumping into the ON CONFLICT,
and inside the ON CONFLICT the excluded.MyValue already holds the "84"

EDIT:
btw: Such an UPSERT can also be implemented with a Trigger in SQLite
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE "tbl_test4" (        "MyName"        TEXT UNIQUE,       
"MyValue"       INTEGER) 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TRIGGER trg_tbl_test4 BEFORE INSERT ON tbl_test4FOR EACH ROW        
WHEN                (SELECT 1 FROM tbl_test4 WHERE
MyName=NEW.MyName)>0                 BEGIN                       
UPDATE tbl_test4 SET MyValue=NEW.MyValue WHERE MyName=NEW.MyName;    
                  SELECT RAISE(IGNORE);                END
Now execute some Inserts incl. duplicates for MyName

----------------------------------------------------------------------
Weitentaaal · 2024-11-29 06:26

Thank you for all that info. This will help a lot understanding the
process and finding a good solution for my situation. Cant Post the
Database here. The Databse was Designed for our Produkt information
(it was designed like 20-30 years ago). But Someone decided to reuse
the Table (out of convenience) and put in Data that should have been
split up in a seperate table. there is a Column which decides the Type
of the Data. So the whole Table is a mixed Dataholder for our
software. I did ask to change it to a normalized Database but that was
not possible. I learned programm with databases by always having them
in the Third normal Form, thats why i have those problems now.

Long story short: the Database is a mess currently.

----------------------------------------------------------------------
Zvoni · 2024-11-29 07:20

Quote from: Weitentaaal on November 29, 2024, 07:26:24 amthere is a
Column which decides the Type of the Data.
Sounds like Entity Anti Pattern.
My Condolences....

And i didn't say to post your Database.
Just the Structure of that Table (Create-Statement), and some Sample-
Data (can be fantasy-Data)

----------------------------------------------------------------------
alpine · 2024-11-29 12:26

Quote from: Zvoni on November 28, 2024, 03:28:25 pmQuote from: alpine
on November 28, 2024, 03:18:13 pmThe PK is highly recommended, not
only for referring by a foreign keys, but also because the physical
B-tree will be structured around it. Otherwise the table will be known
as "heap" and the row extraction will be performed in a linear manner.
As a last resort you can use an autoinc for a surrogate PK if you
really can't find/decide for a natural PK. There is a big debate which
one is better - natural or surrogate PK - but that is another story.
Have to disagree regarding SQLite: Even if you don't declare an
explicit Primary-Key-Column, you'd still have the hidden "rowid"
You are right, (my bad - RTFM first!) it turns out the SQLite always
have that hidden "rowid" column unless WITHOUT ROWID was specified but
then you can't have AUTOINCs ... seems they're just another
incarnation for rowid.
But my advice was based on what I know about the true RDBMS-es and
mainly M$SQL. IMO perfectly valid.

Quote from: Zvoni on November 28, 2024, 03:28:25 pmIf you do declare a
Primary Key, you don't need the AUTOINCREMENT modifier for a surrogate
Integer-PK.

Though i do agree, that any table in any Database should always have a
Primary Key (surrogate or natural)

SideNote: Even if you don't have a Primary Key, BUT you have a UNIQUE
column, you can create an Index based on that unique Column, which
should speed up things in a remarkable way
Unique indexes will speed up things indeed. But one must bear in mind
that indexes are nothing more that internally managed tables  of
(column value, row PK value) and the actual extraction always follows
the B-tree distribution. Thus selecting suitable PK is always
important.