Solved

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

Posted on 2004-07-31
13
298 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:leannonn
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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now