[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

"Time-out expired" Error


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
fskilnik
Asked:
fskilnik
  • 6
  • 4
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Do you have an index on 'FieldName'

Also use the TSQl syntax
"Where TableName.FieldName <> 100"  
0
 
ExpertAdminCommented:
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
 
fskilnikAuthor Commented:

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
fskilnikAuthor Commented:

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
 
ExpertAdminCommented:
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
 
fskilnikAuthor Commented:
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
 
ExpertAdminCommented:
Yes, that is a decent use of the view. I think you are on the right track.

M@
0
 
fskilnikAuthor Commented:

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
 
fskilnikAuthor Commented:

(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
 
ExpertAdminCommented:
Sounds good. Good luck.

M@
0
 
fskilnikAuthor Commented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now