Selecting Query Takes so much time

  • 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

  • Please see Gail's post about how to post performance problems here. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]

    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/

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply