SQL faster with this code?

Magno · 2005-07-22

Hi,

I heared from someone that sql modify could work faster when you do this:

rec.RESET;
rec.SETFILTER(...);
rec.find('-');
rec.field := value;
rec.MODIFY();

instead of :

rec.RESET;
rec.SETFILTER(...);
rec.find('-');
rec2.COPY(rec);
rec2.field := value;
rec2.MODIFY();

is this true?
If it is, can someone give me some example to test this?

DenSter · 2005-07-22

Well I wouldn't think so, because you'd have to get values in your Rec variable first, which is more processing. Also, you're doing an additional operation to your first example, so by definition that would take more time. I don't think this will save any time for you.

Even if it did, it's just one record you are modifying, that's not where you want to focus your attention when fighting performance issues.

bostjanl · 2005-07-22

Freightliner wrote:
Hi,

I heared from someone that sql modify could work faster when you do this:
rec.RESET;
rec.SETFILTER(...);
rec.find('-');
rec.field := value;
rec.MODIFY();
instead of :
rec.RESET;
rec.SETFILTER(...);
rec.find('-');
rec2.COPY(rec);
rec2.field := value;
rec2.MODIFY();
is this true?
If it is, can someone give me some example to test this?

What's wrong with first example? :?

Why even bother to use second one ? :roll:

Unless there is some catch?

regards

Bostjan

Magno · 2005-07-22

that's indeed the same as i thought, but as i think of it, would it be different if the value you change is the one in the filter?

bostjanl · 2005-07-22

Freightliner wrote:

that's indeed the same as i thought, but as i think of it, would it be different if the value you change is the one in the filter?

Yes it would be, but even more different if field is in the key that is used.

The second one is ](*,)

Bostjan

Magno · 2005-07-22

in what sense would it be different then?
would the first be faster or the second one?

bostjanl · 2005-07-22

Freightliner wrote:

in what sense would it be different then?
would the first be faster or the second one?

It's not speed. In first version if you change value that is in the key and you are in the loop you can get "suprise" when NEXT-ing to the next record. ](*,)

Bostjan

Magno · 2005-07-22

yes, but that is the same as in native.

So there would be no difference only for SQL?

kriki · 2005-07-22

No, both statements are translated in an UPDATE-statements in SQL.

But like bostjanl said, it is needed to to avoid surprises in a loop (both Navision as SQL-DB). (Surprises : not all records are processed OR endless loop)

SQL faster with this code?

Comments

Categories