Lesson 2: Resolving Performance Problems in VS .NET

Encoding GS1 - 12 in VS .NET Lesson 2: Resolving Performance Problems

Summary

Horizontal data partitioning is a method of moving data from one table or database to other data repositories. Data can be partitioned across multiple filegroups on the same server or moved to databases on remote servers. Targeting multiple database servers is an effective way to scale out an application and allow it to accommodate more users. You can use several techniques to scale out your application. You might have to use one or more of the following to achieve the desired result: scalable shared database, peer-to-peer replication, linked servers, distributed partition views, data-dependent routing, and service-oriented data architecture. You can use SQLCLR to execute managed code for the .NET Framework on your SQL Server. In some cases, this can provide a performance advantage. You can write code using Visual Basic .NET, C#, or J#. To analyze and resolve database application performance problems, you should use tools such as Windows System Monitor, SQL Server Profiler, and the newly included distributed management views.

Because the new application will only need to access data from a SQL Server database and you are targeting the .NET Framework, you can use the ADO.NET data provider. It is unlikely you will need to take advantage of the newer features available with SQL Server, given the application is being converted from an older application and given your time constraints. For that reason, it is not necessary to consider SNAC as a data provider. You can also use TCP/IP as the network protocol to provide the fastest access to the data across the Internet. You can also suggest that they use Visual Basic .NET as the language because the development team is already accustomed to using prior versions of Visual Basic. This should help to speed the conversion time.

In cases where you are not able to modify Transact-SQL directly, you can still improve query performance using a plan guide. The plan guide is used to override the execution plan automatically generated by the query optimizer. As long as the execution plan you come up with returns the same results and performs better than the original query, this is a good way to affect performance without altering the code.

It might not be necessary to replace all the cursors in this poorly performing application. The best place to start is by identifying those areas of the application that are experiencing the largest delays. You could use SQL Server Profiler to record a trace while the application executes some of the slower functions. The trace recording can be used as input for the Database Engine Tuning Advisor, as an alternative to replacing the cursor. You might be able to resolve the application problems by simply applying a few indexes. If the application performance continues to suffer, then you should consider replacing some of the cursors with set-based alternatives. You would start by identifying those cursors that take the longest to execute. For these long-running stored procedures, try to find a way to replace the cursor with one of the alternatives mentioned in Lesson 2, Designing a Cursor Strategy. Compare the execution times for the alternative with the original cursor to determine

if the change was successful. You might just have to do this procedure with a few of the cursors to achieve the desired performance results. There is a tradeoff to consider between development time and application performance.

The data-mining features available with SQL Servers Analysis Services (SSAS) enable you to locate trends in large sets of data for the purpose of making predictions. You could utilize SSAS to create a data-mining solution that processed the sales history data and predicted what products the customer would likely purchase. In order to keep the recommendations up-to-date, and based on the latest sales data, the datamining solution would need to be automated so that it was re-processed on a nightly or even hourly basis. Analysis Management Objects (AMO) could be used to accomplish this task. Not only could you develop code that automated the processing of the mining model, but this code could be added to your ASP.NET application to query the mining model for predictions. These predictions would then be presented to the customer as customized product recommendations.