Nav Sp1 5.0 Bulk Insert

NavStudent · 2008-04-07

Hello Navision has introduced with 5.0 SP1 Bulk insert.

By my definition Bulk insert is actualling using BULK INSERT statement.

What I see in query profiler is

insert record one
insert record two.
etc etc.

Is this actually bulk insert? or just that the records not insert until the commit and then inserted?

Also if they are calling insert, wouldn't it be faster to use union all statement instead.

read this post and performance difference

http://blog.sqlauthority.com/2007/06/08 ... union-all/

Thank you.

ara3n · 2008-04-07

As far as bulk insert statement concerned, it accepts a file or xml file.
As far as the way the inserts are called, I don't know maybe MS can answer this?

kriki · 2008-04-07

The bulk insert doesn't use union all.
It just buffers the inserts until a COMMIT is called or until you do some other action (SELECT,MODIFY,DELETE,...) on that table.

NavStudent · 2008-04-07

kriki wrote:

The bulk insert doesn't use union all.
It just buffers the inserts until a COMMIT is called or until you do some other action (SELECT,MODIFY,DELETE,...) on that table.

Thanks for confirming that it's not really bulk insert.
So since it's not a bulk insert and just buffering till the end, why aren't they calling insert once and doing a union on all the parameters. This will ave a lot better performance.

NavStudent · 2008-04-07

The first performance gain is a single insert statement which is reduction in network traffic. I'm just curios why Navision didn't implement the insert that way. You would have gained much better performance on insert.

kine · 2008-04-08

Because it will need much more work than just "buffering" the inserts and make them in one "bulk"... do not forget that there is no time to do "big" optimalization on current architecture. One day, the architecture will change and NAV (or what will be the name in these days) will use another method to insert the data...

You know, this change was optimal from "gain/cost" ratio...

NavStudent · 2008-04-08

I don't buy the big change.
I would say buffering insert is a bigger change, Replacing SIFT is a bigger change.

Calling

INSERT INTO Item  ([No], Description)
VALUES ('ABC','afg');
INSERT INTO Item  ([No], Description)
VALUES ('BCD','ghk');

instead of

INSERT INTO Item  ([No], Description)
SELECT 'ABC','afg';
UNION ALL
SELECT 'BCD','ghk';

is minor, and the performance difference huge. 100 or 1000 records. :whistle:

kine · 2008-04-08

Yes, just comparing the SQL queries, it look like easy task. But you need to take into account all the levels between C/Al command and the resulting SQL query. The communication library is working in some way (each command is translated into SQL query) and you can find out that in time when you are producing the SQL query, you have no info about upcomming next insert and you have no context and data to union these inserts. It is why this easilly looking change is a big problem...

DenSter · 2008-04-08

Maybe you should get a job at Microsoft, so you can tell them how to do it

NavStudent · 2008-04-08

kine wrote:

Yes, just comparing the SQL queries, it look like easy task. But you need to take into account all the levels between C/Al command and the resulting SQL query. The communication library is working in some way (each command is translated into SQL query) and you can find out that in time when you are producing the SQL query, you have no info about upcomming next insert and you have no context and data to union these inserts. It is why this easilly looking change is a big problem...

You'll be doing UNION on all the fields. UNIONALL.

All we are doing is stipulating. If MS could give an answer it would be helpful.

NavStudent · 2008-04-08

DenSter wrote:

Maybe you should get a job at Microsoft, so you can tell them how to do it

All, I'm doing is asking a question.

I was asking a while back about indexed views that could be implemented instead of SIFT, and behold SP1 made the change.

DenSter · 2008-04-08

NavStudent wrote:

All, I'm doing is asking a question.

I know what you're doing, and I think it's great. I'm only half joking too, you seem to be thinking a lot about how to improve the base product, and one way to make a difference is to pursue a job at Microsoft. A critical thinker such as yourself would be a welcome addition to any team.

Alex_Chow · 2008-04-08

DenSter wrote:

NavStudent wrote:

All, I'm doing is asking a question.

I know what you're doing, and I think it's great. I'm only half joking too, you seem to be thinking a lot about how to improve the base product, and one way to make a difference is to pursue a job at Microsoft. A critical thinker such as yourself would be a welcome addition to any team.

I wouldn't recommended it. It's probably better to make a ruckus at the forums instead of working in the development team.

You may be bogged down by politics instead of doing what you're doing now.

ara3n · 2008-04-14

There is a new blog on bulk insert.

http://dynamicsuser.net/blogs/navsustat ... 0-sp1.aspx

kriki · 2008-04-15

[Topic moved from Navision forum to SQL Performance forum]

Howdy, Stranger!

Quick Links

Categories

Nav Sp1 5.0 Bulk Insert

Comments