Forum > Databases
Speed up Database Operations
Weitentaaal:
Hello,
would someone tell me how i should refactor this code for a optional Saving/Loading approach:
--- 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";}};} --- 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:
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 [+][-]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";}};} ---Query.SQL.Text := 'SELECT * FROM cities WHERE country_code = :CountryCode AND population > :Population';
and then insert/update my rows 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";}};} ---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 [+][-]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";}};} ---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:
--- Quote from: Weitentaaal on November 27, 2024, 12:20:12 pm ---I am using SQLite and the libraries provided by Lazarus (sqlite3conn, sqldb, db).
i still need to check if the record does exists right ?
--- End quote ---
No, if you have/use correct constraints it's not necessary
--- Quote ---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 [+][-]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";}};} ---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;
--- End quote ---
Yes, because usually you only write them once.
My last "monster" is some 200 lines long in my Query-Editor
Navigation
[0] Message Index
[#] Next page