?
Solved

Using SQL Management Studio, receiving Timeout Expired message

Posted on 2007-12-01
6
Medium Priority
?
979 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 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