Hey Guys,

I am getting a "timeout expired" error in sql server every time I try to update, delete or insert more than 100,000 rows into a table. The table that I am trying to work on has about a half million records in it. I am running the queries straight from the enterprise manager console. (The queries are also failing when I run them from my vb app). Any Idea what I am doing wrong here?

Here is what I have tried:
- Put the database in simple mode so that the logs will not compound
- In query analyzer, under Tools -> options -> Connection Properties, I set the query time out to 0.

I'm kind of bummed because sql server was hyped up to be this great database that could handle heavy loads, and so far Access is kicking the crap out of it.

Any assistance would be greatly appreciated.

Ryan

nikotromus

__________________
nikotromus

Not sure if this will work, but has for me in the past. In EM right click on the server and choose properties. Click on the Connectins tab. There you can set the Query Tiimeout to 0.

Hey jbenson001,

That was one of the first things I tried. It didn't yeild any results for me. It's pretty frustrating. I am doing this as a side project at night. I work for one of the worlds biggest corporations during the day. I talked to a sql server dba there, and I followed all of his advice. He is now out of ideas for me. I switched from microsoft Access to sql server thinking that I would get a big boost in performance. It has been a huge let down so far.

nikotromus

Well SQL server is a much better tool than access. You can't even compare the 2. I am suprised you are getting a timeout error if you have set it to 0.
Let me ask. Are you using tables from different servers, maybe one server's timeout is 0 while the other still has one set? I have run into that issue as well.

Let me know what you come up with. I will try to think of more possibilites

Good luck...
Jim

Hi Jim,

I am running SQL server locally from my machine (2.4 gig processor, .5 gigs or RAM). I am writing queries both from enterprise manager and a visual basic application. It gives the same error from either instance. I am working on my machine locally and sending the code to a business in Alabama. My boss there has his own network set up with his own instance of SQL server. He gets the exact same errors that I get when he executes deletes, inserts or update statements on tables with more than a half million records. Thats why I was thinking that it must be a common error with a simple solution. Apparently I am wrong.

Thanks for the reply. I appreciate the effort.

nikotromus

Ryan,

Are there any column indexes or triggers on the table?

Scott

Scott,

Yes. I have the unique key indexed.

Ryan


nikotromus

OK, but any others, like foreign keys, unique constraints, etc? The reason i am asking is because it seems to me that there is some process that is affecting the CRUD (Create, Read, Update, Delete) actions on the table.

Hey Scott,

That is the only index. It is also the primary key.


nikotromus

Scott,

You got me thinking about the indexes on the table, and the fact that maybe the table might be corrupt for some reason. I rebuilt the table from scratch, and this time included an indexed sequnece number. I loaded the table with a million records, and tried updates and deletes. It is still giving me the timeout error.

nikotromus