performance difference between DSN and DSN-less?

[Alistair]

is there a performance difference between using a DSN and a DSN-less (Jet)
connection?

I have a shopping cart app that has a problem when working with a DSN-less
connection, as soon as I change to a DSN, problem disappears.

if it would help to show the script..I think I can copy and paste it, but
just thought someone might know a little bit more about this.

thanks

[Mark Schupp]

What is the problem?

Show both connection strings.

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Alistair" <forget_it> wrote in message
news:10*************@corp.supernews.com...

is there a performance difference between using a DSN and a DSN-less (Jet)
connection?

I have a shopping cart app that has a problem when working with a DSN-less
connection, as soon as I change to a DSN, problem disappears.

if it would help to show the script..I think I can copy and paste it, but
just thought someone might know a little bit more about this.

thanks

[Alistair]

"Mark Schupp" <ms*****@ielearning.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

What is the problem?

Show both connection strings.

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com
"Alistair" <forget_it> wrote in message
news:10*************@corp.supernews.com...

is there a performance difference between using a DSN and a DSN-less (Jet) connection?

I have a shopping cart app that has a problem when working with a DSN-less connection, as soon as I change to a DSN, problem disappears.

if it would help to show the script..I think I can copy and paste it, but just thought someone might know a little bit more about this.

thanks

' Jet Oledb connection
' DatabaseConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source="&server.mappath("../database/155ads1q.mdb")

' DSN connection,
' DatabaseConnectionString = "DSN=155ads1q.dsn"

The file in question carries out an INSERT into the DB, then reads back the
ID which is an "auto" field.

mySQL="INSERT INTO customers (name, lastName, customerCompany, email, city,
countryCode, phone, address, zip, password, state, stateCode,
idCustomerType, active, user1, user2, user3, bonusPoints) VALUES ('" &pName&
"','" &pLastName& "','" &pCustomerCompany& "','" &pEmail& "','" &pCity& "',
'" &pCountryCode& "','" &pPhone& "','" &pAddress& "','" &pZip& "','"
&pPassword& "','" &pState& "','" &pStateCode& "',1,-1,'" &pUser1& "','"
&pUser2& "','" &pUser3& "',0)"

call updateDatabase(mySQL, rsTemp, "customerRegistrationExec")

' obtain the idCustomer of the new record

mySQL="SELECT idCustomer FROM customers WHERE email='" &pEmail&"'"

but the new record is never inserted and so the select idcustomer bit
fails..

until I change to a DSN connection..then everything works perfectly.

The DSN-less connection works fron for reading the products from the dbase,
and modifying them , and updating them etc...it's just that the new customer
registration fails.

[Peter Foti]

"Alistair" <forget_it> wrote in message
news:10*************@corp.supernews.com...

is there a performance difference between using a DSN and a DSN-less (Jet)
connection?
DSN-less connections are better. See this article for more details:
http://www.aspfaq.com/show.asp?id=2126

I have a shopping cart app that has a problem when working with a DSN-less
connection, as soon as I change to a DSN, problem disappears.

if it would help to show the script..I think I can copy and paste it, but
just thought someone might know a little bit more about this.

Without knowing what the "problem" is or seeing the code, it's hard to
provide any useful feedback.

Regards,
Peter Foti

[Bob Barrows [MVP]]

Alistair wrote:

' Jet Oledb connection
' DatabaseConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source="&server.mappath("../database/155ads1q.mdb")

' DSN connection,
' DatabaseConnectionString = "DSN=155ads1q.dsn"

The file in question carries out an INSERT into the DB, then reads
back the ID which is an "auto" field.

mySQL="INSERT INTO customers (name, lastName, customerCompany, email,
city, countryCode, phone, address, zip, password, state, stateCode,
idCustomerType, active, user1, user2, user3, bonusPoints) VALUES ('"
&pName& "','" &pLastName& "','" &pCustomerCompany& "','" &pEmail&
"','" &pCity& "', '" &pCountryCode& "','" &pPhone& "','" &pAddress&
"','" &pZip& "','" &pPassword& "','" &pState& "','" &pStateCode&
"',1,-1,'" &pUser1& "','" &pUser2& "','" &pUser3& "',0)"

call updateDatabase(mySQL, rsTemp, "customerRegistrationExec")

' obtain the idCustomer of the new record

mySQL="SELECT idCustomer FROM customers WHERE email='" &pEmail&"'"

but the new record is never inserted and so the select idcustomer bit
fails..

No error message? The INSERT simply does not happen?? Do you have an "On
Error Resume Next" statement that is preventing an error message from
occurring?
Oh wait! " .. works with ODBC but not with OLEDB ... " This is very likely a
reserved word issue. Let's see ... Yes, you used "name" and "password" as
column names. These are reserved keywords. If you can't change the names of
the columns, then you will need to remember to surround them with brackets
[] when you use them in a SQL statement:

"...([name], ..., [password], ..."

Here is a list of reserved keywords which should be avoided when naming your
columns:
http://www.aspfaq.com/show.asp?id=2080

Why are you storing both State and StateCode in this table? Seems redundant
to me.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Alistair]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eq**************@TK2MSFTNGP12.phx.gbl...

Alistair wrote:

' Jet Oledb connection
' DatabaseConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source="&server.mappath("../database/155ads1q.mdb")

' DSN connection,
' DatabaseConnectionString = "DSN=155ads1q.dsn"

The file in question carries out an INSERT into the DB, then reads
back the ID which is an "auto" field.

