Solved

Using SQL Management Studio, receiving Timeout Expired message

Posted on 2007-12-01
6
950 Views
Last Modified: 2010-04-21
When I use a simple select statement to query, records return with-in seconds.  But when I add the WHERE clause to select the correct records the system said waiting on response for the server & and then after about 30 seconds I get a timeout expired & message.  The query is against a new archive BD that will not see much action.  I tried to change the settings in Tools - Options - Designers ... but no effect (still times out in 30+- seconds).  Because the data will not be used much, I can extend the time-out limit or fix the root cause which I think is related to the keys and indexing.  The field that I need to filter on is not a Key/Index.  

Any ideas?

Thanks
MC
0
Comment
Question by:mcain_bba
6 Comments
 

Expert Comment

by:iscontact
ID: 20388350
Create an index on the field you are filtering.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 20388372
>>Tools - Options - Designers<<
That is the Connection timeout.  You want the Execution timeout in Tools | Options | Query Execution, which by default is 0 (Infinite) so either it was changed or you have some other problem.
0
 

Author Comment

by:mcain_bba
ID: 20388803
Can you talk me through how to create an index?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Expert Comment

by:Yveau
ID: 20388934
try this:

http://msdn2.microsoft.com/en-us/library/ms188783.aspx

If your specific query uses a range lookup (where ID > 1000) in stead of a pick (where ID = 1000) you should gain even more by making the index clustered. But since you can have only one clustered index per table, that might not be possible. If you can, try it.

hope his helps ...
-- nonclustered index:
create index ixMyIndex
on MyTable (column1, column2, ...)
 
 
-- clustered index:
create clustered index ixMyIndex
on MyTable (column1, column2, ...)
 
--

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20389072
It really depends what type of index you are trying to create.  For example, the following will create a non-clustered index on YourColumnName in YourTableName:
CREATE INDEX IX_YourTableName_YourColumnName ON YourTableName (YourColumnName)
0
 

Author Closing Comment

by:mcain_bba
ID: 31412165
There was a problem ... All of the indexes had to be recreated ... Once that was done, all worked as it should.

Thanks
MC
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question