Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-02
13
Medium Priority
?
602 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 38

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 38

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
 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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