[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Accessing SQL2005

Budwieser

8/17/2007 3:21:00 PM

I have a fairly heavy duty app that can use SQL2005, however I am getting
some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
appear to happen. Here's what happens:

The VB6 application makes fairly regular calls on the database (only when
the user requests data). At what appears to be completely random times
(when the user makes a request for data) the application will lock up for
anything up to 1 minute before carrying on. Obviously the user thinks the
machine has crashed and starts clicking everywhere, which does make the
application crash!

This does not appear to happen with MSDE or SQL2000 databases. My
application opens and maintains only one connection to the database, where
all the SQL requests are piped through. I'm wondering if this is good
practice? Also my application never maintains open recordsets, I always use
SELECT to populate a screen, and only when the user hits a save button does
the database get updated with UPDATE and INSERT SQL commands.

If no one can shed any light on this specific problem, does anyone have a
guide for best practices when it comes to connection states.

Kind Regards

Tim


8 Answers

Dave Michaud

8/17/2007 3:58:00 PM

0

Lots of possibilities here - Missing index, insufficient RAM on DB or Client,
Network storm. What's the app doing during the hang? Use Profiler to see
what the DB is doing. Does 2005 SP1 or SP2 work any better?

"Timbo" wrote:

> I have a fairly heavy duty app that can use SQL2005, however I am getting
> some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
> appear to happen. Here's what happens:
>
> The VB6 application makes fairly regular calls on the database (only when
> the user requests data). At what appears to be completely random times
> (when the user makes a request for data) the application will lock up for
> anything up to 1 minute before carrying on. Obviously the user thinks the
> machine has crashed and starts clicking everywhere, which does make the
> application crash!
>
> This does not appear to happen with MSDE or SQL2000 databases. My
> application opens and maintains only one connection to the database, where
> all the SQL requests are piped through. I'm wondering if this is good
> practice? Also my application never maintains open recordsets, I always use
> SELECT to populate a screen, and only when the user hits a save button does
> the database get updated with UPDATE and INSERT SQL commands.
>
> If no one can shed any light on this specific problem, does anyone have a
> guide for best practices when it comes to connection states.
>
> Kind Regards
>
> Tim
>
>
>

William Vaughn

8/17/2007 3:58:00 PM

0

Okay, what else is the SQL Server system doing? Is the system hosting
Reporting Services, a print server or a screen saver?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Timbo" <me@home.com> wrote in message
news:e2xD%23IO4HHA.1188@TK2MSFTNGP04.phx.gbl...
>I have a fairly heavy duty app that can use SQL2005, however I am getting
>some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
>appear to happen. Here's what happens:
>
> The VB6 application makes fairly regular calls on the database (only when
> the user requests data). At what appears to be completely random times
> (when the user makes a request for data) the application will lock up for
> anything up to 1 minute before carrying on. Obviously the user thinks the
> machine has crashed and starts clicking everywhere, which does make the
> application crash!
>
> This does not appear to happen with MSDE or SQL2000 databases. My
> application opens and maintains only one connection to the database, where
> all the SQL requests are piped through. I'm wondering if this is good
> practice? Also my application never maintains open recordsets, I always
> use SELECT to populate a screen, and only when the user hits a save button
> does the database get updated with UPDATE and INSERT SQL commands.
>
> If no one can shed any light on this specific problem, does anyone have a
> guide for best practices when it comes to connection states.
>
> Kind Regards
>
> Tim
>

Budwieser

8/17/2007 4:12:00 PM

0

I think I may have the answer.

After reading about connection pooling, I think I might have my answer....
Am I right in saying.....

At the moment my application opens a OLEDB connection when the user starts
the application. At no point is the connection closed until the application
quits (even then I don't think I have a close command), however I ALWAYS
close and release recordsets and commands. If I was to close the connection
every time I retrieved a recordset or executed a command (or whatever),
because of connection pooling the performance wouldn't be hit, but each
command will have a nice new fresh connection to use.

What do you reckon?


"Dave Michaud" <DaveMichaud@discussions.microsoft.com> wrote in message
news:4E6D21D8-087E-4894-98D8-460F6ABAA58F@microsoft.com...
> Lots of possibilities here - Missing index, insufficient RAM on DB or
> Client,
> Network storm. What's the app doing during the hang? Use Profiler to see
> what the DB is doing. Does 2005 SP1 or SP2 work any better?
>
> "Timbo" wrote:
>
>> I have a fairly heavy duty app that can use SQL2005, however I am getting
>> some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
>> appear to happen. Here's what happens:
>>
>> The VB6 application makes fairly regular calls on the database (only when
>> the user requests data). At what appears to be completely random times
>> (when the user makes a request for data) the application will lock up for
>> anything up to 1 minute before carrying on. Obviously the user thinks
>> the
>> machine has crashed and starts clicking everywhere, which does make the
>> application crash!
>>
>> This does not appear to happen with MSDE or SQL2000 databases. My
>> application opens and maintains only one connection to the database,
>> where
>> all the SQL requests are piped through. I'm wondering if this is good
>> practice? Also my application never maintains open recordsets, I always
>> use
>> SELECT to populate a screen, and only when the user hits a save button
>> does
>> the database get updated with UPDATE and INSERT SQL commands.
>>
>> If no one can shed any light on this specific problem, does anyone have a
>> guide for best practices when it comes to connection states.
>>
>> Kind Regards
>>
>> Tim
>>
>>
>>


Dave Michaud

8/17/2007 5:44:00 PM

0

I don't know the answer to this new question. I think you should create a
new post for this question. Maybe the SQL Server OLEDB group is probably
better.

"Timbo" wrote:

> I think I may have the answer.
>
> After reading about connection pooling, I think I might have my answer....
> Am I right in saying.....
>
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the application
> quits (even then I don't think I have a close command), however I ALWAYS
> close and release recordsets and commands. If I was to close the connection
> every time I retrieved a recordset or executed a command (or whatever),
> because of connection pooling the performance wouldn't be hit, but each
> command will have a nice new fresh connection to use.
>
> What do you reckon?
>
>
> "Dave Michaud" <DaveMichaud@discussions.microsoft.com> wrote in message
> news:4E6D21D8-087E-4894-98D8-460F6ABAA58F@microsoft.com...
> > Lots of possibilities here - Missing index, insufficient RAM on DB or
> > Client,
> > Network storm. What's the app doing during the hang? Use Profiler to see
> > what the DB is doing. Does 2005 SP1 or SP2 work any better?
> >
> > "Timbo" wrote:
> >
> >> I have a fairly heavy duty app that can use SQL2005, however I am getting
> >> some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
> >> appear to happen. Here's what happens:
> >>
> >> The VB6 application makes fairly regular calls on the database (only when
> >> the user requests data). At what appears to be completely random times
> >> (when the user makes a request for data) the application will lock up for
> >> anything up to 1 minute before carrying on. Obviously the user thinks
> >> the
> >> machine has crashed and starts clicking everywhere, which does make the
> >> application crash!
> >>
> >> This does not appear to happen with MSDE or SQL2000 databases. My
> >> application opens and maintains only one connection to the database,
> >> where
> >> all the SQL requests are piped through. I'm wondering if this is good
> >> practice? Also my application never maintains open recordsets, I always
> >> use
> >> SELECT to populate a screen, and only when the user hits a save button
> >> does
> >> the database get updated with UPDATE and INSERT SQL commands.
> >>
> >> If no one can shed any light on this specific problem, does anyone have a
> >> guide for best practices when it comes to connection states.
> >>
> >> Kind Regards
> >>
> >> Tim
> >>
> >>
> >>
>
>
>

Ralph

8/17/2007 6:47:00 PM

0


"Timbo" <me@home.com> wrote in message
news:%236IXdlO4HHA.5724@TK2MSFTNGP05.phx.gbl...
> I think I may have the answer.
>
> After reading about connection pooling, I think I might have my answer....
> Am I right in saying.....
>
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the
application
> quits (even then I don't think I have a close command), however I ALWAYS
> close and release recordsets and commands. If I was to close the
connection
> every time I retrieved a recordset or executed a command (or whatever),
> because of connection pooling the performance wouldn't be hit, but each
> command will have a nice new fresh connection to use.
>
> What do you reckon?
>
>
<snipped>

That sounds backwards.

I think you meant you didn't release the ADODB.Connection object until the
Application quits. You should be opening and closing "connections" before
and immediately after you use one, the same as for Commands and Recordsets.

Amplification (or confusion <g>) follows...

When using Connection Pooling in ADO if you Open a Connection the pool
provides one. Period. Whether it gets the last one in a queue, or a new one,
is all dependent on what is going on. If there is no activity for a period
of time (it used to be like a second, it is now far less) then the
connection is closed automatically.

So when using ADO Connection Pooling, standard practice is to create one
ADODB.Connection object and open it when ever one is needed. And close it
when it is not. (Again don't destroy it, just open/close) You're never
actually "Closing" the connection, you merely signalling the POOL that it
can if it wants to. ie, helps manage the Pool manage its pool.

[Just to make sure we are talking about the same thing here.
If I create an object as in...
Dim cnn As ADODB.Connection: Set cnn = New ADODB.Connection.
I have created an object which has an ADO Pool associated with it.
This object creation does all the real grunt work, it figures out the
various middle layer components that are going to be used, and checks to
make sure they are available. It then seeks out the store and asks
permission to talk. ADO and the store work out the details. ADO then sets up
a virtual wire from your app to the store. But doesn't plug it in. With all
of the real time consuming stuff out of the way. ADO announces the
Connection object ready and then waits for instructions. When it receives an
Open then it plugs the wire in, on close, it unplugs it, on long delays it
unplugs the wire rather that waste a socket.

When I do a ...
cnn.Open blah blah
I am only asking the Pool for an open connection]

Thus forget about trying to micro-manage "Connections" if using the Pool.
Just let the Pool do its job. It is likely better at it than you are. <g>

What OLEDB provider are you using. You may need to upgrade.

hth
-ralph

William Vaughn

8/17/2007 7:16:00 PM

0

Depending on the data source, getting a connection can take some time,
however, in my experience, acquiring a SQL Server connection (after the
first) takes less than a second--whether or not you're pooling.
And no, I don't agree that connection pooling makes sense or is at all
necessary for Windows Forms applications. I agree that it's essential for
ASP-based applications or XML Web Services, but not for Windows Forms. I
recommend opening the Connection and leaving it open for the life of the
application. Does this reduce scalability? Sure, to about 3000
connections--far more than any MSDE engine could handle.
I doubt if it's connection overhead that's causing the problem, but it would
be easy to tell. Set the ConnectionTimeout to a low value (say, 10 seconds)
and trap the error/exception. If it goes off you have something to debug.
Consider that SQL Server does not pre-allocate space in the database. From
time to time as new rows are added, it must go off an build extents and
expand the database. On a wimpy system or one that's being used for other
operations (where SQL Server must share resources with other processes),
this can take 10-30 seconds. To prevent the user from clicking and
restarting, be sure to "entertain" them with a progress bar--and make sure
to disable the mouse or the ability to click again in the form. However,
I've found that if the user sees an hourglass, a progress-bar or some other
indication that the app is running they won't reboot. Of course in some
cultures, the propensity to reboot is far shorter (as in New York) or far
longer (as in Arkansas) so your success-rate may vary.

I discuss these approaches in detail in my book.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Timbo" <me@home.com> wrote in message
news:%236IXdlO4HHA.5724@TK2MSFTNGP05.phx.gbl...
>I think I may have the answer.
>
> After reading about connection pooling, I think I might have my answer....
> Am I right in saying.....
>
> At the moment my application opens a OLEDB connection when the user starts
> the application. At no point is the connection closed until the
> application quits (even then I don't think I have a close command),
> however I ALWAYS close and release recordsets and commands. If I was to
> close the connection every time I retrieved a recordset or executed a
> command (or whatever), because of connection pooling the performance
> wouldn't be hit, but each command will have a nice new fresh connection to
> use.
>
> What do you reckon?
>
>
> "Dave Michaud" <DaveMichaud@discussions.microsoft.com> wrote in message
> news:4E6D21D8-087E-4894-98D8-460F6ABAA58F@microsoft.com...
>> Lots of possibilities here - Missing index, insufficient RAM on DB or
>> Client,
>> Network storm. What's the app doing during the hang? Use Profiler to see
>> what the DB is doing. Does 2005 SP1 or SP2 work any better?
>>
>> "Timbo" wrote:
>>
>>> I have a fairly heavy duty app that can use SQL2005, however I am
>>> getting
>>> some rather strange behaviour, if MSDE or SQL2000 is used, this doesn't
>>> appear to happen. Here's what happens:
>>>
>>> The VB6 application makes fairly regular calls on the database (only
>>> when
>>> the user requests data). At what appears to be completely random times
>>> (when the user makes a request for data) the application will lock up
>>> for
>>> anything up to 1 minute before carrying on. Obviously the user thinks
>>> the
>>> machine has crashed and starts clicking everywhere, which does make the
>>> application crash!
>>>
>>> This does not appear to happen with MSDE or SQL2000 databases. My
>>> application opens and maintains only one connection to the database,
>>> where
>>> all the SQL requests are piped through. I'm wondering if this is good
>>> practice? Also my application never maintains open recordsets, I always
>>> use
>>> SELECT to populate a screen, and only when the user hits a save button
>>> does
>>> the database get updated with UPDATE and INSERT SQL commands.
>>>
>>> If no one can shed any light on this specific problem, does anyone have
>>> a
>>> guide for best practices when it comes to connection states.
>>>
>>> Kind Regards
>>>
>>> Tim
>>>
>>>
>>>
>
>

Ralph

8/17/2007 8:25:00 PM

0


"William Vaughn" <billvaNoSPAM@betav.com> wrote in message
news:u2%237GMQ4HHA.4436@TK2MSFTNGP03.phx.gbl...
> Depending on the data source, getting a connection can take some time,
> however, in my experience, acquiring a SQL Server connection (after the
> first) takes less than a second--whether or not you're pooling.
> And no, I don't agree that connection pooling makes sense or is at all
> necessary for Windows Forms applications. I agree that it's essential for
> ASP-based applications or XML Web Services, but not for Windows Forms. I
> recommend opening the Connection and leaving it open for the life of the
> application. Does this reduce scalability? Sure, to about 3000
> connections--far more than any MSDE engine could handle.
> I doubt if it's connection overhead that's causing the problem, but it
would
> be easy to tell. Set the ConnectionTimeout to a low value (say, 10
seconds)
> and trap the error/exception. If it goes off you have something to debug.
> Consider that SQL Server does not pre-allocate space in the database. From
> time to time as new rows are added, it must go off an build extents and
> expand the database. On a wimpy system or one that's being used for other
> operations (where SQL Server must share resources with other processes),
> this can take 10-30 seconds. To prevent the user from clicking and
> restarting, be sure to "entertain" them with a progress bar--and make sure
> to disable the mouse or the ability to click again in the form. However,
> I've found that if the user sees an hourglass, a progress-bar or some
other
> indication that the app is running they won't reboot. Of course in some
> cultures, the propensity to reboot is far shorter (as in New York) or far
> longer (as in Arkansas) so your success-rate may vary.
>
> I discuss these approaches in detail in my book.
>
> hth
>
<snipped>

Whether ADODC.Connections should be Open/Closed is one of the few things I
mildly disagree with you on. I say mildly, because it is tough to defend
doing something that "probably" isn't all that necessary. The average
single-threaded client app likely doesn't need to open and close the
connection, since unless you are in a closed loop occasions where you would
ever need more than one connection at a time, seldom come up. And with the
quick time-out it would be equally difficult to keep one open anyway.

It reminds me of the same situation of whether or not to Set references
dim'd locally to Nothing, or just let the OS take care of it when the
procedure exits. Some will defend an explicit release arguing that one is
saving some clicks. (And perhaps measurable if you call the routine about
30,000 times. Others argue for completeness, that while it is unnecessary
99.9% of the time - there is always that one object, that migration to a
different environment, or chance encounter with a less attentive maintainer
who expands the routine, &etc.

I tend to side with the latter.

Both sides rarely, if ever, reach an agreement. <g>

-ralph

Andrew D. Newbould

8/18/2007 5:04:00 PM

0

On the point of the database re-sizing itself as required:

SQL Server defaults this to 10% of the current size. On a small database
this is not a problem however, if you database is say 30Gb then this
equates to 3Gb of new space and any PC / Server will cough while it adds
a chunk that big. Its far better to change the parameter to allocate a
known amount (say 10Mb ow whatever your system needs) as and when rather
than a percentage.


In message <u2#7GMQ4HHA.4436@TK2MSFTNGP03.phx.gbl>, William Vaughn
<billvaNoSPAM@betav.com> writes
>Depending on the data source, getting a connection can take some time,
>however, in my experience, acquiring a SQL Server connection (after the
>first) takes less than a second--whether or not you're pooling.
>And no, I don't agree that connection pooling makes sense or is at all
>necessary for Windows Forms applications. I agree that it's essential
>for ASP-based applications or XML Web Services, but not for Windows
>Forms. I recommend opening the Connection and leaving it open for the
>life of the application. Does this reduce scalability? Sure, to about
>3000 connections--far more than any MSDE engine could handle.
>I doubt if it's connection overhead that's causing the problem, but it
>would be easy to tell. Set the ConnectionTimeout to a low value (say,
>10 seconds) and trap the error/exception. If it goes off you have
>something to debug. Consider that SQL Server does not pre-allocate
>space in the database. From time to time as new rows are added, it must
>go off an build extents and expand the database. On a wimpy system or
>one that's being used for other operations (where SQL Server must share
>resources with other processes), this can take 10-30 seconds. To
>prevent the user from clicking and restarting, be sure to "entertain"
>them with a progress bar--and make sure to disable the mouse or the
>ability to click again in the form. However, I've found that if the
>user sees an hourglass, a progress-bar or some other indication that
>the app is running they won't reboot. Of course in some cultures, the
>propensity to reboot is far shorter (as in New York) or far longer (as
>in Arkansas) so your success-rate may vary.
>
>I discuss these approaches in detail in my book.
>
>hth
>

--
Andrew D. Newbould E-Mail: NO-usenet-SPAM@zadsoft.com

ZAD Software Systems Web : www.zadsoft.com