Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 19th, 2005, 01:44 AM
Registered User
Join Date: Feb 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Performance Enhancements

I have a ASP.Net web application with SQL server 2000 as the back-end. The webserver and the db server are on the same server.
There are about 60 users accessing the application. The performance of the application becomes poor as time passes.
At the beginning of the say, the speed is ok but it declines with time. The memory usage seems to be quite high at times which could be one of the causes. But are there any other possible causes since there are times when memory usage is not much but still the performance is low. Please send in comments.

Old February 19th, 2005, 02:25 AM
Friend of Wrox
Join Date: Oct 2003
Posts: 463
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to madhukp

Here are some general methods to improve performance.

Method 1:
Stored procedures.
Are you using stored procedures or direct queries ? You can use stored procedures to improve the speed very much.

Method 2
You have to define appropriate indexes for tables. An index defines the order in which records are stored (either physically or logically). How to determine which column to index ? This will depend on the query you are using to fetch (especially the order by fields) and the way you want to show data.

Method 3
Horizontal splitting
Let a table contain very large number of records. But 90% of the time, users will be using only 10% of these records. Then it is better to split the table into two (one live table and one archive table). You can retain the 10% frequently accessed records in the live table and the 90% infrequently accessed records in an archive table. However, this will involve significant amount of script changes.

Vertical splitting
If the table is having large number of fields (more than 20), it is better to split the table into two or more. The most frequently used fields may be kept on one table and less frequently used fields can be pushed to other tables. For e.g. It may be collecting and storing about 50 details of an employee. But only 10 of them may be used frequently. The less frequently used fields (such as housename, home postal address etc. etc) can be put into another table and then relate these two tables.

Method 5
Better query formation
You can use efficient queries. For e.g.

Select <field list> from <table clause> Where <where clause>

is more efficient than

select * from <table clause> Where <where clause>

You can use join queries instead of sub queries to fetch field names corresponding to foreign keys. For e.g. consider the query.

Select employees.employee_name, (select countries.country_name from countries where country_id=employees.country_id) as employee_country from employees

The following query is much more efficient than this query to fetch a list of employees and their countries.

select employees.employee_name, countries.country_name as employee_country FROM (employees left join countries on employees.country_id=countries.country_id)

Method 6
Use of triggers

You can use triggers to do many business rules. For e.g. if you want to delete records from many tables in a cascaded manner, this can be very well done with the help of triggers. The normal method of first deleting records from the bottom-most table in the hierarchy and then from the table just above and finally from the top-most table is not very efficient.

Method 7.
Data shaping.

Whenever you are in need to display hierarchical records, it is better to use data shape object. You can search and findout what it is.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Upgrades and enhancements plb BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 4 January 29th, 2007 09:21 AM
Web Service Enhancements dilemma sabansingh C# 3 July 11th, 2006 03:07 AM
Enhancements to WEO? AGR All Other Wrox Books 0 April 22nd, 2004 10:37 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.