Connection String for "local" connection

I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
using a "localhost" type of connection would be faster than referring to the
server name and thus invoking the DNS lookup. The connection string I am
using as an ASP include is:
Dim Connect
Set Connect = Server.CreateObject("ADODB.Connection")
Connect.open "DRIVER={SQL
Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
ThanksSomething even better would be to use OLEDB instead of ODBC. You can also
use (local) (with the parenthesis) instead of localhost to use named pipes
instead of TCP/IP but I'm not really sure is this will impact the
performance.
Replace localhost with 127.0.0.1 if you want to make really sure that the
DNS lookup has been shortcutted.
Finally, be default, the connections are pooled, so you shouldn't see any
improvement about shortcutting the DNS lookup.
S. L.
"MICHAEL SHIFFMAN" <mettaworks@.verizon.net> wrote in message
news:rSfAd.7050$1U6.2738@.trnddc09...
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string I
> am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
> Thanks
>|||MICHAEL SHIFFMAN (mettaworks@.verizon.net) writes:
> I'm running MSSQL2k on a Win2k box used as a webserver. I imagine that
> using a "localhost" type of connection would be faster than referring to
> the server name and thus invoking the DNS lookup. The connection string
> I am using as an ASP include is:
> Dim Connect
> Set Connect = Server.CreateObject("ADODB.Connection")
> Connect.open "DRIVER={SQL
> Server};SERVER=SERVERName;DATABASE=DBnam
e;UID=USERid;PWD=Password;"
To echo what Sylvain said: change DRIVER=(SQL Server) to Provider=SQLOLEDB.
Changing providers is not entirely painless - some things may broke. But
the default OLEDB-over-ODBC driver is deprecated for SQL Server, and since
it means one extra layer, there is definitely an extra overhead.
The gain in performance you may get by chaning the SERVER parameter is
probably neglible. However, as long as you run SQL Server on the web
server - which is dubious practice - you gain some maintainability by
dropping SERVER altogther. If the server changes name, you don't have
to change the connection string. Then again, it is not unlikely that you
move the SQL Server to a separate box one day - in which case your
question becomes moot.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp