Please help me - Timeout error

[joozh]

Hi,

I have a view which it self is based on a number of views (using joins)....

When I try to return all records I get a Timeout message...Is there any solution? Is there "something/anything" that I can do to somehow increase the Time-out or the setting of some parameter so taht I do not get this message and get the results?

I'll appreciate your help.

[rmiao]

Where do you run the query? In query analyzer?

[Rawhide]

You best bet is to optimize your queries in your views. Make sure that your tables are properly indexed and that you're joining on key fields (preferably clustered int fields).

Also, use the nolock optimizer hint in all of your select queries so that it's not waiting for locks to be released.

[joozh]

Thanks for the replies.

Rawhide, can you kindly give an example of how to use the 'nolock optimizer'.

Sorry if this is a silly question but I've never used it...

Do I specify in XYZ clause of my SQL query???

Thanks

[rmiao]

select * from table with (nolock)

[Rawhide]

Or if you're aliasing your tables:

select * from table As T with (nolock)


Using in a join expression:

select *
from table As T with (nolock)
Inner Join othertable As O with(nolock) On O.FieldID = T.FieldID


You can express it in 3 ways and all are the same:

with (nolock)
with(nolock)
(nolock)

So the query can be:

select * from table with (nolock)
select * from table with(nolock)
select * from table (nolock)

[joozh]

Many thanks both.

Will give it a try and see if it improves the performance - hopefully it will

[ponyryd]

I know this is late to the game, but for future readers.

Please be informed that by using (nolock) you might read the not-necessarily-valid uncommitted modifications of a running transaction. If that transaction should fail and rollback, you query resultset will be inaccurate. Something to consider before using this parm.

[Rawhide]

Thanks, I should remember to point that out when I suggest it. You wouldn't want to do this with something like banking information.