Stack Overflow Exception

Today I got a stack overflow exception, because I have written a recursive function to be called each time with the same conditions thus creating an infinite recursion.

Why Stack Overflow?
This exception occurs when too much memory is used by call stack and consequently it is running out of the allocated memory space. Call stack contains the execution flow of the computer program.

Why stored procedures?

When you compile a stored procedure, SQL Server will create an execution plan and cache it in the server memory. So, next time you don't have to recompile it, thus reducing overhead on server.

Parameter options for SQL stored procedures

Output parameters
You can use output parameters to get the values back to the caller of the procedure using OUT or OUTPUT keyword with the parameter.

@dtmRegDate test.datetime OUT
Assign a default value to a parameter
If you are not passing a value as an argument this value will be set.



Table valued parameters


You can define a table data type and pass an argument of that type (table with some data) to a stored procedure

@dtmRegDate datetime = '1/2/2011',

Why you should not set an extreme value as server time-out?

Time out value is there to limit the amount of processing time allocated for requests. If you assign a larger value as time out, it will stay more time in the queue. So, even though you can always get over from a time out issue by increasing the time out value, ideally what needs to be done is ensure that your application is well optimized so that it will execute within less duration than the specified time out value. It will enhance the server performance.

Dev best practices: Meaningful variable names

Always learn to use meaningful and more descriptive variable names.
Ex: Do not use "arr" to name an Array, use "itemCollection" instead!

How to increase the no. of active connections in a JDBC connection pool?

What is a connection pool?

Connection pool handles the connections to a database requested from a particular web server process. It caches the database connections, so that connections can be reused thus improving the performance.

If you are running out of connections you will get the following server error:
"Host connection pool not found"

How to increase the number of connections in connection pool?

In "installation folder" \tomcat\conf\Catalina\localhost\ROOT.xml file set the maxActive attribute to the prefered value.

Add default values in to a new column for existing rows

If you want to add a new column with a default constraint to a table and insert the default value to all the existing rows, use WITH VALUES expression as given below:

SET ANSI_NULLS ON!

If you set ANSI_NULLS ON, and if you have null as any of the two operands in your comparison statement, SQL Server will return 0, even if you have results with null values that match the given condition.

Using StringBuilder for string concatenation

It is being said that you should always use StringBuilder object to append strings. But you can simply do the same thing using an overloaded + or += operator. Once you compile the program, compiler will tranform the + operator to String.Concat.

So why we should use StringBuilder?

The reason is String is an immutable object, which means if you create a string once you cannot change it again. So, if you keep appending several strings, it will create lot of unnessasary temporary strings in memory.
To clean them up, garbage collection needs to run more often - resulting an additional overhead.

Unlike String, StringBuilder is mutable, so it will reduce the no of temporary objects you create in memory.
Consequently, it will make appending faster than the String.

Avoid unneccassary scope allocation

If you have variables which are not used outside the class, but declared as Public, you should avoid that practice. Declare them as Private. Also, if you have variables which are accesed only with in a particular method, do not declare them in class scope. Define them only in method scope.

Either way we can access the variables to get what we want. Then, why we need to follow this?

Why?

It optimizes memory allocation. For arguments, memory is allocated as stack frames. Once you go out of the scope of variable, the memory allocated for that variable is released. So, more scope you allocate for variables, more memory will be required to hold them.