Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Selecting Query Takes so much time Expand / Collapse
Posted Monday, September 17, 2012 12:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 60, Visits: 337
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.

Post #1360389
Posted Monday, September 17, 2012 12:34 PM



Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 14,970, Visits: 14,879
Please see Gail's post about how to post performance problems here.

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 for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1360398
Posted Monday, September 17, 2012 1:22 PM



Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 6,318, Visits: 15,432
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.

Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

I had some words with my wife, and she had some paragraphs with me.
Post #1360424
Posted Tuesday, September 18, 2012 5:41 AM



Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 15,976, Visits: 30,338
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.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1360704
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse