Solved

Firebird, Delphi - Need help in an efficient way of updating a table

Posted on 2009-04-02
13
589 Views
Last Modified: 2013-12-09
Hi all,

Well, I am using 2 tables (Players+teams) and at the moment I am using a rather slow process using Listboxes etc to get the result I want. But I think I can manage to achieve it using only FB SQL commands which I would think improves the speed a lot.

What I want to achieve:

I want to select the first 6 names(field is called NAME) where Club = clubchosen.caption and Injury = 0 and Pos = D. No problems doing this using:

SELECT  First 6 * FROM SPELARE where klubb = '''+clubchosencaption+'''' and Pos = 'D' and Injury = '0' order by Def DESC;

Here's basically what I need help with:

From those 6 records chosen, the first 2 needs to have its field "Lineup" updated to '1', the following two results(3+4) should have the field "Lineup" set to 2 and finally the last 2 records(5+6) should have the field "Lineup" set to 3.

At the same time, the Players table holds 2 fields called Def and Off (basically the strength of the player)

From the query mentioned above and the 6 records that shows up, the Def and Off(holds a number from 1-20) should be added and the final result should be placed in LineupDef.Caption(for Def field) and LineupOff.Caption(for Off field).

What would be the most efficient way to achieve this, especially the update method where it also needs to do some counting? (The db will only be used locally.. used Paradox before but it was way too slow when I reached 10k records).

Hope you can understand me and bare with my poor english.
0
Comment
Question by:MerlaP83
  • 6
  • 5
  • 2
13 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24048017
the fastest way is using calculated fields

the thing i don't see is what you do with the select ...
are you using this only in your program in memory or writing this back into a third table ?
0
 

Author Comment

by:MerlaP83
ID: 24048051
The thing with the Select function is that I want to edit the 6 records(players) with the highest Def skill set. The other records are not to be updated(there are about 30 records per team, only 6 are to be edited).

I am only using this in my program in memory - need to do the calculation to get the team skill, so I need to do a fast calculation.

0
 

Author Comment

by:MerlaP83
ID: 24062402
Forgot to ask what it means with "calculated fields" ? How to use and take advantage of this?
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24069662
a calculated field is a field you add to the dataset
put a query on a form
right click and click edit fields
then you can add fields
you set the type to calculated

then assign the oncalculate event handler
this gets called for each record on loading the data, and you can set the value of your fields to what you want

procedure TForm1.Query1OnCalculate(Dataset: TDataset);
begin
  DataSet.FieldByName('TOTAL').AsFloat := Dataset.FieldByName('QUANTITY').AsFloat * DataSet.FieldByName('PRICE').AsFloat;
end;
0
 

Author Comment

by:MerlaP83
ID: 24105914
Sounds good, but didn't get it to work with Zeos? Any good tutorial link for calc?

The main problem I have though is the following:

I have a query that returns, say 5 results and I would like the Off field (number 1-20) from those 5 results to be added to count_off.caption.


with Spelare do

      begin

      Close;

      SQL.Clear;

      SQL.text := 'select first 5 * from spelare where klubb = ''Tomteland'' and Pos = ''F'' order by Off DESC';

      Open;

end;
 

//Adding this, which is probably not that efficient, will only give me the number of the first result's Off field (not the total amount from the returned results).
 

count_off.caption.caption := inttostr(strtoint( count_off.caption) + strtoint(Spelare['Off']));
 

//

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24114757
"//Adding this, which is probably not that efficient, will only give me the number of the first result's Off field (not the total amount from the returned results)."
Okay, well then why don't you cycle/iterate through your dataset and add up the off field that way?
var
  i : integer;
i := 0;
MyDataset.First;
While MyDataset.eof = false do
begin
  i := i + MyDataSet.Fieldbyname('Off').asInteger;
  MyDataset.Next;
end;
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:MerlaP83
ID: 24117556
Thanks, that did the trick; however, my final question before I close and accept it as a solution is how I, at the same time, can update the results?
with Spelare do

      begin

      Close;

      SQL.Clear;

      SQL.text := 'select first 3 skip 3 * from spelare where klubb = ''+nuvklubb.caption+''' and Pos = ''B'' order by Def DESC';

      Open;

end;

i := 0;

Spelare.First;

While Spelare.eof = false do

begin

 i := Spelare.Fieldbyname('Def').asInteger;

 Spelare.sql.text := 'update spelare set platsar = ''2''';

 Spelare.Next;

end;

Open in new window

0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 500 total points
ID: 24117979
"From the query mentioned above and the 6 records that shows up, the Def and Off(holds a number from 1-20) should be added and the final result should be placed in LineupDef.Caption(for Def field) and LineupOff.Caption(for Off field)."
Okay so you first said you just want to put the result of the count in "Lineupdef" (what type of object is LineupDef anyways? Is it a DBEdit? Is it even data-aware?)
You could just change your above code to this:
 

i := 0;

Spelare.First;

While Spelare.eof = false do

begin

 i := Spelare.Fieldbyname('Def').asInteger;

// Spelare.sql.text := 'update spelare set platsar = ''2''';

 Spelare.Next;

end;

LineupDef.caption := inttostr(i);
 

Next you said

"From those 6 records chosen, the first 2 needs to have its field "Lineup" updated to '1', the following two results(3+4) should have the field "Lineup" set to 2 and finally the last 2 records(5+6) should have the field "Lineup" set to 3."

Well then you would change your above code to something like this:
 
 

  i := 0;

  Spelare.First;

  While Spelare.eof = false do

  begin

   i := Spelare.Fieldbyname('Def').asInteger;

   if (Spelare.RecNo => 0) and (Spelare.RecNo <= 1) then

   begin

     Spelare.Edit;

     Spelare.Fieldbyname('LineUp').asinteger := '1';

     Spelare.Post;

   end;

   if (Spelare.RecNo => 2) and (Spelare.RecNo <= 3) then

   begin

     Spelare.Edit;

     Spelare.Fieldbyname('LineUp').asinteger := '2';

     Spelare.Post;

   end;

   if (Spelare.RecNo => 4) and (Spelare.RecNo <= 5) then

   begin

     Spelare.Edit;

     Spelare.Fieldbyname('LineUp').asinteger := '3';

     Spelare.Post;

   end;

   Spelare.sql.text := 'update spelare set platsar = ''2''';

   Spelare.Next;

  end;
 
 

===

Warning: Actually making changes to the dataset while you are iterating/cycling through it might be a bad idea. So you can update the table using a query instead. Usually you will need the IDs of the fields to be updated. Does your resultset have an ID field you can use? Here is an example:

REPLACE THESE:

     Spelare.Edit;

     Spelare.Fieldbyname('LineUp').asinteger := '3';

     Spelare.Post;

WITH THESE:

     OtherQuery.SQL.Clear;

     OtherQuery.SQL.Text := 'SET Spelare.LineUp = 3 WHERE Spelare.ID = ' + Spelare.Fieldbyname('ID').asstring);

     OtherQuery.ExecSQL;

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24118000
Hey I see an error in your code that I have repeated above. You say:
   i := Spelare.Fieldbyname('Def').asInteger
that should be:
  i := i +  Spelare.Fieldbyname('Def').asInteger;
otherwise i will not count.
0
 

Author Comment

by:MerlaP83
ID: 24118280
Thanks for all your information and tips, very useful.

Sorry if I've said different things, its because I haven't found the best solution for the query yet. Unfortunately, I do not have any ID for the table.

LineUp has nothing to do with the DB, its just a label.

6 records will be chosen.

First 2 should have its 'Def' attributes added to Line1Def, and their 'Platsar' should be set to '1'.
#3 and #4 should have its 'Def' attribute added to Line2Def, and their 'Platsar' should be set to '2'.
#5 and #6 should have its 'Def' attribute added to Line3Def and their 'Platsar' should be set to '3'.

It needs to be a fast procedure since it will be used a lot in updating lineups for the teams.
//By adding this I will pull the first 2 results of the 6 (seemed like an easy method, but requires a lot of coding (copy/change/paste) and will probably be a little bit slow.
 

with Spelare

do

      begin

      Close;

      SQL.Clear;

      SQL.text := 'select first 2 * from spelare where klubb = ''+nuvklubb.caption+''' and Pos = ''B'' order by Def DESC';

      Open;

end;

i := 0;

Spelare.First;

While Spelare.eof = false do

begin

 i := i + Spelare.Fieldbyname('Def').asInteger;

 Spelare.Next;

end;

Line1Def.Caption := inttostr(i);
 

//Doing the following will give the error "Expression expected but '>' found. Looked like a good solution otherwise though since it will be a lot less coding to go through. But do I have to go with Edit/Post? It's a bit slower, isn't it?
 

  While Spelare.eof = false do

  begin

   if (Spelare.RecNo => 0) and (Spelare.RecNo <= 1) then

   begin

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24118687
sorry change => to >=
i forget that it likes to say "..is greater than or equal to" but doesn't like "is equal to or greater than".
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24118698
I'm also slightly confused about which are your datasets and which are your table names. I suspect you have a TZTable or TZQuery called "Spealer" and also a table in your database called "Spealer". It's probably my fault, I should read through all of this carefully to understand 100%
0
 

Author Comment

by:MerlaP83
ID: 24119655
I'm sorry, I'm not too easy to understand sometimes with my somewhat poor english :)

You were correct in the fact that I have a TZQuery called "Spelare" and a table in my db called "Spelare".

Finally I got everything settled and working perfectly (A LOT faster than it used to be + a lot less code). Thanks for your patience and excellent help, really appreciated as always rfwoolf.
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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

708 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