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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hi RekhaShah,

You could try to set the timeout period. It is set to 30 seconds as a default.
It can be done like this

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.

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.