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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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
DarkCore_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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

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.