Saturday, March 24, 2012

Problem based on following "Scotts todo list" guidelines

Hello

Since I started using ajax I based all my web programming on "Scott's todo list" Video.

That means everything I do is AJAX, CSS and most important of all, ObjectDataSource based. Everything went fine until I decide to use great amounts of data. For example I have now a single table on sql 2005 database with a couple of indexes, the table have more tha 4 million records and growing up, the database runs on a 2 x dual xeon processors, 4GB ram and 10.000 rpm seagate scsi hdd. (I described the machine just for you understand that the problem i'm going to talk is not a preformance problem)

Tha HDD keeps unfragmeted the most of time. But there are much information and the query I did takes more than 30 seconds (I want to call the attention that my query really needs to take more than 30seconds, that's a fact and no matter why). To understand this, when I execute the query the CPU load not pass from 5% to 8%. So I think it is because no matter how fast the hdd is, it is slow to return the data.

From time to time, the user changes parameters in the web page, like incremeting the data range, include specific record types or specific clients, vendors and so incrementing the amount of records to be returned

When it is needed to return much rows, (it is unspecifically the amount of records to be returned,) it takes more than 30 seconds. so from time to time I get the "The timeout period elapsed prior to completion of the operation" error and just at 30 seconds the query stops and I have no results.

Looking for a solution on the internet I conclude the following:

1. It is a very very common error in web sites using .aspx

2. In my case, I do not know others, I use OBJECTDATASOURCE, as indicated on Scott's ToDo List, (where I CAN NOT define a timeout parameter for the database connection), instead of SQLDATASOURCE (where one can define a CommandTimeout parameter)

So How can I extend the TimeOut parameter when I use ObjectDataSource?

As explained above, I do not want answers like, "you need to improve your sql database, make tunning, or such common answers". What I really want to know is HOW TO CHANGE THE TIMEOUT parameter for the ObjectDataSource. That's all. Because there are times when you need to execute systems procedures using for example xp_shell, or whatever that makes your query take more than 30 seconds.

It is possible, Or I need to switch to SqlDataSource?

By the way I changed the timeout value on the web.config at the connection string, but no matter which value I put it always stops at 30 seconds.

Helcjo

Firstly, I'm surprised that you are taking 30+ seconds to run a query!! I know you said you don't want answers like this... and I'm sorry.. but... this sounds huge to me! There have GOT to be ways to reduce this time down.

Secondly, on to what you really want to know... how to set the timeout. Well... you're using an objectdatasource... which simply calls whatever method you specify to return your results. So it sounds like that method must create a SqlConnection then a SqlCommand and execute something. Can you not change the way that this connection behaves so as not to timeout so quickly?


Hello

I guess you will understand better my problem if you see and follow the steps indicated in "SCOTT'S TODO LIST" sample. As you can see, he just drag and drop the objectdatasource to the web page and connects it to the dataset he previously added to the project.

Now imagine millions of records in his sample, in only one table, but with more fields than the Scott's sample.

For your understanding, on why can take more than 30secs a query to be returned, imagine using xp_cmdshell to perform several tasks that take some minutes to be completed. So not necesarily you need to expect for data records from your database.

I'm not sure about what you are saying in the phrase: "Can you not change the way that this connection behaves so as not to timeout so quickly?", could you please explain me


Hello

I mentioned on my post, that after some research on the internet, I found this was a very common problem for aspx. web pages, and if anyone of you try to look in google, yahoo, msn search, etc. then search this error message

"The timeout period elapsed prior to completion of the operation"

, so you will find several amounts of websites with this error. I guess there are several databases which takes more than 30 secs to respond.


Hello Again

I still have exactly the same problem. Just to be known, I used SQL Express 2005 and after no help on this subject, I tried MySQL, added several indexes for the table, just doing what you indicate: looking on how to improve the query, even more added 8GB to the dual xeon machine, but nothing, that's a fact all the queries run much faster, but not yet to outperform the 30secs barrier.

Of course not all the queries take more than 30 secs. So I'm going to ilustrate my problem using real queries:

For example:

select ip, sum(cltbytes), sum(srvbytes), count(*) from log WHERE ip like '192%' and date >= '20070901' and date <= '20070901'' GROUP BY IP, ORDER BY IP

this real simple query which represents only ONE day data, takes no more than 2 secs resulting from almost 1 million records in just like 50 rows (50 different ips)

As you can see my table have only 5 columns: ip varchar(16), date datetime, cltbytes int, srvbytes int, host varchar(72) and I have an index by IP and other by date

I can not imagine how to make more indexes in order to improve the results, also I can noit imagine how to make a better query that he above one.

The problem starts when I increase the date range, for example:

select ip, sum(cltbytes), sum(srvbytes), count(*) from log WHERE ip like '192%' and date >= '20070901' and date <= '20070916'' GROUP BY IP, ORDER BY IP

as you can see it should obtain data from 15 days, as we can get a sum from almost 15 million records, this query takes more than 25 seconds, but still works because is under the 30 secs barrier.

Of course to get the results for more than 16 days It'S IMPOSSIBLE.

Why it takes more than 30 secs, in two diffrerent databases, ms-sql express 2005 and mysql, I do not know, but I have some idea: The server's HARD DISK can not handle so much data in a more eficient way if you are saving almost a million records per day, Of course I have a fast and powerful hdd a seagate cheetah 73GB 15000 RPM using SCSI 320 , if you know more fastest HDDs I would like to know them.

I would like to see you trying such table in you machine with just 15 million records and running the above query in order to know if the problems is my machine, or my vs2005 or my iis6 or my win 2003. in fact exactly the same problem happens in my develpment machine and in the production server.

A week ago I found wht seems the solution to my problem, an article from your website:http://www.asp.net/learn/data-access/tutorial-72-cs.aspx I followed the instructions, and yes, finally some light at the end of the tunnel, I can now change the CommandTimeOut, BUT it doesn't work with the simple Scott's ToDo List Example.

In order to make it work, I need to manually programmatically type the code, but I do not want to do that. I REALLY WANT THE SCOTT'S TODO sample working with my table. But now, at last, I know where the problem is. the problem is at the autogenerated code for the dataset when adding datasets at the app_code folder in my application.

So, I have now another idea, how can I hack vs2005 in order to change the default timeout value of 30 seconds when it generates on the fly code for the DATASET? and why somebody at microsft decided that 30 secs is enough for all the cases instead of leaving it forever or at least let the user pass the parameter when issuing the databind() for the ObjectDataSource?

thanks

Helcjo

No comments:

Post a Comment