Query Timeout??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Query Timeout???

Author

Topic

pantinosm
Starting Member

9 Posts

Posted - 2007-07-08 : 11:34:38

I try to run a sql query locally and iget the results, but when i try remotely from my website, after 10 seconds i get a query timeout. The query timeout is set to 0, i do not know what am i doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 11:50:43

Where is the query timeout set to 10 seconds?

Peter Larsson
Helsingborg, Sweden

pantinosm
Starting Member

9 Posts

Posted - 2007-07-08 : 11:54:40

it's not set to 10secs, i just get a timeout error after around 10secs.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 12:47:49

The default time-out setting for COMMANDS is 30 seconds, and the default timeout setting for CONNECTION is 10 seconds.
So I ask you again, where is the timesout set to 10 seconds?
Do you have proper access to the database from the web application?

Please post your connection string here and after that, have a look at www.connectionstring.com

Peter Larsson
Helsingborg, Sweden

pantinosm
Starting Member

9 Posts

Posted - 2007-07-08 : 13:06:35

I don't know how to set the timeout this is what i am asking someone to help me. My access is proper so is my connection string. My db has over a million records, so i think this is the main problem.
My conn string is Driver={SQL Native Client};Server=PantinosDeskPC\SQLEXPRESS;Database=ibeba;Uid=sa;Pwd=xxx;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 14:55:21

Do you really use SA account from web page to access the database?
Is the database set to Mixed mode or only authentication mode?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-08 : 14:56:39

If you run a simple command from web page, such as

SELECT TOP 1 * FROM <some table name here>

what do you get?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-09 : 04:32:54

See if these help
http://vyaskn.tripod.com/watch_your_timeouts.htm
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm

Madhivanan

Failing to plan is Planning to fail

pantinosm
Starting Member

9 Posts

Posted - 2007-07-09 : 14:53:54

The problem is that i have about a million records, that is why i get the timeout. How can i set the value higher?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-09 : 16:34:56

quote:
Originally posted by pantinosm

The problem is that i have about a million records, that is why i get the timeout. How can i set the value higher?

No, the problem is that you have people here trying to help you, but you're ignoring the questions they ask (Peso) or the suggestions they provide (madhi).

Try reading the two links madhi provided, and answering the two questions that Peso asked, before posting any other questions or cries for help.

pantinosm
Starting Member

9 Posts

Posted - 2007-07-10 : 01:51:45

Yes, for now i use sa account from web page, and my authentication mode is mixed mode. When i try the select statement you said i don't get any errors. The errors come up when i try >5000 lets say records.
I am sorry for not answering your questions earlier.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-10 : 02:20:26

I would look a the table. Do you have any useful indexes?

Peter Larsson
Helsingborg, Sweden

pantinosm
Starting Member

9 Posts

Posted - 2007-07-10 : 13:45:38

My table structure is this.

CREATE TABLE [dbo].[tblLinks](
[ID] [int] IDENTITY(1,1) NOT NULL,
[URL] [nvarchar](255) NOT NULL,
[Title] [nvarchar](255) NULL,
[Keywords] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[ModifiedDate] [smalldatetime] NOT NULL,
[counter] [int] NULL
) ON [PRIMARY]

I don't have indexes.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-10 : 15:24:35

pantinosm,

Ok. Can you post the actual query you're running that's timing out?

If you have a million records and you're running a query without a WHERE clause, it could explain the timeout easily.

pantinosm
Starting Member

9 Posts

Posted - 2007-07-11 : 01:43:35

The whole structure is the following.

strSQL = "Select TOP 5000 * from tbllinks where (title like '%" & ArraySTR(0) & "%'"

for x = 0 to Wordcount - 1
if lcase(ArraySTR(x)) <> "and" then
if x > 0 then
strSQL = StrSQL & " AND title like '%" & ArraySTR(x) & "%'"
end if
end if
next

strSQL = StrSQL & ") OR (Description like '%" & ArraySTR(0) & "%'"
for x = 0 to Wordcount - 1
if lcase(ArraySTR(x)) <> "and" then
if x > 0 then
strSQL = StrSQL & " AND Description like '%" & ArraySTR(x) & "%'"
end if
end if
next

strSQL = StrSQL & ") OR (URL like '%" & ArraySTR(0) & "%'"
for x = 0 to Wordcount - 1
if lcase(ArraySTR(x)) <> "and" then
if x > 0 then
strSQL = StrSQL & " AND URL like '%" & ArraySTR(x) & "%'"
end if
end if
next

strSQL = StrSQL & ");"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 02:39:38

Did you try the suggestion made for you here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86091

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-11 : 02:41:08

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86091

Peter Larsson
Helsingborg, Sweden

pantinosm
Starting Member

9 Posts

Posted - 2007-07-11 : 07:58:56

I will try that thank you.