Creating and maintaining a social website centered on students of San Marcos, Texas. Here students can upload pictures, connect, and find information regarding events and facilitating their academic success.

We have a custom written CRM application with a SQL database backend and a browser based front end. The system is used by 40-50 people and very slow at times but our programmer always seems push the blame elsewhere. A few questions anyone who knows about programming...

1) The browser based front end is written in ASP. I asked him about .Net and he said that standard ASP is just as good but more reliable. Is this true or is it just that he doesnt know how to use .Net?

2) The server than runs SQL is a good spec ML330 (Xeon E5504, 8GB DDR3, 15K SAS, Gigabit) but for some reason the sqlservr.exe*32 process is constantly using CPU cycles. When busy it normally stays around the 60-70% mark and spikes all the time. It seems everytime a new page is loaded or button clicked in the CRM it spikes. Is this normal? I can understand the SQL service using lots of memory and disk read/writes but why so much cpu time?

1) The browser based front end is written in ASP. I asked him about .Net and he said that standard ASP is just as good but more reliable. Is this true or is it just that he doesnt know how to use .Net?

I might fire him for 1) using ASP and 2) making up such extreme BS about it.

ASP is an outdate, outmoded and basically unsupported joke of a system. It was fine when it was the Microsoft competitor for early PHP but to use it in the last nine years is completely ridiculous. I've dropped vendors for using it before (many, many years ago.)

Working with ASP means he is likely writing in VBScript instead of a modern, compiled language.

ASP is interpreted and extremely slow. ASP.NET is compiled and extremely fast. ASP.NET has modern development, testing frameworks, models, tools and security. ASP.NET is likely drastically more stable although both should be perfectly stable.

ASP also lacks 99% of the modern tools of ASP.NET, so your developer is likely spending most of his time writing code that is included in ASP.NET done for you by Microsoft.

I can only guess that you have a developer who doesn't know anything but VBScript and is struggling to use that and is afraid of moving to modern languages because he isn't familiar with object orientation and other basic concepts. Sounds like someone trying to protect their turf.

Definitely sounds like SQL optimization issues. It could be caused by a number of things. Data that's 'too' normalized - requiring multiple database reads and lookups for each data query, too much or too little indexing on critical fields, and possibly structural issues like page size, growth rate, too frequent backups, log shipping, SQL jobs that are 'constantly' running for whatever reason ....

But the real question is ....Why are you not using SugarCRM, or Goldmine, or Dynamics CRM? There are so many packages across the price spectrum from Free to Expensive that there's got to be a match in there somewhere for you.

We are an insurance broker and to be honest the application is much more than a CRM. It manages the import of email leads automatically (customer details), distributes them to individual users in the sales team based on rules. It also has custom reports to track sales performance, lead provider performance, commision performance etc.

Thanks for the advice about SQL. What do you think about the asp front end?

Agree with the above comments, this sounds like a lot of bad queries. Unfortunately I'm not a programmer so I really can't comment on that, but I can say from experience he's doing something wrong because for only 40 - 50 people using it you shouldn't have any issues with the system.

Have you thought about having another programmer come in and take a look at it?

With the information provided, the DB server is definitely powerful enough. Keep in mind that a programmer does not automatically come with good query writing skills, and it could be an issue of too much querying as well.

As far as ASP vs ASP.NET goes, I only know a little. ASP is traditionally a lot of looping through recordsets that the query returned. So if the programmer is using a query to get some data, then looping through it and issuing further queries for each record in that recordset, it can put a lot of strain on the DB. In addition to what was mentioned in other posts, there is the way the tables are being joined together, too many tables being joined, the lack of proper Where clause filtering, etc. etc.

SQL Server has a Profiler for query tuning which can help determine where the slowdown is and whether indexing could help. But the mix of code and querying needs to be looked at as well.

1) The browser based front end is written in ASP. I asked him about .Net and he said that standard ASP is just as good but more reliable. Is this true or is it just that he doesnt know how to use .Net?

I might fire him for 1) using ASP and 2) making up such extreme BS about it.

ASP is an outdate, outmoded and basically unsupported joke of a system. It was fine when it was the Microsoft competitor for early PHP but to use it in the last nine years is completely ridiculous. I've dropped vendors for using it before (many, many years ago.)

Working with ASP means he is likely writing in VBScript instead of a modern, compiled language.

ASP is interpreted and extremely slow. ASP.NET is compiled and extremely fast. ASP.NET has modern development, testing frameworks, models, tools and security. ASP.NET is likely drastically more stable although both should be perfectly stable.

ASP also lacks 99% of the modern tools of ASP.NET, so your developer is likely spending most of his time writing code that is included in ASP.NET done for you by Microsoft.

I can only guess that you have a developer who doesn't know anything but VBScript and is struggling to use that and is afraid of moving to modern languages because he isn't familiar with object orientation and other basic concepts. Sounds like someone trying to protect their turf.

2) The server than runs SQL is a good spec ML330 (Xeon E5504, 8GB DDR3, 15K SAS, Gigabit) but for some reason the sqlservr.exe*32 process is constantly using CPU cycles. When busy it normally stays around the 60-70% mark and spikes all the time. It seems everytime a new page is loaded or button clicked in the CRM it spikes. Is this normal? I can understand the SQL service using lots of memory and disk read/writes but why so much cpu time?

I'm not a SQL Server expert anymore so I'll defer to the SQL advice above from others. But there is some related advice that needs to be added that the non-developers will not be aware of:

ASP, being ancient, doesn't have ORM components that connect to the database, so it has to do more archaic and often "brute force" data access methods. ASP.NET has access to Entity Framework, Hibernate and other ORM options that will speed development, increase security, increase stability and speed data access. So this is a spot where the database might be doing extra work because of bad framework choices.

(For those not aware, ASP and ASP.NET are frameworks, not languages. ASP supports the use of VBScript or JScript and ASP.NET supports any .NET language such as C#, F#, VB.NET, etc. So ASP is like Rails and VBScript is like Ruby. Sort of.)

The reason we use SQL 32bit is that we need to export data to CSV format for an autodialler system and the tools that do this do not work with 64bit SQL. To be honest the SQLSERVER process never uses more than 3GB of Ram, but that may be the upper limit of 32bit. The database is under 2GB in file size.

We had a meeting today with the programmer and i questioned him with some of the information provided from this post. He said the following...

* ASP vs .net is more the programmers preference than a feature decision.

* He admitted ASP is an older technology but did everything we needed it to do

* He said 60% of new applications are still written in ASP

* He uses a combination of vbscript, jscript and ajax in our application.

* in the new version he is moving some of vbscripts that run into SQL procedures which will improve speed.

Any thoughts? Should we demand that the new version is written in .Net?

* ask the programmer if this custom crm is working a-ok on any other client. Get in contact with that client and ask his/her experience.

* have someone from IT look at the database server config: windows and sql. It is not normal that it´s always at 60% and spikes when you click some crm button. I would look into the basics: any AV installed, hdd cache off, wrong raid used, sql memory usage, sql cpu affinity, competing processes, etc

* have someone from networking, maybe there is some bad utp cable, a badly configured switch, maybe the sql server and web server should be hosted on the same box: thus, avoiding network delays

* have some dba take a trace from the sql server and analyze it; maybe there are some important indexes missing?

* if a rewrite is comming... it's a good time to dump the solution altogether and use vtiger or sugarcrm (both open source). We recently dumped our old crm (bought) in favor of vtiger, and made modifications on it

In my experience the key thing is that the end-user can get his/her job done. So, the language or technology used means zip.

It's true that ASP is a very old technology, but a lot of big sites still use it: I wouldn't bet that a complete rewrite is the solution. There are many other things to check before going down this path.

Remember that a complete rewrite also means testing... testing... testing, are you and your users ready for that?

2) The server than runs SQL is a good spec ML330 (Xeon E5504, 8GB DDR3, 15K SAS, Gigabit) but for some reason the sqlservr.exe*32 process is constantly using CPU cycles. When busy it normally stays around the 60-70% mark and spikes all the time. It seems everytime a new page is loaded or button clicked in the CRM it spikes. Is this normal? I can understand the SQL service using lots of memory and disk read/writes but why so much cpu time?

I'm not a SQL Server expert anymore so I'll defer to the SQL advice above from others. But there is some related advice that needs to be added that the non-developers will not be aware of:

ASP, being ancient, doesn't have ORM components that connect to the database, so it has to do more archaic and often "brute force" data access methods. ASP.NET has access to Entity Framework, Hibernate and other ORM options that will speed development, increase security, increase stability and speed data access. So this is a spot where the database might be doing extra work because of bad framework choices.

(For those not aware, ASP and ASP.NET are frameworks, not languages. ASP supports the use of VBScript or JScript and ASP.NET supports any .NET language such as C#, F#, VB.NET, etc. So ASP is like Rails and VBScript is like Ruby. Sort of.)

Sorry but you are not entirely correct about ORM (object relational mapping). ORM is all about code generation that helps the programmer with the impedence mismatch between object-oriented programming language and a relational data store. Yes they can speed development and increase stability, but as for security that's arguable. As for speeding data acess I would entirely dissagree, from my experience and others. The problem is the ORM tools generate the data access code for you including all SQL queries, and have the additional overhead of mapping objects to relational data stores, so the data access is going through an un-optimised layer and you have no access to the queries the ORM is using to optimise the SQL. I know from experience I would rather access data through optimised SQL Stored Procedures taking full advantage of relational and set theory than use any ORM, even if that is considered brute-force and archaic.

Also ASP.NET is not compiled in the sense that C++ is compiled to native machine code. ASP.NET is compiled to Microsoft Intermediate Language (MSIL) which is at runtime interpreted by the .NET Framework to native machine language - It's also only compiled to MSIL when first accessed.

I would also like to make a comment about looping through data. In fact on a web server you actually want, if it fits your needs, your code to loop through a forward-only stream of data rather than bloat the web server's memory by generating some massive dataset object, with tons of methods and properties that you are not going to use or need.

2) The server than runs SQL is a good spec ML330 (Xeon E5504, 8GB DDR3, 15K SAS, Gigabit) but for some reason the sqlservr.exe*32 process is constantly using CPU cycles. When busy it normally stays around the 60-70% mark and spikes all the time. It seems everytime a new page is loaded or button clicked in the CRM it spikes. Is this normal? I can understand the SQL service using lots of memory and disk read/writes but why so much cpu time?

I'm not a SQL Server expert anymore so I'll defer to the SQL advice above from others. But there is some related advice that needs to be added that the non-developers will not be aware of:

ASP, being ancient, doesn't have ORM components that connect to the database, so it has to do more archaic and often "brute force" data access methods. ASP.NET has access to Entity Framework, Hibernate and other ORM options that will speed development, increase security, increase stability and speed data access. So this is a spot where the database might be doing extra work because of bad framework choices.

(For those not aware, ASP and ASP.NET are frameworks, not languages. ASP supports the use of VBScript or JScript and ASP.NET supports any .NET language such as C#, F#, VB.NET, etc. So ASP is like Rails and VBScript is like Ruby. Sort of.)

+1 here for asp vs .net. If he is writing in asp, then he needs to update his skills to the current standards, or even stanards from a couple of years ago.

2) The server than runs SQL is a good spec ML330 (Xeon E5504, 8GB DDR3, 15K SAS, Gigabit) but for some reason the sqlservr.exe*32 process is constantly using CPU cycles. When busy it normally stays around the 60-70% mark and spikes all the time. It seems everytime a new page is loaded or button clicked in the CRM it spikes. Is this normal? I can understand the SQL service using lots of memory and disk read/writes but why so much cpu time?

I'm not a SQL Server expert anymore so I'll defer to the SQL advice above from others. But there is some related advice that needs to be added that the non-developers will not be aware of:

ASP, being ancient, doesn't have ORM components that connect to the database, so it has to do more archaic and often "brute force" data access methods. ASP.NET has access to Entity Framework, Hibernate and other ORM options that will speed development, increase security, increase stability and speed data access. So this is a spot where the database might be doing extra work because of bad framework choices.

(For those not aware, ASP and ASP.NET are frameworks, not languages. ASP supports the use of VBScript or JScript and ASP.NET supports any .NET language such as C#, F#, VB.NET, etc. So ASP is like Rails and VBScript is like Ruby. Sort of.)

Sorry but you are not entirely correct about ORM (object relational mapping). ORM is all about code generation that helps the programmer with the impedence mismatch between object-oriented programming language and a relational data store. Yes they can speed development and increase stability, but as for security that's arguable. As for speeding data acess I would entirely dissagree, from my experience and others. The problem is the ORM tools generate the data access code for you including all SQL queries, and have the additional overhead of mapping objects to relational data stores, so the data access is going through an un-optimised layer and you have no access to the queries the ORM is using to optimise the SQL. I know from experience I would rather access data through optimised SQL Stored Procedures taking full advantage of relational and set theory than use any ORM, even if that is considered brute-force and archaic.

Also ASP.NET is not compiled in the sense that C++ is compiled to native machine code. ASP.NET is compiled to Microsoft Intermediate Language (MSIL) which is at runtime interpreted by the .NET Framework to native machine language - It's also only compiled to MSIL when first accessed.

I would also like to make a comment about looping through data. In fact on a web server you actually want, if it fits your needs, your code to loop through a forward-only stream of data rather than bloat the web server's memory by generating some massive dataset object, with tons of methods and properties that you are not going to use or need.

Sorry just wanted to clear up some confusion here

That's dependent on the ORM. Some allow for optimizations, some do not. It's a general technology with lots of different implementations. It is true that you can certainly optimize around an ORM, but people using ASP aren't likely people optimizing SQL by hand either, I'm guessing.

However, the speed that I'm talking about isn't from better queries but fewer of them. Good ORMs have caching built in and can reduce calls to the data layer altogether making a call with a cache it much faster than even a well optimized query. It doesn't help with every db call and every app will behave differently, but without a cache layer you have a natural performance disadvantage. Of course, you can build in your own cache too, but that's a lot of work.

.NET compilation is like Java compilation, more or less. It goes to a target layer. The running of that target layer is more like virtualization than like traditional interpretation (extremely fast in both cases,) I believe in both cases. I'm more familiar with the Java process but I believe that they are the same here.

1st Post

Hi Andy,

Its hard to tell without looking at the code but ASP and SQL should be fine for an installation that size. You will see spikes and a high-idle, that is normal for a database server. Databases are constantly reorganizing records (caching, etc).

If you have the resources you might try moving the database to one device and the web server to another. This obviously reduces load but also lets you tune them independently.

Some SQL statement tuning might also be possible (not using select * for example)

Its hard to tell without looking at the code but ASP and SQL should be fine for an installation that size. You will see spikes and a high-idle, that is normal for a database server. Databases are constantly reorganizing records (caching, etc).

If you have the resources you might try moving the database to one device and the web server to another. This obviously reduces load but also lets you tune them independently.

Some SQL statement tuning might also be possible (not using select * for example)

Ruby should be fine too. It's mostly telling that the developer won't move off of ASP more than anything. It's not that ASP can't do the job, it certainly can. It's more of a WHY make ASP do that job. It takes more time to write and has no technical advantages. A programmer who is defending ASP as the "right" choice seems likely to have other issues. A programmer defending ASP as "well it's already in ASP so it's not worth changing now" is different.

0

This discussion has been inactive for over a year.

You may get a better answer to your question by starting a new discussion.