performance difference between DSN and DSN-less?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Alistair

    performance difference between DSN and DSN-less?

    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

    #2
    Re: performance difference between DSN and DSN-less?

    What is the problem?

    Show both connection strings.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning



    "Alistair" <forget_it> wrote in message
    news:1060mb81r8 b28b5@corp.supe rnews.com...[color=blue]
    > 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
    >
    >[/color]


    Comment

    • Alistair

      #3
      Re: performance difference between DSN and DSN-less?


      "Mark Schupp" <mschupp@ielear ning.com> wrote in message
      news:%2377DF5OE EHA.1452@TK2MSF TNGP09.phx.gbl. ..[color=blue]
      > 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:1060mb81r8 b28b5@corp.supe rnews.com...[color=green]
      > > is there a performance difference between using a DSN and a DSN-less[/color][/color]
      (Jet)[color=blue][color=green]
      > > connection?
      > >
      > > I have a shopping cart app that has a problem when working with a[/color][/color]
      DSN-less[color=blue][color=green]
      > > 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,[/color][/color]
      but[color=blue][color=green]
      > > just thought someone might know a little bit more about this.
      > >
      > > thanks
      > >
      > >[/color]
      >[/color]

      ' Jet Oledb connection
      ' DatabaseConnect ionString = "Provider=Micro soft.Jet.OLEDB. 4.0;" & "Data
      Source="&server .mappath("../database/155ads1q.mdb")

      ' DSN connection,
      ' DatabaseConnect ionString = "DSN=155ads1q.d sn"

      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& "','" &pCustomerCompa ny& "','" &pEmail& "','" &pCity& "',
      '" &pCountryCod e& "','" &pPhone& "','" &pAddress& "','" &pZip& "','"
      &pPassword& "','" &pState& "','" &pStateCode& "',1,-1,'" &pUser1& "','"
      &pUser2& "','" &pUser3& "',0)"

      call updateDatabase( mySQL, rsTemp, "customerRegist rationExec")

      ' 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..the n 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.


      Comment

      • Peter Foti

        #4
        Re: performance difference between DSN and DSN-less?

        "Alistair" <forget_it> wrote in message
        news:1060mb81r8 b28b5@corp.supe rnews.com...[color=blue]
        > is there a performance difference between using a DSN and a DSN-less (Jet)
        > connection?[/color]

        DSN-less connections are better. See this article for more details:


        [color=blue]
        > 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.[/color]

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

        Regards,
        Peter Foti


        Comment

        • Bob Barrows [MVP]

          #5
          Re: performance difference between DSN and DSN-less?

          Alistair wrote:[color=blue][color=green]
          >>[/color]
          >
          > ' Jet Oledb connection
          > ' DatabaseConnect ionString = "Provider=Micro soft.Jet.OLEDB. 4.0;" &
          > "Data Source="&server .mappath("../database/155ads1q.mdb")
          >
          > ' DSN connection,
          > ' DatabaseConnect ionString = "DSN=155ads1q.d sn"
          >
          > 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& "','" &pCustomerCompa ny& "','" &pEmail&
          > "','" &pCity& "', '" &pCountryCod e& "','" &pPhone& "','" &pAddress&
          > "','" &pZip& "','" &pPassword& "','" &pState& "','" &pStateCode&
          > "',1,-1,'" &pUser1& "','" &pUser2& "','" &pUser3& "',0)"
          >
          > call updateDatabase( mySQL, rsTemp, "customerRegist rationExec")
          >
          > ' 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..[/color]

          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:


          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.


          Comment

          • Alistair

            #6
            Re: performance difference between DSN and DSN-less?


            "Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
            news:eqq0DPPEEH A.2968@TK2MSFTN GP12.phx.gbl...[color=blue]
            > Alistair wrote:[color=green][color=darkred]
            > >>[/color]
            > >
            > > ' Jet Oledb connection
            > > ' DatabaseConnect ionString = "Provider=Micro soft.Jet.OLEDB. 4.0;" &
            > > "Data Source="&server .mappath("../database/155ads1q.mdb")
            > >
            > > ' DSN connection,
            > > ' DatabaseConnect ionString = "DSN=155ads1q.d sn"
            > >
            > > 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& "','" &pCustomerCompa ny& "','" &pEmail&
            > > "','" &pCity& "', '" &pCountryCod e& "','" &pPhone& "','" &pAddress&
            > > "','" &pZip& "','" &pPassword& "','" &pState& "','" &pStateCode&
            > > "',1,-1,'" &pUser1& "','" &pUser2& "','" &pUser3& "',0)"
            > >
            > > call updateDatabase( mySQL, rsTemp, "customerRegist rationExec")
            > >
            > > ' 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..[/color]
            >
            > 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[/color]
            a[color=blue]
            > 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[/color]
            of[color=blue]
            > 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[/color]
            your[color=blue]
            > columns:
            > http://www.aspfaq.com/show.asp?id=2080
            >
            > Why are you storing both State and StateCode in this table? Seems[/color]
            redundant[color=blue]
            > 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.
            >
            >[/color]

            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.


            Comment

            • dlbjr

              #7
              Re: performance difference between DSN and DSN-less?

              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.


              Comment

              • Bob Barrows [MVP]

                #8
                Re: performance difference between DSN and DSN-less?

                Alistair wrote:[color=blue]
                >
                > 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??
                >[/color]

                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 "CustFullNa me" 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.


                Comment

                • Ravichandran J.V.

                  #9
                  Re: performance difference between DSN and DSN-less?

                  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.Odb c
                  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.Ravichandra n
                  - http://www.geocities.com/
                  jvravichandran
                  - http://www.411asp.net/func/search?
                  qry=Ravichandra n+J.V.&cob=aspn etpro
                  - 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.Ravichandr an"
                  at http://www.Google.com

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

                  Comment

                  • Nicolas

                    #10
                    Re: performance difference between DSN and DSN-less?

                    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("<fie ldname>") = 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("idCu stomer") 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


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

                    Comment

                    • Bob Barrows

                      #11
                      Re: performance difference between DSN and DSN-less?

                      Nicolas wrote:[color=blue]
                      > 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("<fie ldname>") = xyz to set the values you have
                      > available
                      > 6) Use rs.Update to save the record
                      >[/color]


                      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"


                      Comment

                      • Bob Barrows

                        #12
                        Re: performance difference between DSN and DSN-less?

                        Ravichandran J.V. wrote:[color=blue]
                        > 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.Odb c[/color]

                        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"


                        Comment

                        • Alistair

                          #13
                          Re: performance difference between DSN and DSN-less?


                          "Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
                          news:e9AjnlZEEH A.3788@TK2MSFTN GP10.phx.gbl...[color=blue]
                          > Ravichandran J.V. wrote:[color=green]
                          > > 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.Odb c[/color]
                          >
                          > 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"
                          >
                          >[/color]

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


                          Comment

                          Working...