Search This Blog

Friday, November 28, 2008

SQL 2005 Paging Method Using CTE(Common Table Expressions)

Following procedure used to get Customers Details with Paging.

Create procedure GetCustomersWithPaging

@PageSize INT, --No of Records per Page
@PageNumber INT--selected Page Number

set nocount on

Declare @FirstRow INT
Declare @LastRow INT

SELECT @FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

WITH Members AS
SELECT CustomerID,ContactName,Address,
FROM Customers
SELECT RowNumber,CustomerID,ContactName,Address
FROM Members
WHERE RowNumber BETWEEN @FirstRow AND @LastRow


Monday, November 24, 2008

SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL

Update: For SQL Server 2008 there is even better method of Row Construction, please read it here : SQL SERVER - 2008 - Insert Multiple Records Using One Insert Statement - Use of Row Constructor
How can I insert multiple values in table using only one insert?
Now this is interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.


INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol) VALUES ('Fifth',5);


The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps me focus on task, instead of copy paste. I have explained following script to new developer. He was quite pleased.

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1

Thursday, November 13, 2008

Important Points On Workflows :

1. ability to change a business process on the fly
2. Any application that implements a long-running process is a natural for workflow. Processes that interact with people, who can take hours or days to respond, are one important instance of this. For example, building a document approval application around a workflow would make good sense.
3. An ASP.NET application that displays pages to its users might use a workflow to control the order in which those pages are shown. Doing this can make it easier to change the page flow without changing the pages themselves, as well as cleanly separating the application’s user interface from its controlling logic.
4. WF can make the application faster to build, quicker to change, and easier to customize.
5. The ability to maintain state throughout the workflow’s lifetime. Especially when people are involved, as with the manager in this example, a workflow can take a long time to complete. (What if the manager has left for the day, or is on a two-week vacation?) Building scalable systems also requires a way to deactivate the workflow and store its state persistently, then reactivate it and load that state when the next step can be executed.
6. The purpose of WF is not to be a complete workflow solution for Windows. Instead, the goal is to make it easier for software developers to create workflow-based Windows applications.
7. workflow consists of a group of activities­­­
8. The workflow acts as a container for these activities, providing a way to control their lifecycles and order of execution.
9. WF provides two built-in workflow types: sequential workflows, capable of executing activities in a pre-defined pattern, and state machine workflows, capable of responding to external events as they occur. Both rely on the same runtime environment, and both can use the same custom activities.
10. Framework 3.5. In this version, it’s straightforward to use WF and WCF together to create workflow-enabled services.
This combination depends on two new WF activities:

Sequence Workflows are Sequence driven workflow
State Machine Workflows are Event Driven workflow(External Events are raised by external user)

UFrame = UpdatePanel + IFRAME

What is UFrame?

UFrame is like an IFRAME that can load and host a page (ASP.NET, PHP or regular html) inside a DIV. However, unlike IFRAME which loads the content inside a frame that has no relation with the main document, UFrame loads the content within the same document. Thus all the Javascripts, CSS on the main document applies to the loaded content. It's just like UpdatePanel with IFRAME's "src" attribute.
The above UFrame are declared like this:

This should get replaced with content from /SomePage/BCD/View

The features of UFrame are:

Load any URL inside a DIV. It can be a php,, jsp or regular HTML page.
Just like IFRAME, you can set "src" property of DIVs and they are converted to UFrames.
Unlike IFRAME, it loads the content within the main document. So, main document's CSS and Javascripts are available to the loaded content.
It allows you to build parts of a page as multiple fully independent pages, instead of controls.
Each page is built as standalone page. You can build, test and debug each page indepedently and them put them together using UFrame.
It loads both inline and external scripts from loaded page. You can also produce different scripts during postback.
All external scripts are loaded before the body content is set. And all inline scripts are executed when both external scripts and body has been loaded.
It loads both inline and external CSS .

Performance Characteristics of Windows Workflow Foundation

I would say that everybody who started developing applications using WF has to read that whitepaper to clearly understand the performance implications, associated with the various pieces of WF. And another thing is this document is highly readable one as the content is succinct as well as interesting. Kudos to Microsoft and we expect such documents on other two technologies (WPF and WCF) too.

I consider the following 10 points are significant ones to be noted down, while I certainly don’t ignore other points that are being discussed out in that document, with respect to the development of better performing WF application.

  1. Understand the performance factors of each Out-of-Box runtime services provided in WF.
  2. Understand the way threads are being used when you use DefaultWorkflowScheduler or ManualWorkflowScheduler services.
  3. Understand the performance factors of each Out-of-Box runtime services provided in WF. For example, out of two transaction services such as DefaultWorkflowCommitWorkBatchService and SharedConnectionWorkflowCommitWorkBatchService, it is essential to know that we have to use the later when SQL Server 2000 is being used as backend database for persistence and tracking, as it eliminates the overhead of bringing in MS DTC.
  4. Avoid persistence when it is not required. If it is required, you need to know what are getting persisted and size of the persisted state. At least make sure that unwanted fields/properties are not persisted.
  5. Whenever possible, create custom activity rather than using one from Base Activity Library. While activities from BAL provide more flexibility, that flexibility may also prove to be a overkill.
  6. Customize the transaction through implementation of IPendingWork and IWorkBatch interfaces, instead of using default TransactionScopeActivity and CompensatableTransactionScopeActivity, if possible.
  7. Understand the differences between CompensatableSequenceActivity and CompensatableTransactionScopeActivity and choose the appropriate one depends on the application’s requirement.
  8. Understand the differences between Code conditions and declarative rule condition and choose appropriate one. If you use a declarative ruleset, don’t forget to set the priorities and chaining behavior in a ruleset.
  9. Just think whether you really need dynamic update feature in your workflow and avoid this feature as much as possible.
  10. Please remember to tune a set of performance related settings such as EnablePerformanceCounters, EnableRetries( of Persistence and transaction services), IsTransactional (of SqlTrackingService) etc.And this whitepaper also provides some scenario based test results and case study that comprises of a series of seven tests. In my view, scenario based test results section should have included the comparison of performance of a WF application, hosted in different types of application such as Console, Service, Windows Form and ASP.NET application. This would have addressed the dilemma in choosing an appropriate host, particularly when people are dilly-dallying with Web Server and console host. While this document explained well about the performance factors associated with the two services – DefaultWorkflowScheduler and ManualWorkflowScheduler and suggested tuning the setting such as MaxSimultaneousWorkflows property, some more additional tuning would be definitely necessary. Before going to that detail, let us see the thread implications of using either of those services.
DefaultWorkflowSchedulerService is the default scheduler service of WF runtime. This Scheduler Service takes care of choosing an appropriate thread to instantiate and run the workflow instance. This service runs the workflow instance asynchronously using a worker thread rather than the main thread of the host application. It leaves the main thread to continue with executing some other task in parallel. It is really an appreciable fact particularly when WF application is hosted in WinForm or WPF applications. But we can not say the same when ASP.NET hosts the WF application.

Let us consider a scenario where ASP.NET hosts WF application and a method in code behind file starts a workflow instance and returns the result back. In this case, the thread that is associated with the http request (that invokes that web method) will not be used to create the workflow instance. Instead a new thread will be taken out of default managed CLR thread pool to start and run the workflow instance. Till the execution of instance is done with, the main thread will be in limbo as the result needs to be sent back to the requester. It means, two threads are taken from processor wide managed thread pool for a single http request. While it does not seem to be alarming, it is indeed alarming one if we consider the fact that there are only 25 worker threads per available processor. What is more alarming is that the craving for threads does not end with just 2 if the application contains the complex workflow that may invoke other workflows using InvokeWorkflow activity or consume web services that are hosted in the same/external web server (if web service hosted in the same web server, that would also use the same thread pool) or using the persistence and transaction service. In that case, the number of threads, that are being taken out from managed thread pool is many and it would cause the thread pool to starve and needless to say, would affect the scalability of the application badly.
An alternative service – ManualWorkflowSchedulerService is recommended for WF application that is to be hosted in IIS, to mitigate the above mentioned problem. This service reuses the main thread (associated with the http request) to instantiate and run the workflow instance. However, we have to remember the fact that still a few more threads would be needed as they are required for invoking web service calls and invoking other workflows and for persistence service (Actually when this service tries to complete a persistence transaction, transaction object would requires a thread from thread pool). But side effect of the usage of this service is, the workflow instance will run in synchronous manner, as the main thread is being used to instantiate and run the workflow.

This is however acceptable, as the main thread should normally be kept in wait state till the workflow instance gets executed. Though the excess number of threads is pulled out from thread pool for a complex workflow application, it may or may not warrant the fine tuning of the managed thread pool, depends on the complexity of the workflow application. But in some other case, more than one workflow instance may be created within an ASP.NET web method. This scenario would require more number of threads depends on the complexity of the workflow application. This would definitely require the fine tuning of the CLR thread pool.

Whenever, such tuning related to threads is necessary, the settings to be taken care of , in ASP.NET configuration in Machine.config file (or editing the metabase in the case of IIS 6.0) are
ConnectionManagement element settings
maxConnection – that enables executing more remote web service calls concurrently.
ProcessModel element settings
maxIOThreads – to increase the number of available worker threads;
maxWorkerThreads – to increase the number of IO threads;
HttpRuntime element settings
minFreeThreads – to set aside a number of threads that will not be used for http request handling
minLocalRequestFreeThreads – the number of threads that is to be set aside to process local web service calls.

Microsoft has recommended changing the default values of all of above settings. See the following table that provides the new values.

Configuration setting...............Default value.................... Recommended value
maxConnection .................... ..........  2 ......................................... .....  12 * #CPUs
maxIOThreads ................................ 20 ............................................... 100
maxWorkerThread .........................  20 ............................................... 100
minFreeThreads ..............................  8 .. .............................................. 88 * #CPUs
minLocalRequestFreeThreads .......    4 ................................................. 76 * #CPUs

Having tuned those settings, now we can try increasing the MaxSimultaneousWorkflows (number of workflows that would be allowed concurrently) value of DefaultWorkflowSchedulerService in WF application, from its default value (5). Careful monitoring of certain thread related performance counters are essential to conduct this tuning. Otherwise, it may lead to thread starvation that would impact the scalability.
Building Perstistence in Workflows

Calling Web Service using Sequential Workflow

How to Use Web Service in Sequencial Workflow

Developing N-Tier Application using VS 2008

WWF Related Sites (No need to search for WWF):

State Machine Workflow

Wednesday, November 12, 2008

why use Windows Workflow Foundation?

Microsoft has provided this foundation in order to simplify and enhance your .NET development. It is not a stand-alone application. It is a software foundation that is designed to enable workflows within your applications. Regardless of the type of application you are developing, there is something in WF that you can leverage.If you are developing line-of-business applications, you can use WF to orchestrate the business rules. If your application comprises a series of human interactions, you can use a WF state machine workflow to implement logic that can react to those interactions. If you need a highly customizable application, you can use the declarative nature of WF workflows to separate the business logic from the execution flow. This allows customization of the flow of control without affecting the underlying business logic. And if you are looking for a better way to encapsulate and independently test your application logic, implement the logic as discrete custom activities that are executed within the WF runtime environment.There are a number of good reasons to use WF, and here are a few of them:• It provides a flexible and powerful framework for developing workflows. You can spend your time and energy developing your own framework, visual workflow designer, and runtime environment. Or you can use a foundation that Microsoft provides and spend your valuable time solving real business problems.• It promotes a consistent way to develop your applications. One workflow looks very similar to the next. This consistency in the programming model and tools improves your productivity when developing new applications and your visibility when maintaining existing ones.• It supports sequential and state machine workflows. Sequential workflows are generally used for system interactions. State machine workflows are well suited to solving problems that focus on human interaction.• It supports workflow persistence. The ability to save and later reload the state of a running workflow is especially important when modeling human interactions and for other potentially long-running workflows.• It supports problem solving using a domain-specific model. Microsoft encourages you to develop your own custom activity components. Each custom component addresses a problem that is specific to your problem domain and uses terminology that is common to the domain.• It provides a complete workflow ecosystem. In addition to the workflow runtime itself, Microsoft also provides a suite of standard activities, workflow persistence, workflow monitoring and tracking, a rules engine, and a workflow designer that is integrated with Visual Studio, which you can also host in your own applications.• It is infinitely extensible. Microsoft provides a number of extension points that permit you to modify the WF default behavior. For example, if the standard SQL persistence service that is provided with WF doesn’t meet your needs, you can implement your own.• It is included with Visual Studio and available for use in your applications without any additional licensing fees. Because of this, it has become the de facto standard workflow framework for Windows developers. The growing communities of WF developers frequently share their ideas, custom activity components, and other code.

Tuesday, November 11, 2008


హాయ్ థిస్ ఇస్ చల్ల thఇస్ ఇస్ మై ఫిర్స్త్ ప్రేసేన్తషన్ ఆన్ వ్వ్ఫ్ (స్టేట్ మెషిన్ వర్క్ఫ్లో)

చల్ల శ్రీనివాస్ రెడ్డి

Popular Posts