Solved

"Time-out expired"  Error

Posted on 2006-07-06
11
210 Views
Last Modified: 2010-05-18

Hi there!

During the execution of an SQL Server 2005 View (locally) I got this error message.  When I put a restriction like

"Where TableName.FieldName !> 100"  

everything works fine.

01. How can I (can I?) alter the time-limitation for running scripts, please?

02. Considering your (expert) experience, what value is still "not big enough" considering I intend to run all this at the Server, fired (and populated)  by ASP.NET oriented website (nothing local) ?

Thanks,
fskilnik.


0
Comment
Question by:fskilnik
  • 6
  • 4
11 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
Comment Utility
Do you have an index on 'FieldName'

Also use the TSQl syntax
"Where TableName.FieldName <> 100"  
0
 
LVL 7

Expert Comment

by:ExpertAdmin
Comment Utility
The first solution should be (as aneeshattingal suggested) improving efficiency. With web applications anything over 6 seconds is a site killer and the default timeout should be much higher than that. So if it is timing out on you, the best thing to do is make it more efficient.

If you could post your view code here we may be able to suggest ways of improving execution time.

M@
0
 

Author Comment

by:fskilnik
Comment Utility

Thanks, Aneesh and M@ !

Aneesh: I will read about "index" at my books now. (I am developing my first SQL Server database...)

M@:  Sure!  I would be glad if you (both!) could tell me about what should be done different...  Here it goes (below) !

Thanks a lot,
Fábio.

View Code:

SELECT DISTINCT
                      TOP (100) PERCENT Gen.StockID, Gen.EqtyTransID, Gen.TransDate, dbo.LastNegDate(Gen.StockID) AS LastNegDate, Gen.NegAccumSum,  Gen.NegPreviousAccum, dbo.LastPositDate(Gen.StockID) AS LastPositDate, Gen.PositAccumSum, Gen.PositPreviousAccum,                      
                      dbo.NegAccumQtyShares(dbo.LastNegDate(Gen.StockID), Gen.StockID) AS FinalAccumNegShares,
                      dbo.PositAccumQtyShares(dbo.LastPositDate(Gen.StockID), Gen.StockID) AS FinalAccumPositShares
FROM     dbo.EqtyTransactionsGeneral AS Gen RIGHT OUTER JOIN
                      dbo.EqtyTransactions_auxNeg AS Neg ON Gen.StockID = Neg.StockID RIGHT OUTER JOIN
                      dbo.EqtyTransactions_auxPosit AS Posit ON Gen.StockID = Posit.StockID
ORDER BY Gen.StockID, Gen.TransDate


OBS.:  dbo.NegAccumQtyShares and dbo.PositAccumQtyShares  are functions.
0
 

Author Comment

by:fskilnik
Comment Utility

Important: these functions are (I guess) the "time-consuming creatures"... as shown below:

CREATE FUNCTION [dbo].[NegAccumQtyShares](@lastInclDate datetime, @StockID int)
RETURNS int
AS
BEGIN
RETURN (SELECT Sum(SgnQtyShares) FROM dbo.EqtyTransactions_aux
Where (SgnQtyShares <0) AND (TransDate !> @lastInclDate) AND (StockID = @StockID))
END

The Posit one is almost identical (sure).

Thanks!
Fábio.
0
 
LVL 7

Accepted Solution

by:
ExpertAdmin earned 300 total points
Comment Utility
OK...

A couple of things. RIGHT OUTER JOIN is a real, real bad thing unless it is absolutely required. What this tells it is give me a list of every record from both tables. If this is the desired action, then it is OK, but if not it can lead to pretty heavy server loading.

Indexing will probably help. These appear to be transactional tables which typically have a lot of row.

Keep in mind that a view is a way of pulling data together to make it more usable and will typically have some criteria applied against it when a SELECT is run. The speed of the view itself is not as important as the speed that a SELECT against it returns. That is because a lot of the time that it is taking to run the view is actually taken up by the development tool (for example, Enterprise Manager) to load that many records in memory and display them.

A better way to test the view's speed is to do something like this:

SELECT COUNT(*) FROM MyView

This will return the number of records but will not tie up the overhead in trying to display them.

Indexes should be added for the following columns:

EqtyTransactionsGeneral.StockID
EqtyTransactions_auxNeg.StockID
EqtyTransactions_auxPosit.StockID
EqtyTransactionsGeneral.TransDate

Also, any columns referenced in a WHERE clause will benefit from indexing.

Before you just add indexes to everything though, you need to understand that indexes are not free. They speed up SELECT statements, but they slow down INSERTS since the new records have to be added to the index when they are created. So if these tables are bulk updated , adding indexes will have a negative impact on that.

M@



0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:fskilnik
Comment Utility
Thanks for the very interesting and complete answer, M@ !

I will re-consider the whole structure to try to avoid the RIGHT OUTER JOIN and use the usual JOIN, for sure.


>> These appear to be transactional tables which typically have a lot of row.
Exactly.

>> Keep in mind that a view is a way of pulling data together to make it more usable and will typically have some criteria applied against it when a SELECT is run.

I am using it to be able to "chain" it, I mean: I create a View then I create another one, "on top of it" , calculating for instance the SUM of all values of a field that was created (by formula) in the previous View, etc etc".  This is the proper way to do this, isn´t it?  I mean, it seems to me that I am doing something analogous to query some queries in Access. Is "my way" (explained) the "normal" way in SQL Server to do that ?

>> A better way to test the view's speed is to do something like this:  SELECT COUNT(*) FROM MyView

UAU!  Very nice and I would not imagine that most of the time would be to "show" the "answers", not calculating them!!

>> About indexes:  thanks for the proper places to put them, thanks for the warning. I will read about them and try to implement them accordingly!  

(Our "work load" is much more in SELECTing things than INSERTing things... good!  But I will try to make it as clean and clear as possible, sure)

Thanks a lot for your quick and throughout assistance, M@.

Fábio.



0
 
LVL 7

Expert Comment

by:ExpertAdmin
Comment Utility
Yes, that is a decent use of the view. I think you are on the right track.

M@
0
 

Author Comment

by:fskilnik
Comment Utility

Great, M@ !  

I will do the following: increase 300 to 400 points and split it to M@ ("Accepted", 300 pts) and Aneesh ("Assisted", 100 pts.).  I guess it´s fair and I hope both of you like it.

Thanks a lot,
Fábio.
 
0
 

Author Comment

by:fskilnik
Comment Utility

(I´m taking into account the fact that Aneesh helped me in another question after the question was closed, therefore I could not reward him then.)
0
 
LVL 7

Expert Comment

by:ExpertAdmin
Comment Utility
Sounds good. Good luck.

M@
0
 

Author Comment

by:fskilnik
Comment Utility
Hi M@ !

Would you mind checking my "next phase database project question" below, please?

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21918495.html

Your expertise in the matter would be most welcomed, if you have little time to have a look!


 Thanks a lot,

 Fábio.
 
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now