Asp Forum - Common issue - query response time <1173074752.152167.267100@s48g2000cws.googlegroups.com>

Parth

3/16/2007 10:22:00 AM

Common issue - query response time - jack

05-Mar-07 01:05:52

I am maintaining the application and database which is been developed
2 years ago. Initially the database response time was perfect. but as
an when the size of data increased the response time also increased.

The database tables are indexed in such a way which leaves the scope
of creating further indexes.

In this scenario what should be done to optimize the reponse time of
the query.

Thanks for replying me.

----------------------------------------

Hi Jack

The problem you mentioned is very common in any database centric application. Perhaps at initial stage volume and access estimation has not been done thoroughly for DB Tier.

If you feel the current response time of your TSQL Code or Code Block is not in acceptable range then you may dig following aspect of your database to get solution of your problem

This list is a bit long and I hope you must have already gone through few of the following, but since you have not mentioned much about your database structure, data volume and DB access mechanism hence I am trying to mention all possible aspects

#1 If not DB is too much normalized
#2 If unnecessary joins implemented in Queries
#3 If Star (*) is used in Select statement
#4 If Queries returning more Columns then it is using in Application
#5 If ?Views? are used that are composed by multiple Indexed Table
#6 If composite Key are there on tables that are queried.
#7 If lot of ?Cast ? or ?Convert? command used in Queries
#8 If not Queries are written poorly.
#9 If lot of Temporary table used in Queries
#10 If Temp DB size is OK
#11 If Fill factor on tables are OK
#12 If DB is up-to-date with Stat and Indexes
#13 If the DB comprised properly with File(s) and File Group(s).
#14 If lot of text data type Columns used in Tables that are comming in Queries.
#15 If result set is used as worktable in Queries
#16 If there lot of Cursor used in Procedures
#17 If lot of recursive Procedure/ Triggers used
#18 If there are any Blocking on Queries
#19 If there are any lock escalation on Queries
#20 If Deadlock arises on executing Queries
#21 If lot of Set commands are used
#22 If Extensive dynamic SQL is there in Queries/ Procedure
#23 If excess recompilations of stored procedures are there.
#24 If RAID is implemented then is it supports improved read mechanism.
#25 If SQL Server is properly Configured.

You can make some valuable analysis using SQL Server Profiler and Windows Performance Monitor. Using SET SHOWPLAN_ALL, SET SHOWPLAN_TEXT and SET STATICS COMMAND can also help you to analyze your slow running queries.

And yes; increasing number of indexes on tables always does not help in Query Optimization, it may go adversely also. Suppose there are lot of insertions and deletion on a base table that is heavily index on its columns then your query response will go down due to broken indexes and out of date statistics and SQL Query Optimizer will have a difficult time to find a better Execution Plan. Insertion in heavily indexed table is also slow due to obvious reason. So before going for further indexes you should reassess and re-analyze the application scenario and problem area.

Thanks and good luck

-Parth
ghoshps@gmail.com

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.egghe...

microsoft.public.sqlserver.programming

Common issue - query response time <1173074752.152167.267100@s48g2000cws.googlegroups.com>

Parth

x Login to ForumsZone