Gurus,

Running SQL Server 2005 SP2 with a client app that is using ODBC. On client
machines, is there a secret that you use to generally try to "speed up" or
make more efficient client connections to the database?

--
Spin

> Running SQL Server 2005 SP2 with a client app that is using ODBC. On
> client machines, is there a secret that you use to generally try to "speed
> up" or make more efficient client connections to the database?

I wouldn't call it a secret, but connection pooling is usually used to avoid
the overhead of repeatedly establishing connections to the same database.
This is the default behavior with ADO and ADO.NET.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Spin" <Spin@invalid.com> wrote in message
news:6iprqjFs7bmnU1@mid.individual.net...
> Gurus,
>
> Running SQL Server 2005 SP2 with a client app that is using ODBC. On
> client machines, is there a secret that you use to generally try to "speed
> up" or make more efficient client connections to the database?
>
> --
> Spin

Dan, I take an ODBC connection will not use connection pooling by default,
but if you go ahead and set it on the client, and given that not many other
clients connect to the DB anyway, to one will see a performance improvement
on the client?

> Dan, I take an ODBC connection will not use connection pooling by default,
> but if you go ahead and set it on the client, and given that not many
> other clients connect to the DB anyway, to one will see a performance
> improvement on the client?

Actually, I believe pooling is enabled by default with later ODBC drivers.
You can double-check the configuration by double-clicking on the driver name
under the Connection Pooling table of the ODBC Administrator.

If pooling is enabled, can you elaborate on how slow is slow in your
environment?

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Spin" <Spin@invalid.com> wrote in message
news:6ipv0nFrv2i3U1@mid.individual.net...
> Dan, I take an ODBC connection will not use connection pooling by default,
> but if you go ahead and set it on the client, and given that not many
> other clients connect to the DB anyway, to one will see a performance
> improvement on the client?
>

> 2) Antivirus doing real-time scanning of both the SQL binary folders AND
> of the DB and log file locations (I turned that off)

Well that sure explains the connection slowness ;-) I would expect the
queries impacted as well. I'm glad you identified the cause.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Spin" <Spin@invalid.com> wrote in message
news:6isat3F9tamU1@mid.individual.net...
> Dan, you are quite right. In my Win 2003 configuration, pooling was
> enabled by default. On the SQL Server, the CPU was pegged out. I
> discovered:
>
> 1) both SQL instances running opn the same cluster node (I failed over one
> instance to the other node)
> 2) Antivirus doing real-time scanning of both the SQL binary folders AND
> of the DB and log file locations (I turned that off)
>
> Now, CPU utilization is better and speed faster.

I always post back solutions to my problems because I feel I owe it to this
group that you all me so much. Thanks for confirming on the ODBC
stuff...