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 counters on 64 bit SSIS

On 32 bit SSIS installations, both of the following performance counter objects are visible in perfmon.

SQLServer:SSIS Service

SQLServer:SSIS Pipeline

On 64 bit SSIS installations, only the following is available.

SQLServer:SSIS Service

The SQlServer:SSIS Pipeline counters are nowhere to be found.

Should I re-install? Is this a known issue with 64 bit SSIS?

P.S. Remote or local access administrative access with perfmon makes no difference, the "SQLServer:SSIS Pipeline" performance counters don't appear in the listbox when connecting to Windows 2003 x64 server.

Try running perfmon w/ 32-bit switch like so:

mmc.exe /32 perfmon.msc

Counters should show up.

|||

Halfway answered.

Local perfmon access to x64 "SQlServer:SSIS Pipeline" counters: yes

Remote perfmon access to x64 "SQlServer:SSIS Pipeline" counters: no

As to the local SSIS performance counter access on x64, using the SysWOW64 version of perfmon.exe does pick up the"SQlServer:SSIS Pipeline" counters.(e.g. C\WINDOWS\SysWOW64\perfmon.exe), so thanks for that.

Performance Comparison - Code vs SqlDataSource, Gridview etc vs PlainControl

There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.

So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.

What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.

Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?

I don't have a tested answer for you, just an opinion. Re your own connect vs SqlDataSource, there should be no differenct. The SqlDataSource has to do everything you do so I don't see any performance implication on a single screen. With that said, though, I could see somepotential connection pooling issues since minor differences in connection strings prevent asp.net from reusing the same connection. Without going into details, I think that awell writtenconnection object (that gets reused) is more likely to allow pooling than typing your connection details into wizards all the time. In any case, I wouldn't worry about it at this stage of the game.

Re GridView vs writing all that stuff yourself. If you use all or most or even some of that functionality, I don't think you should attempt to rewrite it -- it's just not worth it. You may even make performance worse if you write it wrong, and frankly, programmer time is worth more than cpu time.

If you're interested in pursuing the issue, Farhan Muhammed wrote a book which gives some fairly detailed numbers comparing different access methods and different controls -- he did real performance comparisons. Real World ASP.NET Best Practiceshttp://www.amazon.com/Real-World-ASP-NET-Best-Practices/dp/1590591003

|||

Thanks a lot for your opinion. By "well written connection object that gets reused" do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope?

|||

ilovecats:

do you mean creating an SqlConnection object once, and try to re-use that same connection for as many command objects (select,insert,update tasks etc) as possible within the same scope

Well, what I really mean is that you have a common object used by all pages & modules in your application that manages connections for you, along the lines of an Application Block like this onehttp://msdn2.microsoft.com/en-us/library/aa480458.aspx. Using something like this not only hides the details of the connection from the programmer, it goes a long way towards assuring consistency in connection strings -- which is something you need if asp.net is to be able to pool connections (ie, getting a connection is very expensive, so asp.net keeps a pool of connections around for reuse, if you request a connection and one is available from the pool that has an identical connection string, it gets that one instead of creating a new one).

