• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • 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.

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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