May 18, 2005 at 10:54 pm
Hello all,
My SQL statement seems taking longer time to run when more people are trying to access the database for data. How can the performance be increased?
Some more background, hope it helps:
I am running SQL server 2000, the statement is just a simple SELECT statement, I have constructed an asp search page to access the data and whenever people search the database repeatedly, the database will become very slow to respond to other request.
Thanks all!!
May 19, 2005 at 6:11 am
Sounds like blocking, run sp_who2 to see when you notice slow performance.
May 19, 2005 at 8:31 am
Thanks for the reply Allen,
Do you think changing the SQL statement to stored procedure would help?
May 19, 2005 at 9:36 am
How simple is the select statement and are there indexes available to support it (especially on the columns used in the where clause or in joins)
Putting the statement in an SP could help with the execution plan being cached but properly optomizing the query would yield much better results quicker (i'm assuming that it's not optimal here which may not be true).
If you are able to post the select query I'm sure one of us could have a look and make recommendations for improvement or otherwise.
May 19, 2005 at 12:32 pm
Okee, it seems that the long execution time has nothing to do with the search function, it might have something to do with my new table and code that I didn't mention in the first place:
I created a new table to keep track of all the messages that sent to customers daily. Here are the procedures:
- First I query the database to get the last message number and the subject of last message sent. (SELECT * FROM table_nameA)
- In the asp page, I retrieve the last message number, increment it and get the next available message to be sent. The messages that need to be sent are previously store in an array.
- If the message has sent to the same customer before, I update the record in table_nameB (UPDATE table_nameB SET...), otherwise the record is inserted into table_nameB (INSERT INTO table_nameB...)
table_nameA has three columns: ID, Message title and Message number
table_nameB has five columns: ID, customer first name, customer last name, message title, date sent.
When I checked Managed Indexes of table_nameA, I found that under Existing Indexes section, Index is set to PK_table_nameA, Clustered is set to YES, and Columns is set to ID, which is my primary key. Is that mean my table is indexed?
May 19, 2005 at 7:49 pm
it sounds like the search is on Message number, which is not indexed. Clustered index is placed on PK by default, but if your select is like:
SELECT * FROM table_nameA
WHERE Message number = 'BLAH BLAH'
then you need an index on Message number as well. Depending on what you do later (order by ID or not) you can choose which column to place clustered index on (can pnly have one per table).
table_nameB should probably not have indexes, since you only insert and update and not search it.
May 20, 2005 at 10:42 am
It's considered a best practice to write your select statements with explicit field names instead of star (*). There are some "hints" available that may help, such as:
NOLOCK: Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
It could be that the database users have locks on the table because they are updating that interferes with your read operation.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
May 20, 2005 at 12:06 pm
Thank you for all your help!
Russell, I used the 'nolock' in the SELECT statement and now the database is back to its optimal execution time. Thanks for the tip!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply