My program uses mysql database on server, it takes about 2 min to run this program

My program uses mysql database on server, it takes about 2 min to  run this program
Connection speed is 2mb/s
Database: 5 char fields and one blob field
On create i select only char fields
Then i run taskbar i see that my program retrieving data, about 20mb. After retrieving it works very fast
how to make my program run faster?
Why it retrieving all database?


selasAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DarkCore_Connect With a Mentor Commented:
Just insert a TADOQuery in your project and assign the connection ... in this example i've named it "qryAux"

//this replaces the LOCATE. Locate requires all records to be loaded, instead, we make a search on the server
qryAux.Close;
qryAux.SQL.Text := 'SELECT * FROM [table] WHERE ID = :Id';
qryAux.Params.ParamByName('ID').Value := LabeledEdit1.Text;
qryAux.Open;

//if not found, the query returns an Empty Dataset
if not qryAux.IsEmpty then

.....
//I don't know what you do after, maybe an Edit or an Insert? so
qryAux.Close;

//in case is an insert or an Append
qryAux.SQL.Text := 'INSERT INTO [table] ( zaidzia, inesta, atiduota, balansas ) VALUES ( :zaidzia, :inesta, :atiduota, :balansas )';

//in case is an edit
qryAux.SQL.Text := 'UPDATE [table] SET zaidzia = :zaidzia, inesta = :inesta, atiduota = :atiduota, balansas = :balansas WHERE ID = :ID';

//and then assign
qryAux.params.ParamByName('zaidzia').Value := Adotable1.FieldByname('zaidzia').Value;
qryAux.params.ParamByname('inesta').Value   := StrToInt( LabeledEdit2.Text );
qryAux.params.ParamByname('atiduota').Value := STRtoINT(LabeledEdit3.text);
qryAux.params.ParamByname('balansas').Value := STRtoINT(LabeledEdit3.text) - STRtoINT(LabeledEdit2.text);

....

I'm asuming here you use the query without RequestLive, but you can use requestLive without problem if you want

Returning when "if not qryAux.isEmpty then ..." you can use qryAux.Edit; to modify the recordset, without closing the ADOquery ( qryAux.Close ) and changing the SQL Statment ( SQL.Text ).

Edu
0
 
Aleksandar BradarićSoftware DeveloperCommented:
> how to make my program run faster?
> Why it retrieving all database?

Which component are you using to connect to MySQL? I supose you are using a Table which retireves all of the data. Using a Query should be quicker... If you can post the DB structure and your query I might be more specific... :)
0
 
Ivanov_GCommented:
yep, I agree with leannonn. Instead of using Table component use Query and put SQL property :

SELECT CHAR_FIELD FROM TABLE_NAME

thus it will retrieve only CHAR_FIELD.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
corsitaCommented:
it takes 2 minutos because is downloading 20 MB... thats clear

change the component to a Query. thats a fact! but not the problem.

then limit the result!! (because the problem is that you are retreiving all the records)
What records do u need? all?? if u need all records it will be a problem.
maybe u can add a "search" in your app to retrieve only some records... this is what i do generally
why to retrieve a millon of records when u only need 10.

explain a bit more of your scenario.

cheers!
0
 
DarkCore_Commented:
As said, only select fields you really need, and if you're using a Table, try to use a Query.

