• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Stored procedure timed out after importing dta

Hi Experts,
I really hope someone can help me.
I have a stored procedure in SQL server 2005 that imports data from a database on a different server. It can import about 10000 or more records at a time.
My desk top app calls this stored procedure giving it the date range to select the data. It works fine.
After it imports data, user can open a form to display this data on a grid. I call another stored procedure to get this data. but it times out. If I quit the app, goto sql server,
run     DBCC DBREINDEX(Edits_data, '', 80)
          UPDATE STATISTICS Edits_data
 and then go back to the application, it works fine.
Does that mean, my index and Statistics are not being updated? I have set "Auto Update Statistics and Auto update index True.

User can run a few batches at a time and import may be 30,000 records or more! I have one clustered index on primary key(this is an nvarchar field) and 3 other non-clustered index.

How do I solve this problem? It is stopping me from releasing the application. Any help will be appreciated.
1 Solution
HainKurtSr. System AnalystCommented:
can you post the related codes for these sps..

Hi RekhaShah,

On a large table (>500 rows), statistics are automatically updated  when 500 +20%  of rows are modified. So, you your table contains 100,000 records, a 10,000 record insert will not update statistics. If possible, add code to update statistics to you procedure.
Hi RekhaShah,

You could try to set the timeout period. It is set to 30 seconds as a default.
It can be done like thishttp://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectiontimeout.aspx

I would try with Connection Timeout=0, which means "never"!
10000 rows is nothing, we import 2 million rows a day. But it took a bit of work to design.
Please do not leave your timeout at zero, as it will come back to hunt you some day.

If you are rebuilding indexes and stats at a regular basis, you may be hit by a database growth?
If this takes just over 30 seconds, your normal sproc will not be able to finish in time. But your dbreindex will need space for an additional copy of your biggest table, and this does not time out.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

RekhaShahAuthor Commented:
The table is not that big yet since it is still under development - most of the time I delete all the records. But, if i run the import and then without exiting the app, if I try to open a new form which will display, it times out. BUT, If i REINDEX and Update Stastics, IT RUNS WITHOUT ANY PROBLEM. So the actual problem may not be the real "timeout".
Connection time out is not an issue since I am the only one who is connected to this db.

It used to run fine in the beginning of the app development.
I wonder if following would affect the performance
I added

1. some more data verifications
2. added one more storedproc to check data
3. Keep deleting the data from this table
Like Rimvis suggested, I may add update state to my stored procedure. I will try it this evening.
Any suggestions
Thank you guys
If you delete all rows, and then import 10000 rows, the server needs to update all its statistics, because it has changed more than 20%. This takes time, maybe more than 30 seconds.

May I recommend sp_whoisactive, so that you can see what the SQL Server is doing whilst your application is waiting for data?

You can get it here: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx

Your application may be blocking itself, if you have a BEGIN TRANSACTION too much.
Anthony PerkinsCommented:
First of all doing UPDATE STATISTICS after DBCC DBREINDEX is pointless.  Reindexing updates stats.
Second DBCC DBREINDEX is deprecated use ALTER INDEX instead.
Lastly and to answer your question, the problem lies in your Stored Procedure.  Unfortunately, until you post the contents, all we can do is make wild guesses as to the solution.
RekhaShahAuthor Commented:
I call a stored procedure that updates the the stats (no reindexing)  when the user leaves the form that allows him/her to import the data. This way, when user is done importing data- may be a few batches at a time, I only update stats once.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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