dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: why this is a bad idea

  1. #1
    Igor Guest

    why this is a bad idea


    Hi everybody,
    Few months ago one of the postings sad that it is not very good way to open
    a recordset like that

    rst.open "exec sp_SelectSomething prmtr1, prmtr2", oConn ...

    where SelectSomething is stored procedure, prmtr1 and prmtr2 are parameters
    and oConn is Connection.
    Unfortunately i could not follow up that message. Can you please explain
    it to me, because i use it all over the place and do not have any problem
    with it. May be it is a performance issue?
    Thanks
    Igor.


  2. #2
    jy Guest

    Re: why this is a bad idea


    "Igor" <irodionov@hotmail.com> wrote:
    >
    >Hi everybody,
    >Few months ago one of the postings sad that it is not very good way to open
    >a recordset like that
    >
    >rst.open "exec sp_SelectSomething prmtr1, prmtr2", oConn ...
    >
    >where SelectSomething is stored procedure, prmtr1 and prmtr2 are parameters
    >and oConn is Connection.
    >Unfortunately i could not follow up that message. Can you please explain
    >it to me, because i use it all over the place and do not have any problem
    >with it. May be it is a performance issue?
    >Thanks
    >Igor.
    >

    i think it's partly performance, since the sql string has to be parsed before
    execution; also (as far as i can tell) you cannot change the timeout period
    from the default (30s?) so long-running queries will probably fail. if it
    ain't broke, don't fix it - otherwise, use a command object :-) . J-Y.

  3. #3
    Will Rickards Guest

    Re: why this is a bad idea

    I believe the timeout can be changed on the connection object itself. (CommandTimeout property)
    The command object also has this property.

    How would that code work? Does the return type of the stored procedure have to be a cursor?
    What are the alternatives? Using the Connection.Execute method?

    Maybe it is a bad idea due to cursor type conflicts? You request a certain type with the Open
    call but the stored procedure controls the actual cursor returned?

    Maybe it encounters the same performance hit that a Refresh call (Command objects parameters)
    incurs. In parsing the exec statement maybe ADO requests the parameter information from the
    DB server to do some type/count checking on the parameter list.

    I really have no idea why it is a bad idea.

    --
    Will Rickards

    "jy" <jy@directdialog.com> wrote in message news:3a30cb42$1@news.devx.com...
    >
    > "Igor" <irodionov@hotmail.com> wrote:
    > >
    > >Hi everybody,
    > >Few months ago one of the postings sad that it is not very good way to open
    > >a recordset like that
    > >
    > >rst.open "exec sp_SelectSomething prmtr1, prmtr2", oConn ...
    > >
    > >where SelectSomething is stored procedure, prmtr1 and prmtr2 are parameters
    > >and oConn is Connection.
    > >Unfortunately i could not follow up that message. Can you please explain
    > >it to me, because i use it all over the place and do not have any problem
    > >with it. May be it is a performance issue?
    > >Thanks
    > >Igor.
    > >

    > i think it's partly performance, since the sql string has to be parsed before
    > execution; also (as far as i can tell) you cannot change the timeout period
    > from the default (30s?) so long-running queries will probably fail. if it
    > ain't broke, don't fix it - otherwise, use a command object :-) . J-Y.




  4. #4
    ian drake Guest

    Re: why this is a bad idea


    Igor -

    When using a recordset like this...
    >rst.open "exec sp_SelectSomething prmtr1, prmtr2", oConn ...

    you are not sending the command as a SP call. It has to be parsed. With
    a command object you can specify the command type, which means less parsing.


    Ian Drake

    PS. This is just from what I have read. Best practice is to use the command
    object, use the Parameters.Add method, and set the command type property
    to adStoredProc.

  5. #5
    Arthur Wood Guest

    Re: why this is a bad idea


    Igor,
    This is quite coincidental, but if you subscribe to VB Programmers Journal,
    there is an article in the current issue (Just received yesterday) on this
    very subject. and Rs.open is one of the valid methods which is discussed.
    If that is the case, then it seems that it must not be considered BAD by
    everyone.

    You can also access the current issue on-line at http://www.vbpj.com (though
    this still shows the December 200 issue, and the current mailed issue is
    Jan 2001, so you might check the site in a couple of days for the Jan 2001
    issue).

    Arthur Wood

    "Igor" <irodionov@hotmail.com> wrote:
    >
    >Hi everybody,
    >Few months ago one of the postings sad that it is not very good way to open
    >a recordset like that
    >
    >rst.open "exec sp_SelectSomething prmtr1, prmtr2", oConn ...
    >
    >where SelectSomething is stored procedure, prmtr1 and prmtr2 are parameters
    >and oConn is Connection.
    >Unfortunately i could not follow up that message. Can you please explain
    >it to me, because i use it all over the place and do not have any problem
    >with it. May be it is a performance issue?
    >Thanks
    >Igor.
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center