SQL performance question on INSERT

I would like to ask opinions about preferences or best practices in the following scenario.

lets say I have a record with primary key Document No::Code20,Version No::Integer. The table is small, but may have a lot of entries. Docuemnt No. is highly selective, but Version no, has a profile something like 90%=1, 5%=2, 4%=3, 1%>3 i.e nearly all the document numbers are unique, and its very rare for there to be more than 3 records with the same document number.

So when I insert a new record, I have basically 2 options, the first is to Lock the table, Filter on Document No, find the last version number in the sequence, increment by one, and insert. Option 2 is to set the version no. to “1”, and then just keep incrementing Version no till the record inserts correctly.Obviously the Navision way to do this is option 1, and if the Integer is highly selective (eg Item Ledge Entry Number) then it makes a lot of sense. But in the case where the first insert will work anyway,and its reare that you will increment more than 2 times anyway, would it be performance wise better to use option 2?

[Quote]Option 1 :

DocVersion.reset;

DocVersion.setrange(“Document No.”,DocNo);

If DocVersion.FINDLAST then

NextVerNo := Docversion.“Version No.” + 1

ELSE

NextVerNo := 1;

DocVersion.INIT;

// Do some stuff to populate DocVersion.

DocVersion.“Document No.” := DocNo;

DocVersion.“Version No.” := NextVerNo;

DocVersion.INSERT;

[/Quote]

[Quote]Option 2 :

DocVersion.INIT;

// Do some stuff to populate DocVersion.

DocVersion.“Document No.” := DocNo;

DocVersion.“Version No.” := 1;

WHILE NOT DocVersion.INSERT DO

DocVersion.“Version No.” += 1;

[/Quote]

Thanks.

Hi David!

Well, good question. Option 1: Here you have one SELECT TOP 1 … DESC statement caused by the FINDLAST and then one INSERT statement. As the “Primary Key Clustered” (I suppose?) is DocNo,VerNo this all should be performed quite fast (Clustered Index Seek).

With the second Option you would not encounter a problem in 90% of all cases; saving the one SELECT statement. In 10% of all cases you would generate more traffic than with option one: As C/SIDE always poulates all fields of the record, thus your sending up to three times more data.

So the question is: do this 10% really harm? I guess not … so maybe Option 2 is the One …

What about using ISEMPTY with option 2?

EXAMPLE:

DocVersion2.SETRANGE(“Document No.”, DocNo);

DocVersion.INIT;

// Do stuff

DocVersion.“Document No.” := DocNo;

NextVersionnNo := 0;

REPEAT

NextVersion := NextVersion + 1;

DocVersion.SETRANGE(“Version No.”, NextVersionNo)

UNTIL DocVersion2.ISEMPTY;

DocVersion.“Version No.” := NextDocVersionNo;

DocVersion.INSERT;

(On another note - do you know how I can turn off this double-spacing?)

But in this case you would fire multiple SELECT TOP 1 NULL instead of one SELECT TOP 1 * before the INSERT statement …

  • Option 1: 1 x SELECT TOP 1 * + 1 x INSERT in all cases

  • Option 2: 1 x INSERT in 90% of all cases + max 3 x INSERT in 10% of all cases

  • Option 3: 1 x SELECT TOP 1 NULL + 1 x INSERT in 90%; max 3 x SELECT TOP 1 NULL + 1 x INSERT in 10%

I still would vote for Option 2!

Should this be:

EXAMPLE 3:

DocVersion2.SETRANGE(“Document No.”, DocNo);

DocVersion.INIT;

// Do stuff

DocVersion.“Document No.” := DocNo;

NextVersionnNo := 0;

REPEAT

NextVersion := NextVersion + 1;

DocVersion2.SETRANGE(“Version No.”, NextVersionNo)// should these be filtered on Doc2 ?

UNTIL DocVersion2.ISEMPTY;

DocVersion.“Version No.” := NextDocVersionNo;

DocVersion.INSERT;

I see what you are doing here, is that because ISEMPTY is much faster?

If I need to do somethign complex, then I create the text in either Word or Front Page, then paste into this window. The problem is that {ENTER} like here

is translated into

(i.e. close the last paragraph and start a new one.) What you really need is a {
} like here
which then does not add a blank line,
but its just too much work to edit
in the HTML editor.
which is what I did to get these lines close together

How about DocVersion.“Version No.” := DocVersion2.COUNT + 1;

that a interesting idea. If this faster than if findlast then; I think all posting routines should be changed to COUNT.[I]

Yes, I agree with you. I never thought from that point o view.

Yes, it is. While a FINDLAST ist doing a SELECT TOP 1 *, thus querying one full record, the ISEMPTY with its SELECT TOP 1 NULL is not retrieving any fields/values.

This will trigger a SELECT COUNT(*). If the resultset - as in the current case - is just 2 or 3 records, it would do not much harm. Anyway, with this Option No. 4 we would have this:

1 x SELECT COUNT(*) + 1 x INSERT in all cases

Option 2 is still my favorite.

I really hope you mean this ironically. Basically a COUNT (= SELECT COUNT()) is “hell on earth” for SQL Server. If you would like to insert one “Item Ledger Entry”, the SELECT COUNT() would return the whole table, which could be millions of records!

A COUNTAPPROX would just do a SET SHOWPLAN_ALL ON and get the figure from the “Estimate_Rows” column, thus not doing a real query. That’s why it much faster than a COUNT, but not precise (“Estimate …”), that’s why we couldn’t use it this case.

First of all Navision doesn’t do a count(*). Second event if it did it would do count(1).

Third the actual statement is

SELECT SUM(rows), SUM(reserved) FROM “databasename”.[dbo].[sysindexes] WHERE id = OBJECT_ID(@P1) AND (indid < 2 OR indid = 255)

So it looking at sysindexes table and that table has around 10K records, and all it returns is actually two values back.

COUNT would probably work in this situation where there is a very small (2-3) number of records being aggregated. I don’t think I would use it to find the next ledger entry No.