Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-07-31
13
Medium Priority
?
328 Views
Last Modified: 2010-04-05
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?


0
Comment
Question by:selas
13 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 11683882
> 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
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11684731
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
 

Expert Comment

by:corsita
ID: 11691207
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Expert Comment

by:DarkCore_
ID: 11691453
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
 

Author Comment

by:selas
ID: 11691803
I'm using:
Ado connection
Datasource
Adoquery
Adotable
0
 
LVL 2

Expert Comment

by:DarkCore_
ID: 11691823
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
 
LVL 18

Expert Comment

by:Johnjces
ID: 11696489
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
 

Author Comment

by:selas
ID: 11701401
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
 
LVL 2

Expert Comment

by:DarkCore_
ID: 11701522
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
 

Author Comment

by:selas
ID: 11705580
I can't use AdoTable...
0
 
LVL 2

Expert Comment

by:DarkCore_
ID: 11710510
how? can u explain better?
0
 

Author Comment

by:selas
ID: 11713653
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
 
LVL 2

Accepted Solution

by:
DarkCore_ earned 1500 total points
ID: 11713716
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question