Now, I'm sure that MS uses good programming when they obtain connections, but it used to be the case -- and I think still is largely the case -- that any differences in your connection string (even cosmetic ones, like case and extra white space) prevent sharing (pooling) connections, so if 2 people put in the same connection strings but with different case, they could not share connections in the pool. I don't know anymore how true this, I seem to recall reading that leading and trailing spaces don't matter anymore, but I'm not sure. A data connection application block would typically get the connection info from a config file, which is to say that everyone uses the same config file, which is to say everyone who connects to sqlserver1.mydatabase has an identical connection string. OTOH, if you use the SqlDataSource, every programmer is entering the connectio info (isn't that right -- perhaps I'm missing something because I haven't used them very much, but I think that's how it has to work -- if I'm wrong I hope someone will correct me), raising the possibility that the connection strings will be a little different, hence they cannot share connections.

I think I'll post a question on this topic to see if my understanding is current.

However, I frankly wouldn't worry about it too much. If you get into it, fine, but unless you're working on a high volume application it won't make a meaningful difference.

In my shop, we do use a data access application block that someone else wrote, but that didn't stop me from using the"no programming" features of asp.net 2.0 SqlDataSource because we just aren't a high volume app.

Perfomance

Hi All,
I have a little bit of code that looks something like this
SELECT
code,
[description],
clientDescription,
band,
AVG(rental) AS value,
COUNT(tableTemp.surveyID) AS [count],
CASE clientID
WHEN @.clientID THEN 1
ELSE 0
END AS color
FROM
(
SELECT tblSurvey.surveyID,
tblSurvey.clientID AS clientID,
client_alias AS code,
CONVERT(varchar(25), client_alias) AS [description],
client_desc AS clientDescription,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS tableTemp
GROUP BY code,
[description],
clientDescription,
band,
tableTemp.clientID
UNION ALL
SELECT
'99' AS code,
'IMV' AS [description],
'IMV' AS clientDescription,
band,
AVG(rental) AS value,
COUNT(averageTemp.surveyID) AS [count],
0 AS color
FROM (SELECT tblSurvey.surveyID,
#StartOfPeriod.band,
rental
FROM tblSurvey
INNER JOIN tblClient
ON tblClient.clientID = tblSurvey.clientID
INNER JOIN #surveys
ON #surveys.surveyID = tblSurvey.surveyID
INNER JOIN #StartOfPeriod
ON #StartOfPeriod.editionID=tblSurvey.editionID
WHERE (mileage = @.mileageint OR @.mileageint = 0)
AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
AND tblSurvey.editionID >= @.startEdition
AND tblSurvey.editionID < @.limitEdition
AND client_alias IS NOT NULL
AND rental IS NOT NULL
) AS averageTemp
GROUP BY band
I have tried everything I can think of to make this run quicker, even though
it's not really slow, just want to get the best performance possible. There
are no way's around the temp tables that I need to use so I am just wonderin
g
if there is a better way that I can write the select statement'
Any help would be great.
Thanks PhilHave you tried something like this already? Your using select statements
with selects statements in an Union. I can imagine that your solutions isn't
the fasted way to get the information.
SELECT code
, [description]
, clientDescription
, COUNT(tableTemp.surveyID) AS [count]
, tblSurvey.clientID AS clientID
, client_alias AS code
, CONVERT(varchar(25), client_alias) AS [description], client_desc AS
clientDescription
, #StartOfPeriod.band
, rental
, CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
FROM tblSurvey
<-- Rest of Query -->
GROUP BY code, [description], clientDescription, band, tableTemp.clientID
UNION ALL
SELECT '99' AS code
,'IMV' AS [description]
,'IMV' AS clientDescription
,COUNT(averageTemp.surveyID) AS [count]
, #StartOfPeriod.band as [band]
,AVG(rental) AS value
,0 AS color
fROM tblSurvey
INNER JOIN tblClient
AND client_alias IS NOT NULL
AND rental IS NOT NULL
<-- Rest of Query -->
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even
though
> it's not really slow, just want to get the best performance possible.
There
> are no way's around the temp tables that I need to use so I am just
wondering
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi there,
Thanks for the reply, it doesn't look like something that I have tired yet
but I am havinbg a few problems understanding the replacement code that you
put in, is there any chance you could explain it a little further.
Thanks in advance, Phil
"M. de Jong" wrote:

> Have you tried something like this already? Your using select statements
> with selects statements in an Union. I can imagine that your solutions isn
't
> the fasted way to get the information.
> SELECT code
> , [description]
> , clientDescription
> , COUNT(tableTemp.surveyID) AS [count]
> , tblSurvey.clientID AS clientID
> , client_alias AS code
> , CONVERT(varchar(25), client_alias) AS [description], client_desc AS
> clientDescription
> , #StartOfPeriod.band
> , rental
> , CASE clientID WHEN @.clientID THEN 1 ELSE 0 END AS color
> FROM tblSurvey
> <-- Rest of Query -->
> GROUP BY code, [description], clientDescription, band, tableTemp.clientID
> UNION ALL
> SELECT '99' AS code
> ,'IMV' AS [description]
> ,'IMV' AS clientDescription
> ,COUNT(averageTemp.surveyID) AS [count]
> , #StartOfPeriod.band as [band]
> ,AVG(rental) AS value
> ,0 AS color
> fROM tblSurvey
> INNER JOIN tblClient
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> <-- Rest of Query -->
>
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:CE9CEEE5-9D1E-4735-9A1A-72B01C5105FA@.microsoft.com...
> though
> There
> wondering
>
>|||Hi
A couple of things:
Can this be done with ROLLUP?
#Surveys does not seem to be doing much.
Use owner prefixes and aliases.
Look at the query plans/indexes.
John
"Phil" wrote:

> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi,
Thanks for the reply, you are correct #surveys doesn't do much, when you say
use ownder alias and prefixes, I am not quite sure what you mean, sorry abou
t
that. I have looked at the query plan and the biggest percentage comes from
Index scans but I am afriad I am not able to change them. I am also not sur
e
how ROLLUP would be of any help.
Sorry about all the question!!!
"John Bell" wrote:
> Hi
> A couple of things:
> Can this be done with ROLLUP?
> #Surveys does not seem to be doing much.
> Use owner prefixes and aliases.
> Look at the query plans/indexes.
> John
> "Phil" wrote:
>|||Hi
Your UNIONED line seems to be a summary line, in which case using
ROLLUP could do that for you.
Using table aliases throughout would make everything clearer (you don't
need the column aliases in the unioned part!).
Using the owner prefix will minimise name resolution as described in
the paragraph
"Fully Qualify Database Objects" See
http://msdn.microsoft.com/library/d...
etchapt14.asp
John
Phil wrote:
> Hi,
> Thanks for the reply, you are correct #surveys doesn't do much, when
you say
> use ownder alias and prefixes, I am not quite sure what you mean,
sorry about
> that. I have looked at the query plan and the biggest percentage
comes from
> Index scans but I am afriad I am not able to change them. I am also
not sure
> how ROLLUP would be of any help.
> Sorry about all the question!!!
> "John Bell" wrote:
>
even though
possible. There
just wondering|||Phil,
There are a few things you can do.
1) What M. de Jong tried to explain, is that your current query is
something like this:
SELECT ... FROM (
SELECT ... FROM <T1> GROUP BY <GB1>
UNION ALL
SELECT ... FROM <T2> GROUP BY <GB2>
) GROUP BY <GB3>
This query can be rewritten as:
SELECT ... FROM <1> GROUP BY <GB1>,<GB3>
UNION ALL
SELECT ... FROM <2> GROUP BY <GB2>,<GB3>
This eliminates one GROUP BY action.
2) As Phil mentioned, table #Surveys doesn't seem to do much. But if it
is necessary, and there is a one-to-one relation with tblSurvey, then
you could remove the INNER JOIN with #Surveys, and add
AND EXISTS (SELECT 1 FROM #Surveys WHERE #surveys.surveyID =
tblSurvey.surveyID)
and see if that makes any difference in performance.
3) If the columns mileage and age_in_months do not contain NULLs, then
you could rewrite the predicates with these columns in such a way that
the OR is removed and an index s could potentially be used. For
example, you could rewrite:
AND (mileage = @.mileageint OR @.mileageint = 0)
with:
AND mileage BETWEEN COALESCE(NULLIF(@.mileageint,0), -2147483648)
AND COALESCE(NULLIF(@.mileageint,0), 2147483647)
4) Make sure all join keys are indexed. In addition, you could try the
Index Tuning Wizard.
Note that this is based on what you posted. Since you did not post any
DDL, we cannot see for all columns from which table they are selected
(the missing table aliases John was talking about).
Hope this helps,
Gert-Jan
Phil wrote:
> Hi All,
> I have a little bit of code that looks something like this
> SELECT
> code,
> [description],
> clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(tableTemp.surveyID) AS [count],
> CASE clientID
> WHEN @.clientID THEN 1
> ELSE 0
> END AS color
> FROM
> (
> SELECT tblSurvey.surveyID,
> tblSurvey.clientID AS clientID,
> client_alias AS code,
> CONVERT(varchar(25), client_alias) AS [description],
> client_desc AS clientDescription,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS tableTemp
> GROUP BY code,
> [description],
> clientDescription,
> band,
> tableTemp.clientID
> UNION ALL
> SELECT
> '99' AS code,
> 'IMV' AS [description],
> 'IMV' AS clientDescription,
> band,
> AVG(rental) AS value,
> COUNT(averageTemp.surveyID) AS [count],
> 0 AS color
> FROM (SELECT tblSurvey.surveyID,
> #StartOfPeriod.band,
> rental
> FROM tblSurvey
> INNER JOIN tblClient
> ON tblClient.clientID = tblSurvey.clientID
> INNER JOIN #surveys
> ON #surveys.surveyID = tblSurvey.surveyID
> INNER JOIN #StartOfPeriod
> ON #StartOfPeriod.editionID=tblSurvey.editionID
> WHERE (mileage = @.mileageint OR @.mileageint = 0)
> AND (age_in_months = @.termsmallint OR @.termsmallint = 0)
> AND tblSurvey.editionID >= @.startEdition
> AND tblSurvey.editionID < @.limitEdition
> AND client_alias IS NOT NULL
> AND rental IS NOT NULL
> ) AS averageTemp
> GROUP BY band
> I have tried everything I can think of to make this run quicker, even thou
gh
> it's not really slow, just want to get the best performance possible. Ther
e
> are no way's around the temp tables that I need to use so I am just wonder
ing
> if there is a better way that I can write the select statement'
> Any help would be great.
> Thanks Phil|||Hi Phil
A possible alternative to Gert-Jan's point2 would be to change the way
that #surveys is constructed so that it includes (when constructed) the
information you re-select from tblSurveys and therefore tblSurveys can
be excluded from the statement.
John
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi there,
I would just like to say a quick thanks to everyone that has posted a
reply to my question, I have now managed to get it working better with a
combination of your solutions.
Sorry I didn't post exactly what you needed but I will rememeber for
next time, thanks again.
Phil
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!