Running database access as Windows service

Forum > Networking and Web Programming

<< < (3/3)

Thaddy:
You can not test CGI with just a single connection..... Isn't that obvious?'

What you should observe is that there is a CGI running for every single connection...
And they must be running at the same time: a CGI program exits when done.

egsuh:

--- Quote from: Thaddy on May 28, 2024, 12:56:54 pm ---You can not test CGI with just a single connection..... Isn't that obvious?'

What you should observe is that there is a CGI running for every single connection...
And they must be running at the same time: a CGI program exits when done.

--- End quote ---

Yes, I know how CGI works. I'm saying a "single request" takes long time to process.

egsuh:
I found the reason of my server's slowdown. It was in the procedure I wrote in Firebird.

I defined a structure of Name=value pairs. Let's say its field name is CF.

1=tiger
2=lion
3=leopard
4=elephant

et cetra.

This is stored as TStrings.Text in a BLOB filed, so that I can retrieve directly from my pascal app.

With pre-defined paris, I can save them in one field. But there are "other" responses, which are not pre-defined in the previous pair-list.
So I save them as one record per animal, with code, and caption.

code caption
100001 shark
100002 hyena
....

And I wrote a stored procedure function that retrieves all of them at once,

--- Code: SQL [+][-]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";}};} --- SELECT CF FROM FixedTable WHERE (cond) INTO :CF; FOR SELECT code, caption FROM OpenTable WHERE (cond) do BEGIN :CF = :CF || code || '=' || caption || ';'; -- Delimiter is ; in the pascal TStrings definition END; suspend; -- this returns CF as a out-field of this procedure

But this takes long time, as the number of records increase in the OpenTable. Just selecting the records from OpenTable does not take much time.
So, I need to change some procedures, or have to change the structure of FixedTable to one record for each animal.

The slowdown had nothing to do with "connecting to DB".

rvk:

--- Quote from: egsuh on June 27, 2024, 09:55:04 am ---
--- Code: SQL [+][-]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";}};} --- SELECT CF FROM FixedTable WHERE (cond) INTO :CF; FOR SELECT code, caption FROM OpenTable WHERE (cond) do BEGIN :CF = :CF || code || '=' || caption || ';'; -- Delimiter is ; in the pascal TStrings definition END; suspend; -- this returns CF as a out-field of this procedure
But this takes long time, as the number of records increase in the OpenTable. Just selecting the records from OpenTable does not take much time.
So, I need to change some procedures, or have to change the structure of FixedTable to one record for each animal.
--- End quote ---
You might want to begin investigating the PLAN for "cond" (the WHERE clause) and see which indexes are used.

BTW. You have the SUSPEND at the end of the PROCEDURE. Shouldn't you have it inside the FOR SELECT loop?
You want SUSPEND to pass the record back to the client for each record, so the client can iterate through the records.
Also see https://firebirdsql.org/refdocs/langrefupd21-psql-forselect.html

I also have the feeling this can be done much easier in just one SELECT and JOIN but can't say for sure without knowing the entire table structures.

--- Quote from: egsuh on June 27, 2024, 09:55:04 am ---The slowdown had nothing to do with "connecting to DB".

--- End quote ---
Good.

egsuh:
@rvk

Thank you for your interest. "where clause" has nothing to do with any problem.

What I was trying to do is to make a single string from multiple records. If written in Pascal, this would be the same as:

--- 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";}};} ---var cf: string; MyTStringsClass: {descendant of} TStringList; begin sqlquery.sql.text := 'select cf from fixedtable where (cond)'; sqlquery.Open; cf := sqlquery.fieldbyname('cf').AsString; sqlquery.close; sqlquery.sql.text := 'select code, caption from Open table where (cond)'; sqlquery.open; sqlquery.first; while not sqlquery.eof do begin cf += format(';%s=%s', [sqlquery.fieldbyname('code').asstring , sqlquery.fieldbyname('caption').asstring]); sqlquery.next; end; MyTStringsClass,DelimitedText := cf; // MyTStringsClass,Delimiter = ';'end;
It's because each part --- DB structure, and MyTStringsClass's usage is best suited in these formats. So I gave up some optimization, which was not "some" but "large".

I don't think this process (combining many strings into one string) would take much time if run in Lazarus, but it took long (more than 10 seconds with about 3-400 rows in Opentable) when run in Firebird SQL, which slowed down web server. So I changed my algorithm and have no problem now.

--- Quote ---BTW. You have the SUSPEND at the end of the PROCEDURE. Shouldn't you have it inside the FOR SELECT loop?
--- End quote ---

As I explained, the whole procedure returns only single row.

Navigation

[0] Message Index

[*] Previous page