4

We have a lot of old legacy Perl scripts that connect to a MS SQL db process some records, and make files. Over time, the daily transaction size grew and these scripts are becoming more and more expensive.

Moreover, the Databases grew with more and more tables, and modifying the old Perl scripts is cumbersome. Was thinking about redoing some of the major scripts under .NET (in C#)

Is there a speed advantage under a machine running Windows Server of using one vs the other?

Again the idea is

  1. Execute Query

  2. Process Results through some basic formatting

  3. Write results to a file

Brad Gilbert
  • 33,846
  • 11
  • 78
  • 129
Sam
  • 946
  • 3
  • 11
  • 22
  • 3
    Depending on how complex the "basic formatting" is, SSIS may be a better fit for your problem. – Damien_The_Unbeliever Jan 25 '12 at 07:27
  • 99% of the formatting is done at the query level anyway. Perl mainly grabs the query, does some date checking etc. From a hit perspective, 99.9999% of the cost is the actual writing to file, – Sam Jan 25 '12 at 07:31
  • 2
    Your issue may relate more to the speed of the disks involved than the actual language. It might be beneficial to run some performance stats and see if your scripts are saturating the server from an I/O perspective. Your solution may be SSD instead of C#. – kdmurray Jan 25 '12 at 07:37
  • so the consensus is that bottleneck is the HDD? – Sam Jan 26 '12 at 21:23

3 Answers3

10

Depends on how stupid the respective programmers are. When initialized properly they both should be comfortable saturating whatever bandwidth you throw at them disc system wise - and THERE is your bottleneck. Make large cached writes (.NET BufferedStream) and make ure you have a SSD or something fast ready. The perforamcne bottleneck with proper programming is the disc subsystem for this type of work.

TomTom
  • 61,059
  • 10
  • 88
  • 148
1

Both tasks can be done equally fast in either language. They can also both be done horribly wrong and horribly slowly in both languages, so there is that to consider.

From another one of your comments, you mention that you do the formatting on the SQL server side. These queries would potentially be a lot less expensive if you did that on the app side, and then moved this script to a faster machine so as to impact the db server the least.

I'd guess that harddrive speed is your biggest problem now. You should monitor the resources while running this script -- Is it maxing out your cpu? is it reading/writing a lot to memory?(it shouldnt). Is it just waiting on disk i/o most of the time? If it is, you should look into upgrading your storage to either faster disks, a raid, or an ssd depending on what makes the most sense for your situation.

Even just something like defragging the disk might help.

If you have good cpu/memory to spare but cant avoid the slow disk, you could even look into compressing all of the output in memory before writing it (again, assuming this is a good idea, it really depends on where these reports are going and what format is needed).

semi
  • 612
  • 5
  • 14
-3

I don't expect great differences between those languages when it comes to write performance (generally bottleneck will be hard disk, not processing power of CPU) . You should rather take a look on "costs of maintaining code", in C# you can get much cleaner code, not to mention that integration with MS SQL probably will be more efficient, not to mention that you can use threading.

Better Code, more maintainable, maybe faster. Yeah C# is good idea

Xamael
  • 257
  • 1
  • 9