Member_2_207537
asked on
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
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
ASKER
Additions Comment/ Question
Do I need to install Microsoft SQL Server 2008 Native Client on the PCs where the client app runns?
tcss
Do I need to install Microsoft SQL Server 2008 Native Client on the PCs where the client app runns?
tcss
ASKER
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
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
ASKER
Sorry to report that with the AutoClose set to false the same symptoms appear.
Any other suggestions?
tcss
Any other suggestions?
tcss
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Setting Database Options" at
http://msdn.microsoft.com/en-us/library/ms190249.aspx