In other way, if you use a Lookup Field, it will load the entire table too. I had the same problem with lookup fields. You can use a calculated field instead ( then you can't use any DBLookup , but you can have a "select" button, to show a "select" form )

And try to control "active" querys at desing-time.

Edu
0
 
selasAuthor Commented:
I'm using:
Ado connection
Datasource
Adoquery
Adotable
0
 
DarkCore_Commented:
try to drop the AdoTable down and select what you _really_ want with an ADOQuery.

And .. in the query is there any field marqued as a lookup field? ( double click over the ADOQuery or ADOTable and you'll see the list of fields ) and try to find one field marked with Fieldkind = fkLookup. Is this is the case, change it to a calculated field.

Edu
0
 
JohnjcesCommented:
You might try in your ADOConnection and in your ADOQuery or ADOTable, to set the cursor location to server.

This keeps all data on the server and prevents it from coming down locally. I do not know how well this will work with MySQL but I know it works very well on MS SQL server.

One drawback is that bookmarking may be problematic and some reports need a local cursor location fior tables and queries. You can still have an ADOConnection.CursorLocation := Server and some tables/queries set to local, but you'll still be retrieving data from the database server.
0
 
selasAuthor Commented:
I dont know can i drop AdoTable, because i don't know how to replase this code:

if not AdoTable2.Locate('ID', LabeledEdit1.Text, []) then
begin
  AdoTable1.FieldByName('lankomumas').Value := AdoTable1.FieldByName('lankomumas').Value + 1;
end;

AdoTable2.Insert;
AdoTable2.FieldByName('id').Value := AdoTable1.FieldByName('id').Value;
AdoTable2.FieldByName('slapyvardis').Value := AdoTable1.FieldByName('slapyvardis').Value;
AdoTable2.FieldByName('lankomumas').Value := AdoTable1.FieldByName('lankomumas').Value;
AdoTable2.FieldByName('zaidzia').Value := AdoTable1.FieldByName('zaidzia').Value;
AdoTable2.FieldByName('inesta').Value := STRtoINT(LabeledEdit2.text);
AdoTable2.FieldByName('atiduota').Value := STRtoINT(LabeledEdit3.text);
AdoTable2.FieldByName('balansas').Value := STRtoINT(LabeledEdit3.text) - STRtoINT(LabeledEdit2.text);
AdoTable2.FieldByName('laikas').Value := DateTimeToStr(Now);
AdoTable2.FieldByName('ivede').Value := pc;
end;
0
 
DarkCore_Commented:
try this with a new Query i've named qryAux ..... I don't know what you have in AdoTable1 ... but if you've done a search before , you can use too a select.

qryAux.SQL.Text := Format( 'SELECT id FROM table WHERE ID = %s',[ QuotedStr( labeledEdit1.Text ) ] );
qryAux.Open;

if qryAux.isEmpty then Begin
    AdoTable1.FieldByName('lankomumas').Value := AdoTable1.FieldByName('lankomumas').Value + 1;
End;

//Insert the new value
qryAux.Close;
qryAux.SQL.Text := 'INSERT INTO Table ( id, slapyvardis, lankomumas, zaidzia, inesta, atiduota, balansas, laikas, ivede ) VALUES ( :id, :slapyvardis, :lankomumas, :zaidzia, :inesta, :atiduota, :balansas, :laikas, :ivede)';

qryAux.Params.ParamByName('id').Value := Adotable1.FieldByName('id').value;
qryAux.Params.ParamByName('slapyvardis').Value := AdoTable1.FieldByName('slapyvardis').Value;

... and so on ...

qryAux.ExecSQL;
0
 
selasAuthor Commented:
I can't use AdoTable...
0
 
DarkCore_Commented:
how? can u explain better?
0
 
selasAuthor Commented:
if i use AdoTable then my program rus very slow on remote computers:

My program uses mysql database on server, it takes about 2 min to  run this program
Connection speed is 2mb/s
Database: 5 char fields and one blob field
On create i select only char fields
Then i run taskbar i see that my program retrieving data, about 20mb. After retrieving it works very fast
how to make my program run faster?
Why it retrieving all database?

How to change this to query?:

if not AdoTable2.Locate('ID', LabeledEdit1.Text, []) then

AdoTable2.FieldByName('zaidzia').Value := AdoTable1.FieldByName('zaidzia').Value;
AdoTable2.FieldByName('inesta').Value := STRtoINT(LabeledEdit2.text);
AdoTable2.FieldByName('atiduota').Value := STRtoINT(LabeledEdit3.text);
AdoTable2.FieldByName('balansas').Value := STRtoINT(LabeledEdit3.text) - STRtoINT(LabeledEdit2.text);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.