Question on speed, native v. SQL

matttrax · 2008-06-19

Obviously some of this depends on the version of SQL you're running, but am I correct in the following general assumptions?

1. Native can only use one processor, but SQL can use up to the number allowed in the version

2. If I run a process on a single-core machine and a quad-core machine, the quad core will be four times faster if the CPU speeds are the same?

I ask because during step 2.10 of the upgrade toolkit, the value entries get updated. 5 million value entries updating, each one taking an average of 0.8 seconds. That's 46 days of continuous processing, which we just can't have. Trying to figure out what to do.

Savatage · 2008-06-19

When I think about speed I think about the number of disks. The more heads reading/writing the better.

Alex_Chow · 2008-06-19

Probably best if you outsource that upgrade project to a certified upgrade center. They'll be able to help you out so it's not 46 days of upgrading.

davmac1 · 2008-06-19

If you are running in SQL Server, then run some of the steps in SQL.

If you don't know which, then find someone who knows enough to run it safely.

Slawek_Guzek · 2008-06-20

Hi:

matttrax wrote:

1. Native can only use one processor, but SQL can use up to the number allowed in the version

Yes. More detailed answer is:

1a. Navision Native database server uses always one processor, or one core on multi-core processors
1b. SQL Server uses all available processors and cores up to allowed or configured number
1c. Navision Client always uses one processor or one core on multi-core processor regardless of database server it connects to.

2. If I run a process on a single-core machine and a quad-core machine, the quad core will be four times faster if the CPU speeds are the same?

It depends.

Index creation for example is much faster on quad-core machine because indexes are recalculated purely by SQL server and NAV client is not involved in that process except of initializing it. If the rest of the system components are fast enough to not to be bottlenecks then quad core will be much faster than on single core machine, but not four times, as performance doesn't scale up in linear manner.

Upgrade Toolkit is most probably written much in the same was as NAV Client, so I would assume that this is single threaded application, which doesn't use multiprocessor capabilities. But this is my guess only.

Slow processing speed might be also limited by your network. Try to run Upgrade Toolkit directly on the server, or on fast machine having 1GB connection to the SQL server.

However I think in your case the best approach would be outsource the project as Alex said.

Regards,
Slawek

kriki · 2008-06-20

Slawek Guzek wrote:

1b. SQL Server uses all available processors and cores up to allowed or configured number

Considering you run only 1 process for the upgrade, the fact that you have multiple cores will not help. Because for best performance it is better to put parallelism to 1 core only for Navision. Dividing a Navision request on multiple cores generally is counterproductive because the Navision requests are too simple.
It is better to use multiple cores to serve multiple requests.
The only moment it using multiple cores for 1 request is when you do an indexrebuild.
The best you can do for speed is outsource it.

And if you want to do it yourself, best go for a lots of fast disks.
-If you can dedicate a server to do just this, I wouldn't even use RAID1 or RAID10 for safety but go FOR RAID0 for striping as much as possible.
So copy the DB on this server. Do the upgrade (for some time you have to hope no disks fails because in this moment you have to redo everything. But the change of diskfailure is very low:and if it should happen, continue to work on the old DB, fix the drives and retry again later).
So:
-a 2 disk RAID0 for the logfile (if you have less then 4 disk for the DB, use only 1 disk for the TL and add the other to the DB)
-a N-disk RAID0 for the DBfile (where N is at least 4; the bigger N is, the better )
-1 disk for system + tempDB
-If you have a 2-core system, this is already enough for the upgrade.

Slawek_Guzek · 2008-06-20

kriki wrote:

The only moment it using multiple cores for 1 request is when you do an indexrebuild.

Not true. Even simple query may use multiple cores.

For example - writing to tempdb (if necessary) - process scheduler may be attached to separate worker thread
If the database is spread across multiple files located on different drives - every file gets served by separate worker thread.

-a 2 disk RAID0 for the logfile (if you have less then 4 disk for the DB, use only 1 disk for the TL and add the other to the DB)
-a N-disk RAID0 for the DBfile (where N is at least 4; the bigger N is, the better )
-1 disk for system + tempDB

I would rather suggest to make writes to the log as fast as possible because log writes are NOT cached in SQL memory in opposite to data writes.

bbrown · 2008-06-20

matttrax wrote:

...
1. Native can only use one processor, but SQL can use up to the number allowed in the version

...

Applications are not single processor or multi-processor. They either are or are not multi-processor aware. Applications that are multi-processor aware are able to instruct the system to execute certian functions on specific processor. Application that are not simply treat the processor as a resource pool.

If you run Performance Monitor on a multi-processor box that is running native, the other processor are not sitting idle. In fact you will find them all running at about the same rate. Try then same thing on SQL and you will find that the usage % varies between processors.

Question on speed, native v. SQL

Comments

Categories