SQL 2005 Express & Connections

[BBousman]

Scenario:
We have software wrote in VB.NET 2005 and we use SQLOLEDB connections to read from and write to the SQL Express database. Most of the servers we have are around 3.0GHz processors, 1GB of RAM and about 70GB of hard drive space.

Problem:
On the servers we have a 3rd party software that checks for files in a folder every 4 seconds. For every file in the folder, an instance of our software is run (so we could have 10 or so of these running at once). The program only takes a couple seconds to run but it causes the server to lag horribly and the SQL Server process in the Task Manager jumps up to over 200,000 K and it's constantly in this state.

On the client side, when the end user hits the "Process" or one of the other 5 buttons, it makes more calls to the database tying it up even more. The end users have to wait anywhere from a couple seconds to a couple minutes for their item to load.

Questions:
Would we be better off using/buying SQL Standard and putting it on our servers? Is there a speed benefit to it over SQL Express?

Should we upgrade our servers to be beefier? We're in the process of that now but how high should we go?

Is there a better/faster connection method besides SQLOLEDB?

Final Thoughts:
I'm currently working to change this from a Console Application to an actual Windows Application to hopefully reduce the load on the servers but if anyone has any sugguestions I'd greatly appreciate it. Thanks in advance!

 

[gmmastros]

>. Is there a better/faster connection method besides SQLOLEDB?

http://connectionstrings.com/?carrier=sqlserver2005

Scroll to: SQL Native Client OLE DB Provider

This may be a little faster, but probably not alot.

There is no bandwidth throttling with SQL Express, so the number of concurrent connections is irrelevant.

>> The program only takes a couple seconds to run but it causes the server to lag horribly

This is probably your problem. I would try to optimize the performance (if I were you).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BBousman]

Thanks! I'll look into changing our connections over to this to see if it helps with speed. I think we use this type of connection for one of our ASP pages and it seems to run pretty decent.

Our servers are pretty old and I'm trying to get management to upgrade them but it's a slow process.

Would/could there be any network issues involved? Like, could our customers slow networks play into it not being able to connect and execute the SQL fast enough? I would think that could be a possibility.....

 

[gmmastros]

>> network issues

I can't answer that. If you are passing small amounts of data between client and server, then an old 56K modem would be sufficient. However, if you are passing large amounts of data, then it's important to have a stable (and fast) network connection.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom