Slow Data Retrieval

Good Day Experts

I have a situation where if a C++ client, a MFC App using wizard generated code for an attributed  ATL OLEDB Consumer to call a stored procedure),periodically takes a "long" time to get the data from the database. I am not sure if it is the server that is not responding/The connection that is broken (and has to reconnect) or a problem on the PC/Application.
Long being between 4 to 6 seconds of no response from the app. After that it is almost instant.

The symptoms show after the screen saver has activated, but not exclusively. When the symptoms do show, it is after a period of inactivity (the user has been away from the machine)

Machine has a static IP address and is on a wired network,
All power saving options are turned off.
Netbios over TCP/IP is enabled under WINS.

The database was recently upgraded from SQL Express 2005 to SQL Express 2008.
I do not recall these symptoms while running 2005.
The server has 3GB of RAM. (SQL Express will only use 1GB) and plenty of free hard disk space.

I need some help in resolving what can be causing this as the delay is unacceptable in the production environment, resulting in the perception that clients are left waiting unnecessarily

TCSS
LVL 1
tcssAsked:
Who is Participating?

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

x
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.

alb66Commented:
May be you have AutoClose property in database set to true?

"Setting Database Options" at
http://msdn.microsoft.com/en-us/library/ms190249.aspx
0
tcssAuthor Commented:
Additions Comment/ Question

Do I need to install Microsoft SQL Server 2008 Native Client on the PCs where the client app runns?

tcss
0
tcssAuthor Commented:
alb66,
You have pointed me in a direction I would not have thought of looking in.

This database has its roots in MSDE (aka 2000) and the compatability leve is still set to SQL Server 2000. Will this have any impact?. Do I need to change this to  SQL Server 2008.
Is there any advantage.
Other settings of interist :
AutoClose = true (Will change this and test if the delay happens again)
AutoShrink = true. (Is this needed? Will it not cause delays)

tcss
0
tcssAuthor Commented:
Sorry to report that with the AutoClose set to false the same symptoms appear.
Any other suggestions?

tcss
0
tcssAuthor Commented:
Problem appears to be solved.
After a few days of now testing the implemented solution, the"Slow Down" has not happened again.

What I have done is create a CDatabase member in the view. (MFC Doc/View app)
I connect to the database on startup and close the connection when the app exists.

This open connection appears to prevent the database from getting "lost" after a long period of inactivity.
The ATL OLEDB classes allow the connection to be opened, process performed and connection is closed again.

If there are no objections, I am going to ask for the points to be returned raher than leave the question to be abandoned

tcss
0

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
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
System Programming

From novice to tech pro — start learning today.