Performance Question

Obviously you do not want to have an open connection to a database
when your program is waiting for user input but if you are doing a
bunch of processing after a user request is it better to open and
close connections when you need access to the database or open once,
use multiple times and then close when you know you will not need
access again?

I guess what I am asking is, what is more important? Minimizing your
concurrent connections to a database or the performance hit of
repeated open and closing a connection.

I know there probably isn't one correct answer but what should I take
into consideration?

John.
The general rule of thumb here is "open late, close early".

Open your connection just before you need to use it, and close it as soon as possible. In my opinion, you would do this each time you need a connection. The reason for this is that by default, your connections are pooled. Normally, when you open a connection that uses an identical connection string as one that has already been opened and closed, you application will grab that connection again. Therefor, the connection does not need to be initialized and authenticated to SQL Server again.

Generally, I design my methods to perform as few database operations as possible with a goal of creating methods that are loosely coupled and highly cohesive. I create and open the connection within the method, use it, then close it. I have analyzed the connection performance with Performance Monitor and this seems to work well. Sometimes it is necessary to loop through some data, and process some database operation one record at a time. In this case I will usually open the connection just before the loop, and close it immediately after.

Ultimately, you may need to experiment a little for yourself. Every situation is different, and calls for different design considerations. You may be interested in having a look at theData Access Application Block from Microsoft for somebest practices.

Hope this is helpful.
|||I agree with NetProfit. I would like to add a couple of points to think on. Its not just for Db hits either, but you should also try to avoid chatty interface to the DB as well, to cut down on network roundtrips. I would also say that generally its better to get the whole result set back, close the connection ASAP and then work on the data. I've seen plenty of solutions that mis-use the datareader.|||Thanks. I had forgetten about connection pooling. I am looking forward to reading the Data Access Application Block article too.

John.

Performance problems ADODB connection

Hi,
My programm (24/7 - VB6) is connected to a MSSQL Database with only one
table where data is stored with a time_stamp field as index. Automatically
the software updates each 2 minutes the table and with the same frequency
querys and display data for the last 3 days.
I have also one function where the user can select the amount of the
displayed data. When the software is working for a few ws in automatic
mode (allways 3 day - data displaying) and the a user start a request to
display all the datas in the table, at the first time the query is failing
with timeout and at the second request is returning the requested data. I
use the ADODB Connection object with client side cursor which is used in a
ADODB.Recordset Open method to return the data.
Has anybody a ideea why is the first time query failing and /or how to
prevent this ?
Thank you !Hi
It appears to be contention problem.Try using lower level locks and if
needed you may use NOLOCK HINT with the select query in backend if possible
.
Regards
R.D
"Marius Cecon" wrote:

> Hi,
> My programm (24/7 - VB6) is connected to a MSSQL Database with only one
> table where data is stored with a time_stamp field as index. Automatically
> the software updates each 2 minutes the table and with the same frequency
> querys and display data for the last 3 days.
> I have also one function where the user can select the amount of the
> displayed data. When the software is working for a few ws in automatic
> mode (allways 3 day - data displaying) and the a user start a request to
> display all the datas in the table, at the first time the query is failing
> with timeout and at the second request is returning the requested data. I
> use the ADODB Connection object with client side cursor which is used in a
> ADODB.Recordset Open method to return the data.
> Has anybody a ideea why is the first time query failing and /or how to
> prevent this ?
> Thank you !
>
>|||Hi RD,
Thank you for trying to help me.
I've changed my select query adding the NOLOCK hint but is no change in the
behaviour.
They are no multiuser access to the table, my programm is the only one
connected to the table over a Connection object created at programm start
and closed when programm ends.
Marius.
"R.D" <RD@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF677CC9-1EB4-4EA9-A588-BC7FA12E4E09@.microsoft.com...
> Hi
> It appears to be contention problem.Try using lower level locks and if
> needed you may use NOLOCK HINT with the select query in backend if
possible.
> Regards
> R.D
> "Marius Cecon" wrote:
>
Automatically
frequency
automatic
failing
I
a|||On Wed, 31 Aug 2005 09:33:20 +0200, "Marius Cecon" <m.cecon@.hydrovision.de>
wrote:
in <ugELI5frFHA.2076@.TK2MSFTNGP14.phx.gbl>

>Hi,
>My programm (24/7 - VB6) is connected to a MSSQL Database with only one
>table where data is stored with a time_stamp field as index. Automatically
>the software updates each 2 minutes the table and with the same frequency
>querys and display data for the last 3 days.
>I have also one function where the user can select the amount of the
>displayed data. When the software is working for a few ws in automatic
>mode (allways 3 day - data displaying) and the a user start a request to
>display all the datas in the table, at the first time the query is failing
>with timeout and at the second request is returning the requested data. I
>use the ADODB Connection object with client side cursor which is used in a
>ADODB.Recordset Open method to return the data.
>Has anybody a ideea why is the first time query failing and /or how to
>prevent this ?
>Thank you !
Try setting the connection object's CommandTimeout property to zero (infinit
e)
just prior to the recordset Open method.
Stefan Berglund

performance on query analyzer and .NET

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.com
Hi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com
|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com. ..
>
|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>
|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
..NET
>
|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>

performance on query analyzer and .NET

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> > Dear Sir,
> >
> > I used dynamic SQL on stored procedure to do the paging in my site.
> >
> > I often hit a connection with that dynamic SQl when I call it from .NET
> > code.
> > However if it run it under query analyzer, everything works fine.
> >
> > Who can explain this to me and what should I do in this case?
> >
> > regards,
> >
> > Guoqi Zheng
> > http://www.ureader.com
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> Hi
>> How do you call it? Have you created a stored procedure which accepts
>> parameters?
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> > Dear Sir,
>> >
>> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >
>> > I often hit a connection with that dynamic SQl when I call it from .NET
>> > code.
>> > However if it run it under query analyzer, everything works fine.
>> >
>> > Who can explain this to me and what should I do in this case?
>> >
>> > regards,
>> >
>> > Guoqi Zheng
>> > http://www.ureader.com
>>
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
> > yes... I have a stored procedure which accept pagenr, pagesize as
> > parameter..
> >
> > The correct sentence is that I hit a connection time out problem when I
> > call
> > it from .NET code..
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> >> Hi
> >> How do you call it? Have you created a stored procedure which accepts
> >> parameters?
> >>
> >>
> >> "guoqi zheng" <no@.sorry.com> wrote in message
> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
> >> > Dear Sir,
> >> >
> >> > I used dynamic SQL on stored procedure to do the paging in my site.
> >> >
> >> > I often hit a connection with that dynamic SQl when I call it from
.NET
> >> > code.
> >> > However if it run it under query analyzer, everything works fine.
> >> >
> >> > Who can explain this to me and what should I do in this case?
> >> >
> >> > regards,
> >> >
> >> > Guoqi Zheng
> >> > http://www.ureader.com
> >>
> >>
> >
> >
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
>> It is probably because you've got a 'bad' execution pln. Try recompile
>> the
>> SP and see what is going on
>>
>>
>> "Guoqi Zheng" <no@.sorry.com> wrote in message
>> news:dtho1p$i4i$1@.newspe.com...
>> > yes... I have a stored procedure which accept pagenr, pagesize as
>> > parameter..
>> >
>> > The correct sentence is that I hit a connection time out problem when I
>> > call
>> > it from .NET code..
>> >
>> >
>> >
>> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> > news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>> >> Hi
>> >> How do you call it? Have you created a stored procedure which accepts
>> >> parameters?
>> >>
>> >>
>> >> "guoqi zheng" <no@.sorry.com> wrote in message
>> >> news:4cc7b9b7665549a4b34db8869b21a9f0@.ureader.com...
>> >> > Dear Sir,
>> >> >
>> >> > I used dynamic SQL on stored procedure to do the paging in my site.
>> >> >
>> >> > I often hit a connection with that dynamic SQl when I call it from
> .NET
>> >> > code.
>> >> > However if it run it under query analyzer, everything works fine.
>> >> >
>> >> > Who can explain this to me and what should I do in this case?
>> >> >
>> >> > regards,
>> >> >
>> >> > Guoqi Zheng
>> >> > http://www.ureader.com
>> >>
>> >>
>> >
>> >
>>
>

performance on query analyzer and .NET

Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection with that dynamic SQl when I call it from .NET code
.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comHi
How do you call it? Have you created a stored procedure which accepts
parameters?
"guoqi zheng" <no@.sorry.com> wrote in message
news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
> Dear Sir,
> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection with that dynamic SQl when I call it from .NET
> code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||yes... I have a stored procedure which accept pagenr, pagesize as
parameter..
The correct sentence is that I hit a connection time out problem when I call
it from .NET code..
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi
> How do you call it? Have you created a stored procedure which accepts
> parameters?
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:4cc7b9b7665549a4b34db8869b21a9f0@.ur
eader.com...
>|||It is probably because you've got a 'bad' execution pln. Try recompile the
SP and see what is going on
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dtho1p$i4i$1@.newspe.com...
> yes... I have a stored procedure which accept pagenr, pagesize as
> parameter..
> The correct sentence is that I hit a connection time out problem when I
> call
> it from .NET code..
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#7ubM76NGHA.1132@.TK2MSFTNGP10.phx.gbl...
>|||I actually do not know how can I compile my SQL code, not to mention
recompile.
Do you know how? I thought when I create a SP, it is compile automatically?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> It is probably because you've got a 'bad' execution pln. Try recompile the
> SP and see what is going on
>
>
> "Guoqi Zheng" <no@.sorry.com> wrote in message
> news:dtho1p$i4i$1@.newspe.com...
.NET[vbcol=seagreen]
>|||There is sp_recompile stored procedure, please refer to the BOL
"Guoqi Zheng" <no@.sorry.com> wrote in message
news:dths3a$jq6$1@.newspe.com...
>I actually do not know how can I compile my SQL code, not to mention
> recompile.
> Do you know how? I thought when I create a SP, it is compile
> automatically?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#W72oL7NGHA.3460@.TK2MSFTNGP15.phx.gbl...
> .NET
>

Performance of named instance

Hi,
I have an app that ran just fine with the default instance of MSDE. I
changed the connection string in the app to work with a named instance
instead, like this: (local)/MyNamedInstance.
Database performance slowed measurably as soon as I did that, particularly
with DDL commands, like:
CREATE DEFAULT BOOLEAN_DEFAULT AS ''F''
CREATE RULE BOOLEAN_RULE AS @.list IN (''T'', ''F'')
sp_addtype DOM_BOOLEAN, ''VARCHAR(1)'', ''NOT NULL''
sp_bindefault BOOLEAN_DEFAULT, DOM_BOOLEAN
sp_bindrule BOOLEAN_RULE, DOM_BOOLEAN
It literally takes 10-15 seconds to execute about 4 of these lines of code,
where they used to execute pretty much immediately.
Is there anything special I need to do with a named instance of MSDE to
obtain the same performance levels?
Thanks in advance.
-Eric Harmon
If the instances are on the same machine it is possible that the first
instance has taken the majority of the memory and is still using it. If the
first instance needs the memory it will not free it up for the second
instance.
I have never seen a case where the cause of a performance problem was a
default vs. a named instance, so you need to concentrate on the other
differences; resources available, differences in data, schema differences,
disk speed, etc.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.