Benefits of Stored Procedures

Before I go on to explain the benefits, let me explain why I am writing this. We use stored procedures as a standard practice, not thinking twice of the very reason behind this.

I realized that we follow standard practice without questioning it, not understanding its perks and pit falls, so I decided to list out the benefits I understand and share it with my team via this blog. Benefits are listed below I will explain the points one by one in the future.

Stored procedures offer many benefits, including:

• Reduced network traffic and latency, boosting application performance.
  
  
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  
  
• Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.
  
  
• Stored procedures help promote code reuse. While this does not directly boost an application's performance, it can boost the productivity of developers by reducing the amount of code required, along with reducing debugging time.
  
  
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients (assuming you keep the parameters the same and don't remove any result sets columns). This saves developer time.
  
  
• Stored procedures provide better security to your data. If you use stored procedures exclusively, you can remove direct SELECT, INSERT, UPDATE, and DELETE rights from the tables and force developers to use stored procedures as the method for data access.

Warning: using stored procedure does not garuntee fast speed, there has to some level of code design, to make the procedure fast and reusable.

Please add comments to blog, i would like to hear what you this of this.

Use view to improve query performance

Recently I came across two such scenarios where a large dataset (1,000,000 + rows) was being retrieved using a complex set of joins in stored procedure, we did all the regular stuff
1. Checked Indexes - made sure they are on the right columns and verified the fill factor
2. Re indexed the database for better performance
3. Checked and removed the non essential joins
4. Verified join types

we did have some improvement in performance, but nothing like we had with using views instead of complex select query, we were able to get performance levels increased by 10 t0 40 times in specific cases.

Now the question is why?
I read the following while searching on another topic and it struck me, a view is an optimized query object, SQL server itself chooses the best execution plan and then when we do a filter on the view the nested loops are decreased considerably as only one select is being filtered rather that all the tables in the join..... It really helps

From the Database Management System (DBMS) perspective, a view is a description of the data (a form of metadata). When a typical view is created, the metadata is defined by encapsulating a SELECT statement that defines a result set to be represented as a virtual table. When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for the executing query. The query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.

FTP without FTP client on windows XP

I am sure everyone has used FTP and FTP Clients one time or the other. what happens when you want to connect to a ftp and dont have a ftp client, i just got to know of connecting to FTP with DOS, so i am sharing the info

1) Open a command windows (start->run_.cmd)
2) Type ftp
3) Type open ftp.ftp.somesite.com
4) Type username: ftpuser
5) Type the password: ftppassword
6) Type “dir” to see the content
7) Type “binary” Or "ASCII" to change to download Type
8) Type: “get filename” to download

also note that the filenames are case sensitive here so be careful.