Results 1 to 5 of 5

Thread: exec

  1. #1
    Join Date
    May 2003
    Posts
    43

    exec

    When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
    Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Actually fully qualifying the execute statement is very advisable.

    example: exec databasename.owner.storedprocedure

    use tempdb

    create proc xq12 as
    select * from sysobjects
    go

    xq12

    exec xq12

    exec dbo.xq12

    exec tempdb.dbo.xq12

    But execution plan for all the above are same.

    if you create a procedure with SP as suffix then sql server look for that proc in the master database. So qualify it as much as possible.

  3. #3
    Join Date
    May 2003
    Posts
    43
    Thanks.

    Yeah I use the full name to help with the execution. I was just lazy to put it in my example. We're having some timeout issues and I'm currently leaning toward recompile issues with the stored procedure. I'm just looking at all options. I have rename all the SP to the full name(database.dbo.spname...), made sure all quieries are pulling only the needed fields etc. Do you have any good links I can look at to read up on issues with SP recompiling and how to fix them. I found a few sources but nothing that great.

    Again, thanks for the help

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Inside the procedure add table hints and see the execution plan.

    example:

    create proc x123 as
    select name from table1 where id > 25 (Index=Idx1)

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •