Performance improves with Studio Query window open

Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.

Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.

So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.

Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.

Here's the connection string used by the application - I have tried various permutations of values to no avail:

Provider=SQLOLEDB.1;Initial Catalog=SampleDB;Data Source=(local)\sqlexpress;Trusted_Connection=yes

Thanks very much

hi, SQLExpress sets by default it's related databases a database propery that could be involved in your observation..

SQLExpress sets the auto close database property to true, so that every database with no active connection is shut down to preserve file integrity.. at next database connection, the database will be re-opened, requiring initial overhead to start it.. you can modify that database option as required...

this "solution" was probably taken out of the box becouse SQLEXpress is expected to execute on "client pcs" and not on "server" hardware that obviously are more "secure" and "stable"...

regards|||

Yes, thank you - switching the Auto Close to False works nicely.

I note that Auto Close is False by default on databases on full SQL Server, so this we only need to watch out for those databases put up on SQL Express.

Thanks again.

Performance improves with Studio Query window open

Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.

Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.

So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.

Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.

Here's the connection string used by the application - I have tried various permutations of values to no avail:

Provider=SQLOLEDB.1;Initial Catalog=SampleDB;Data Source=(local)\sqlexpress;Trusted_Connection=yes

Thanks very much

hi, SQLExpress sets by default it's related databases a database propery that could be involved in your observation..

SQLExpress sets the auto close database property to true, so that every database with no active connection is shut down to preserve file integrity.. at next database connection, the database will be re-opened, requiring initial overhead to start it.. you can modify that database option as required...

this "solution" was probably taken out of the box becouse SQLEXpress is expected to execute on "client pcs" and not on "server" hardware that obviously are more "secure" and "stable"...

regards|||

Yes, thank you - switching the Auto Close to False works nicely.

I note that Auto Close is False by default on databases on full SQL Server, so this we only need to watch out for those databases put up on SQL Express.

Thanks again.

Performance Anomaly

I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?Run a Profiler trace and see exactly how the procedure is being called
from your web application.|||Please do not multipost. I answered in .server.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Tom @. Metrinex" <TomMetrinex@.discussions.microsoft.com> wrote in message
news:5286E608-C003-4F01-B2AF-7913134C2996@.microsoft.com...
I am currently having a strange performance problem with one of my queries.
If I run a stored procedure from my web application or from Visual Studio it
will timeout after 5 minutes (my timeout setting). If run the stored
procedure using SQL Query Analyzer it executes without issue in 19 seconds.
What can cause this severe difference in the performance of this stored
procedure?

Performance and logs

Hi,

I'm using SQL Server Management Studio Express and I have made a website
that uses a database with stored procedures.

When running pages in my webpages it cost around 2 seconds to load each
page.
Thanks too long!! But where is the problem?

I like to see a list of all executed stored procedures with there execute
time (for each page).
In this way I can check if the problem is here.

How can I get a logfile like this?

Thanks!

Performance tuning part of SQL Server does not come with Express, the Tuning Advisor which replaced the Index tuning Wizard, PerfMon and Profiler. The last can tell you stored proc duration and other statistics. Try the link below and download the eval which is good for 180days but it will be a good idea to buy the no deployment developer edition because it cost only $33. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

Perform a select asynchronously with ADO (C++)

I'm working with ADO 2.8 en C++ with Visual Studio 2005. I want to perform a "select" in asynchronous mode. I don't really understand the logical of the recordset events. For example, I received a number of MoveComplete event higher than the number of rows in my recordset. It is really not clear for me ...

Does someone knows where I can find a a good example of C++ (or VB) code to manage select statements in asynchronous mode ?

Thanks in advance for your help.

Fran?ois.

ADO Code Examples in Visual C++

other links:

WillMove and MoveComplete Events (ADO)
With Further ADO
Asynchronous Processing (OLE DB)