Solved

Using SQL Management Studio, receiving Timeout Expired message

Posted on 2007-12-01
6
959 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 109
Selection from table2 where criteria for table1 10 49
Strange msg in the SSMS pane 13 58
Need help how to find where my error is in UFD 6 40
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 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