27 October 2010

Which is the best way to count number of records in a table.

In SQL Server 2005 and above, which query is the fastest and most reliable for returning an accurate record count?

  • Select Count(*) from DB1.Table1
  • Select Convert(bigint,[rows]) from Sys.sysindexes where id=Object_Id(DB1.Table1) and inid<2;
  • select cast(p.rows as float) from sys.tables tbl inner join sys.indexes as idx on idx.object_id=tbl.object_id and idx.index_id<2 inner join sys.partitions as p on p.object_id=cast(tbl.object_id as int) and p.index_id=idx.index_Id where tbl.name=N'Table1' and SCHEMA_NAME(tbl.schema_id)='DB1'
  • Select Sum(row_count) from Sys.dm_db_partition_stats where object_Id=OBJECT_ID('DB1.Table1') and (index_id=0 or index_Id=1)

Reviewing the execution plan of all 4 queries as a batch reveals that Query 1 is the slowest costing about 90% of the total run time.
Query 3 is the next slowest costing about 9% of the total run time.
Queries 2 and 4 cost approximately the same amount of the total run time but Query 2 uses the sysindexes table, which may not be as reliable because the accuracy will be determined by when the statistics have been updated. In addition, Microsoft says sysindexes will be removed in future versions.
This makes Query 4 the fastest and most reliable way for returning an accurate record count.

25 October 2010

OOPS Part1-Classes

  • Objects with similar properties and methods are grouped together to form a Class. Thus a Class represent a set of individual objects or in short we can say class is a template or an bluprint from which we can create objects.
  • Characteristics of an object are represented in a class as Properties . The actions that can be performed by objects becomes functions of the class and is referred to as Methods.
        For example: Consider we have a Class of Cars under which Santro Xing, Alto and WaganR represents individual Objects. In this context each Car Object will have its own, Model, Year of Manufacture, Colour, Top Speed, Engine Power etc., which form Properties of the Car class and the associated actions i.e., object functions like Start, Move, Stop form the Methods of Car Class.
  • No memory is allocated when a class is created. Memory is allocated only when an object is created, i.e., when an instance of a class is created.

17 October 2010

Removing Items from a Collection

----> Its seems easier than it is.... Just loop through an array of items and remove items from it.However,when you
remove an item from a collection while looping through it.the size of the collection changes and the loop may fail(depending on how you are looping).
The correct way to loop through a collection is to essentially loop through it backwardsmstarting with the last and moving towards 0'th element.

  For(int i=MyList.count-1;i>=0;i--)

13 October 2010

Optimizing asp.net applications

   Performance tuning is an extremely important issue to both the developer writing code and the system administrator maintaining the application.
    The following guidelines list specific techniques that you can use to avoid performance bottlenecks.

1.State Management
    Disable session state when you are not using it. Not all web application require a session state for each user.
To disable session state for a complete page in your application, use the following code.        

                     <%@ page EnableSessionState="false" %>
To disable  session state for the entire application, open the 'web.config' file, and set the mode attribute to off under the session state section as in the following code:
                    <SessionState mode="off" />

2.Data Access1.Use SQL Data Reader instead of datatabe and dataset whenver it is possible
2.Use compiled stored procedure instead of SQL plain text commands, this gives your application an extra speed performance. SQL server builds and stores execution plans for previously stored procedures.
3.use of Paging  in data Controls like gridview and listview
4.Avoid Multiple trips to Database

3.Use HTML controls whenever possible.

4.Avoid round trips to server whenever possible.
For example, validating user inputs can always (or at least in most cases) take place in the client side. There is no need to send these inputs to the server to check their validity

5.Use Page.IsPostBack to avoid performing unnecessary processing on a round trip.

6.Server Controls View State
Server control by default saves all the values of its properties between round trips, and this increases both page size and processing time which is of course an undesired behavior. Disable the server control view state whenever possible.

7.Before deploying your web application  disable the debug and trace mode.

8.Exception handling
Avoid common exceptions like null reference, dividing by zero , and so on by checking them manually in your code. Dont always rely on TRY CATCH.

9.Use of StringBuilder Class when there string is modified more than 2 times.

10.Use the Server.Transfer method to redirect between pages in the same application.
Using this method in a page, with Server.Transfer syntax, avoids unnecessary client-side redirection.

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...