Speed up Database Operations

[Zvoni]

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.

Can you post your current structure? and some sample entries
We might see something

[Zvoni]

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.  

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

example for "excluded"

Code: SQL  [Select][+][-]

1. CREATE TABLE Test (MyName TEXT UNIQUE, MyValue INTEGER);
2.  
3. INSERT INTO Test(MyName, MyValue) VALUES('Zvoni', 42);
4.  
5. INSERT INTO Test(MyName, MyValue) VALUES('Zvoni', 84)
6. 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][+][-]

1. CREATE TABLE "tbl_test4" (
2.         "MyName"        TEXT UNIQUE,
3.         "MyValue"       INTEGER
4. )
5.  

Code: SQL  [Select][+][-]

1. CREATE TRIGGER trg_tbl_test4 BEFORE INSERT ON tbl_test4
2. FOR EACH ROW
3.         WHEN
4.                 (SELECT 1 FROM tbl_test4 WHERE MyName=NEW.MyName)>0
5.                 BEGIN
6.                         UPDATE tbl_test4 SET MyValue=NEW.MyValue WHERE MyName=NEW.MyName;
7.                         SELECT RAISE(IGNORE);
8.                 END

Now execute some Inserts incl. duplicates for MyName

[Weitentaaal]

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]

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

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"

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.

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

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.