• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Help with sp_executeSQL calling a stored procedure with multiple parameters

Hi experts,

The attached code loops through a temporary table and runs a complex validation procedure on each row.

I am looking at ways this could be speeded up and am investigating sp_executeSQL as opposed to Exec.  I cannot get it to work...the called procedure is still expecting the original parameter names...see the commented line above the sp_executeSQL  call.

I have been really struggling with this, and have looked at BOL.  If anyone can help me untangle it I'd appreciate it.

Colsp-executeSQL.docx
0
colinspurs
Asked:
colinspurs
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
Changing to sp_executesql won't speed it up.

Ways to improve performance:
1) Make ID a primary key on @ProjectTables (i.e., cluster @ProjectTables by ID).
2) Add an index to TEMP_SITES_AND_TABLES on TABLE_NAME that includes project and site_id (not needed if T_S_A_T is clustered on table_name).
0
 
colinspursAuthor Commented:
Thanks Scott.

T_S_A_T is actually a table variable in the original code.

I have updated the script (attached) but there is no discernible difference in performance.

The execution plan suggests a bunch of missing indexes so I'll try those too.

ValidateProc.docx
0
 
colinspursAuthor Commented:
Indexes made no great difference...think I'll park this one on the fact that sp_executeSQL would not help.
0
 
colinspursAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now