September 17, 2012 at 12:17 pm
This is structure of the Table.
CREATE TABLE [dbo].[recent_items](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](50) NULL,
[sid_ids] [varchar](1500) NULL,
[p_ids] [varchar](1500) NULL,
[contact_ids] [varchar](1500) NULL,
[client_ids] [varchar](1500) NULL,
[access_date_time] [varchar](max) NULL
)
In above table recent_items only one record will be exists for one user.
select * from recent_items where user_name='suresh' order by 1 desc
When i run above query in sql server 2005, some time it is taking less than 1 second to execute.
When i run same query , it will taking 4 :30 Minutes to execute.
And i am getting time out exception error in my application.
Because we set sql command time out =120 seconds.
Why the above sql query taking 4:30 Minutes.
How to Improves the performance of the query without changing the Sql Command Time out property.
Thanks,
Suresh
September 17, 2012 at 12:34 pm
Please see Gail's post about how to post performance problems here. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D
I would make two recommendations about your query. First you should NEVER use select * in a production environment. Secondly you should use column names in your order by instead of ordinal position. Of course as you say there is only 1 row that meets the where condition so why do you even bother to order a result set of 1?
I also have to question some of your datatypes. You have a column named access_date_time which indicates it will a datetime value but the datatype is varchar(max). If this is really date information you should the appropriate datatype.
Also, you have a number of columns like sid_ids. The names indicate this is holding a delimited list of values. This type of thing should be normalized instead crammed into a single column. It makes it a lot harder to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 1:22 pm
I second all of Sean's comments.
In addition, I note that you are referencing a column in your example which does not even exist in the table (ri_user_name).
I suggest that you fire up Profiler to verify that your application is issuing exactly the same query. You might also get some additional pointers as to where the bottleneck is.
September 18, 2012 at 5:41 am
If you're trying to only get the latest value, you might want to try some of the different methods I outlined in this article on Simple-Talk[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply