asked on

Refresh/requery table after update through SQL

Hi,

I have following code for which I need to increase performance:

begin
// Alle relaties deselecteren
With DM.QAlgemeenGebruik do
begin
Close;
SQL.Clear;
SQL.Add(' UPDATE Relaties SET Relaties.GeselecteerdVoorAfspraak = False, Relaties.GefactureerdIndicator = False ');
SQL.Add(' WHERE Relaties.GeselecteerdVoorAfspraak = True OR Relaties.GefactureerdIndicator = True; ');
ExecSQL;
end;
If VarToStr(Event.ID) <> '' then
begin
// Alle relaties markeren die aan de afspraak zijn toegewezen.
With DM.QAlgemeenGebruik do
begin
Close;
SQL.Clear;
SQL.Add(' UPDATE (Relaties INNER JOIN AgendaRelaties ON Relaties.Relatienr = AgendaRelaties.Relatienr) SET Relaties.GeselecteerdVoorAfspraak = True ');
SQL.Add(' WHERE (((AgendaRelaties.[AgendaID]) = '+ QuotedStr(VarToStr(Event.GetCustomFieldValueByName('ID2Relaties'))) +')); ');
ExecSQL;
end;
// Merkeer alle relaties waarvoor voor de afspraak een factuur is aangemaakt.
With DM.QAlgemeenGebruik do
begin
Close;
SQL.Clear;
SQL.Add(' UPDATE (Relaties INNER JOIN AgendaRelaties ON Relaties.Relatienr = AgendaRelaties.Relatienr) SET Relaties.GefactureerdIndicator = True ');
SQL.Add(' WHERE (((AgendaRelaties.[AgendaID]) = '+ QuotedStr(VarToStr(Event.GetCustomFieldValueByName('ID2Relaties'))) +')) ');
SQL.Add(' AND (AgendaRelaties.Gefactureerd) = True; ');
ExecSQL;
end;
end;
DM.TRelatieLookup.DisableControls;
DM.TRelatieLookup.Requery;
DM.TRelatieLookup.EnableControls;
end;

The queries perform pretty good, about 0,015 seconds a piece.
But DM.TRelatieLookup.Requery; takes about 2,000 seconds.
Change this line to: DM.TRelatieLookup.Refresh; doesn't have much effect.

Is there any other way to run perform the updates by SQL and refresh the table while increasing performance?

Regards,
Stef

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kyle Foster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ivanov_G

Create a stored procedure inside database, something like this:

// MSSQL syntax
CREATE PROCEDURE sp_something @EventID INT, @AgendaID VARCHAR(50)
AS
UPDATE Relaties SET Relaties.GeselecteerdVoorAfspraak = False, Relaties.GefactureerdIndicator = False
WHERE Relaties.GeselecteerdVoorAfspraak = True OR Relaties.GefactureerdIndicator = True
IF @EventID <> ''
BEGIN
UPDATE (Relaties INNER JOIN AgendaRelaties ON Relaties.Relatienr = AgendaRelaties.Relatienr) SET Relaties.GeselecteerdVoorAfspraak = True
WHERE (((AgendaRelaties.[AgendaID]) = '+ QuotedStr(VarToStr(Event.GetCustomFieldValueByName('ID2Relaties'))) +'))
END
UPDATE (Relaties INNER JOIN AgendaRelaties ON Relaties.Relatienr = AgendaRelaties.Relatienr) SET Relaties.GefactureerdIndicator = True
WHERE (((AgendaRelaties.[AgendaID]) = @AgendaID))
AND (AgendaRelaties.Gefactureerd) = True
GO

then change your code to something like:

With DM.QAlgemeenGebruik do
begin
Close;
SQL.Clear;
SQL.Add(' CALL something ' + EventID + ', ' + AgendaID);
SQL.Add(' WHERE Relaties.GeselecteerdVoorAfspraak = True OR Relaties.GefactureerdIndicator = True; ');
ExecSQL;
end;

Stef Merlijn

ASKER

Thank you for your post Ivanov G. But as stated before, the SQL's aren't the bottleneck in performance. Refreshing the table is.

Stef Merlijn

ASKER

Thanks for you reply TomasHelgi.
As you can see I already use Requery instead of Refresh, because I don't need to save the current recordposition at that moment.

Ivanov_G

How much records do you have in your table. What is the type of the Cursor, ctClient maybe... ?

Stef Merlijn

ASKER

Number of record = 70
CursorType = ctStatic
CursorLocation = clUseClient
LockType = Optimistic
TableDirect = True
ADOConnection

Ivanov_G

What is the database? How much time does the query takes to execute in the DB's tool for queries?

How does DM.TRelatieLookup.Close; DM.TRelatieLookup.Open; execute?

Stef Merlijn

ASKER

I use a MS Access database.

> The queries perform pretty good, about 0,015 seconds a piece.
> But DM.TRelatieLookup.Requery; takes about 2,000 seconds.

DM.TRelatieLookup.Close; DM.TRelatieLookup.Open; takes about 2,000 seconds.

There a quite a few forms that use this table for lookup (lookupcomboboxes).

SOLUTION

Kyle Foster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stef Merlijn

ASKER

Making a copy does make a lot of difference.
Not it takes only 0,6 seconds to refresh.

Kyle Foster

All right,

try this

with DM.TRelatieLookup do begin
DisableControls;
try
Requery
finally
EnableControls;
end;
end;

Stef Merlijn

ASKER

Doesn't make any difference compared without EnableControls.

Kyle Foster

step through the code. does the requery here take the time or the EnableControls?

JeePeeTee

Have seen this also in an application I setup with MS Access. Do you have any tables that have setup datasource to any of the slow performing table in your App?

SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stef Merlijn

ASKER

Thank you all for your help.