Re: Let me rephrase: Connection Timeouts on local machine ?

Sky,

First off I am not an express expert but I am pretty sure these links answer
all your questions. To the best of my knowledge there is no difference in
performance between SQL Logins and Windows Logins. SQL Server has been using
both for many years. If you don't want the start up costs you should ensure
Auto Close is turned off. If that is not enough you may want to try moving
away from a user instance.

--
Andrew J. Kelly SQL MVP

"Sky" <public.skysigal{*AT*}xact-solutions.com> wrote in message
news:B6F229CF-7E1F-49D7-8AB3-90F347C87A51@xxxxxxxxxxxxxxxx
Andrew,

*Excellent* link! First one alone was worth gold:

The "common problems" exactly describes what has driven me nuts several
times in the past, when I briefly put my foot in the water, and I then
avoided using SSExpress... that issue of locking one of the two
UserInstances
when switching from IDE to app, in debug mode, or today, using SSEE, and
the
web application not running and vice versa.

LOCKING: But Today's problem may or may not be the same thing:

a) The web.config is using a mounted (non-UserInstance) db
b) But I am trying to use SSEE at the same time -- and its not liking it.
Is
SSEE Express Edition using User Instances if I sign in with Windows
authentication?
In other words, would it help if I sign in with "SA", so that it doesn't
use
up a UserInstance account, and leaves it for running the web app?


SLOW STARTS:
In the first article, I see a variable for adjusting the default dump from
mem UserInstances -- (8 minutes or so?) -- but is there one for when using
a
named account ("SA") so that it remains zippy?

c) I see that UserInstances have to do a lot of work (copying databases
etc)
for new Instances -- but only for the first access. The first article then
mentions that it doesn't have to do that for subsequent accesses.... Or
does
the dump from memory after 8 minutes mean that it has to redo all that,
hence
the long times to get going again, and hence the time-out errors in my
error
log?


SQL ACCOUNTS ARE BETTER?
I'm wondering if I'm wrong in starting to get the impression that:
a) the older SQL server accounts are maybe better to work with than the
newer Windows accounts?
b) The slow speed I've seen creating an initial new connection, whether
from
sqlcmd to SSEE Express edition, etc. is maybe not something that exists if
the connection string/account issues can be worked out?

That last part would be nice: up to this point I have been getting the
impression that
i) SqlServer is fast, once it gets running
ii) But it had one heck of a hard time getting out of bed in the morning
to
start work,
iii) That is sure takes a lot of resources to do what its doing (but
that's
a different thread -- although i will state my amazement after reading the
last thread I read while looking for answers where it was stated 'one
should
run sqlserver on a stand alone computer'. Nice utopia, but with that kind
of
thinking, we'll soon end up with a stand-alone computer just to run a
keyboard efficiently..! )

Nope: I'm looking for a new desktop use for SSExpress -- for a small
database, a bit too big for sqlite, a bit too trigger happy for
SSEverywhere, but not sending men to the moon) Firebird is about the right
size, speed, and power, but funky sql.
Hence my interest in SSExpress -- if it can load *fast*. Using it for a
small web server would be nice too ;-)

Night!



"Andrew J. Kelly" wrote:

Sky,

Sounds like you are using a user instance instead of a regular database.
These are set to Autoclose and will basically shut down if you don't
access
it for a few minutes. Have a look at these especially the first one:

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
User Instances

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring
Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/emsqlexcustapp.asp
Embedding Express in Apps
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express


--
Andrew J. Kelly SQL MVP

"Sky" <public.skysigal{*AT*}xact-solutions.com> wrote in message
news:F8EBC41D-C245-4EFB-9A68-A531B125EFAC@xxxxxxxxxxxxxxxx
I'm trying to understand the following:

a) I've just installed SqlServer Express on two machines -- one my Dev
machine to use via VS2005, one on a webserver.

b) Just addressing the dev machine to make the scenario simpler:
if the sqlservice is running.
if the database is local (dif hard drive btw)
if the database is attached, and therefore server knows where it is and
doesn't have to go and find it,
if the db contains only a couple of indexed tables, and the queries are
straight forward non-join selects
if the tables are indexed, and therefore already basically optimized
for
queries,

why would a connection take 10-15 seconds to make? This is basically
10-15
seconds longer than MySQL and I just don't get it.

What is it doing under there that makes it need so much time to get
ready
to
answer a simple SELECT statement? Ie -- what extra features that I will
probably never need, can I turn off, so that it is much more
responsive?

BTW: this happens if it is my own app, or if using SSEE to open a
connection
to the db -- it just seems to take eons, rather than be instantaneous.

I'm hoping here that this has to be a configuration issue (although its
the
default configuration of the downloadable Express SP-1) because this
kind
of
start up delay is a sales killer in my mind. There is abs no way I
could
sell an app that takes that much time to 'warm up'.

Can anybody help me out with this?





.

Relevant Pages