Speed up Database Operations

[Weitentaaal]

Hello,

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

Code: Pascal  [Select][+][-]

1.    RsDB.Clear;
2.    RsDB.UsePrimaryKeyAsKey:= False;
3.    RsDB.DataBase:= fDatabase;
4.    RsDB.SQL.Text:= 'SELECT * FROM Persons WHERE Name = "'+fName+'" AND Age>=30 AND City = "'+fCity.toString+'"';
5.    RsDB.Open;
6.    RsDB.Edit;
7.    RsDB.FieldByName('X').AsInteger:= fX;
8.    RsDB.FieldByName('Y').AsInteger:= fY;
9.    RsDB.FieldByName('Z').AsInteger:= fZ;
10.    RsDB.FieldByName('Typ').AsInteger:= ord(fTyp);
11.    //.....
12.    
13.    RsDB.Post;
14.    RsDB.ApplyUpdates;
15.    Trans.CommitRetaining;
16.  

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]

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]

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]

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][+][-]

1. Query.SQL.Text := 'SELECT * FROM cities WHERE country_code = :CountryCode AND population > :Population';
2.  

and then insert/update my rows like this:

Code: Pascal  [Select][+][-]

1. Query.Params.ParamByName('CountryCode').AsString := 'USA';
2. Query.Params.ParamByName('Population').AsInteger := 1000000;
3. Query.ExecSQL;
4.  

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][+][-]

1. CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
2. INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
3.   ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
4.  

[Zvoni]

I 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

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][+][-]

1. CREATE TABLE phonebook(name TEXT PRIMARY KEY, phonenumber TEXT);
2. INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212')
3.   ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber;
4.  

Yes, because usually you only write them once.

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

[Weitentaaal]

ok thank you. I tried to avoid long queries.

[egsuh]

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]

SQLite 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]

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]

SQLite 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]

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][+][-]

1. procedure TMainView.DoUpsert(RowID: integer);
2. begin
3.    StartTimeCheck;
4.    if not fTrans.Active then fTrans.StartTransaction;
5.    try
6.       fQuery.Close;
7.       //fQuery.SQL.Text:= 'Insert or Ignore Into Geraet(ZNr, Position, Zeichnung) VALUES(:ZNr, :Position, :Zeichnung);'
8.          //+'Update Geraet set ZNr=:ZNr, Position=:Position, Zeichnung=:Zeichnung where changes()=0 and ZNR = :ZNr;';
9.       //fQuery.SQL.Text:= 'Insert or Ignore Into Geraet(ZNr, Position, Anlage) VALUES(:ZNr, :Position, :Zeichnung)'
10.          //+' Select :ZNr, :Position, :Zeichnung WHERE NOT EXISTS(SELECT changes() as Change FROM Geraet WHERE change <> 0) ';
11.  
12.       fQuery.SQL.Text:= 'Update Geraet SET Anlage=:Anlage, Zeichnung=:Zeichnung WHERE ZNr=:ZNr; '
13.          +'INSERT INTO Geraet(ZNr, Anlage, Zeichnung) SELECT :ZNr as ZNr, :Anlage as Anlage, :Zeichnung as Zeichnung '
14.          +'FROM Geraet WHERE NOT EXISTS(SELECT * FROM Geraet WHERE ZNr = :ZNr) LIMIT 1;';
15.  
16.  
17.       fQuery.Prepare;
18.       fQuery.ParamByName('ZNr').AsInteger:= RowID;
19.       fQuery.ParamByName('Anlage').AsString:= 'TEST';
20.       fQuery.ParamByName('Zeichnung').AsInteger:= 1;
21.       fQuery.ExecSQL;
22.       fQuery.Close;
23.    finally fTrans.Commit; end;
24.    EndTimeCheck('UPSERT');
25. end;
26.  

[alpine]

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]

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][+][-]

1. INSERT INTO Geraet(ZNr, Anlage, Zeichnung) VALUES(:ZNr,:Anlage,:Zeichnung)
2. ON CONFLICT(ZNr) DO UPDATE SET Anlage=excluded.Anlage, Zeichnung=excluded.Zeichnung;

[Weitentaaal]

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][+][-]

1. DO UPDATE SET Anlage=excluded.Anlage, Zeichnung=excluded.Zeichnung;
2.  

what does the excluded.XYZ mean ? could i also use

Code: Pascal  [Select][+][-]

1. DO UPDATE SET Anlage=:Anlage, Zeichnung=:Zeichnung;
2.  

[Zvoni]

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.

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