mySQL="INSERT INTO customers (name, lastName, customerCompany, email,
city, countryCode, phone, address, zip, password, state, stateCode,
idCustomerType, active, user1, user2, user3, bonusPoints) VALUES ('"
&pName& "','" &pLastName& "','" &pCustomerCompany& "','" &pEmail&
"','" &pCity& "', '" &pCountryCode& "','" &pPhone& "','" &pAddress&
"','" &pZip& "','" &pPassword& "','" &pState& "','" &pStateCode&
"',1,-1,'" &pUser1& "','" &pUser2& "','" &pUser3& "',0)"

call updateDatabase(mySQL, rsTemp, "customerRegistrationExec")

' obtain the idCustomer of the new record

mySQL="SELECT idCustomer FROM customers WHERE email='" &pEmail&"'"

but the new record is never inserted and so the select idcustomer bit
fails..

No error message? The INSERT simply does not happen?? Do you have an "On
Error Resume Next" statement that is preventing an error message from
occurring?
Oh wait! " .. works with ODBC but not with OLEDB ... " This is very likely

a reserved word issue. Let's see ... Yes, you used "name" and "password" as
column names. These are reserved keywords. If you can't change the names of the columns, then you will need to remember to surround them with brackets
[] when you use them in a SQL statement:

"...([name], ..., [password], ..."

Here is a list of reserved keywords which should be avoided when naming your columns:
http://www.aspfaq.com/show.asp?id=2080

Why are you storing both State and StateCode in this table? Seems redundant to me.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I saw the reserved words and although I cringed a little, I left them.
Still, I would expect it to fail all the time rather than only when using
the oledb string. Don't suppose you'd care to explain this??

thanks again for the help though.

[dlbjr]

As for performance, The OLE DSN-Less connection is best.
The ODBC - DSN adds the ODBC Layer.

David L. Bryant, Jr.

Unambit from meager knowledge of inane others,
engender uncharted sagacity.

[Bob Barrows [MVP]]

Alistair wrote:

I saw the reserved words and although I cringed a little, I left them.
Still, I would expect it to fail all the time rather than only when
using the oledb string. Don't suppose you'd care to explain this??

There are a different set of reserved words for OLEDB. I had a link to the
page on the MSDN site but I can't take the time to dig it up right now. You
can find it the same way I did: by searching msdn.microsoft.com for the
keywords "OLEDB reserved keywords". Also the list might vary depending on
the database.

The best way to avoid using reserved words is to use descriptive names for
your objects. for example, "name" is a horribly descriptive name: "name" of
what? Try to make them specific, as you did for the lastName column. How
about "CustFullName" instead of "name"? I can guarantee that will not be a
reserved keyword.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Ravichandran J.V.]

A DSN connection is supposed to take longer to establish connection
because the server has to look up the Registry each time plus there is
only one Namespace System.Data.Odbc
to serve a DSN connection and resources are very less on the web. The
only advantage is for the Hosting provider who can charge extra money
for Database use.

with regards,
J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Nicolas]

Hello Alistair,

I know it doesn't change the connection issue, but here is (what i think
is) a better way of achieving what you are doing. Sorry it's only in
steps, not actual code...

1) Open you connection
2) Create an ADO recordset called (for instance) "rs"
3) Open the table you want to add to in the recordset (make sure you
open it for updating)
4) Use rs.AddNew to create a new record
5) Use rs.Fields("<fieldname>") = xyz to set the values you have
available
6) Use rs.Update to save the record

Now tha catch is the the cursor has not moved off the new record, so...

7) Use myAutoID = rs.Fields("idCustomer") to read off the autonumber
field
8) Close the recordset
9) Close the connection

If you are looking for more detailed info, look for the update method of
the ADO recordset object on MSDN - the examples will help.

Cheers,
Nicolas
http://www20.brinkster.com/intefacesa/

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Nicolas wrote:

Hello Alistair,

I know it doesn't change the connection issue, but here is (what i
think is) a better way of achieving what you are doing. Sorry it's
only in steps, not actual code...

1) Open you connection
2) Create an ADO recordset called (for instance) "rs"
3) Open the table you want to add to in the recordset (make sure you
open it for updating)
4) Use rs.AddNew to create a new record
5) Use rs.Fields("<fieldname>") = xyz to set the values you have
available
6) Use rs.Update to save the record

There are several good reasons not to do it this way if a better way exists.
In order to provide maximum scalability, sql statements should be used
rather than recordsets. Cursors create much overhead in the form of locks,
memory, network traffic, etc. that is avoided when executing sql statements
instead. For maximum benefit, the sql statements should be encapsulated in
stored procedures.

In a non-Web environment, such as a desktop application, where throughput is
not as much an issue, then it certainly makes sense to take advantage of the
functionality provided by ADO recordsets. But in the web environment, you
need to be very conservative about resource usage.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Ravichandran J.V. wrote:

A DSN connection is supposed to take longer to establish connection
because the server has to look up the Registry each time plus there is
only one Namespace System.Data.Odbc

That may be true, but the registry lookup has a very minor impact on
performance compared to the overhead imposed by using two separate
libraries, the ODBC library AND the OLEDB library to connect to and work
with your database, as opposed to the single OLEDB library used when
connecting via a native OLEDB provider. Moving data across process
boundaries is very expensive.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Alistair]

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:e9**************@TK2MSFTNGP10.phx.gbl...

Ravichandran J.V. wrote:

A DSN connection is supposed to take longer to establish connection
because the server has to look up the Registry each time plus there is
only one Namespace System.Data.Odbc

That may be true, but the registry lookup has a very minor impact on
performance compared to the overhead imposed by using two separate
libraries, the ODBC library AND the OLEDB library to connect to and work
with your database, as opposed to the single OLEDB library used when
connecting via a native OLEDB provider. Moving data across process
boundaries is very expensive.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Many thanks for everyone's help, esp Bob...