By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

sp_fkeys stored procedue is very slow

P: n/a
I am trying to find the tables and columns that depends on 'table1'

sp_fkeys @pktable_name='table1'

and this takes about eight seconds, whereas if I run it for finding all
tables and columns that 'table1' depends on

sp_fkeys @fktable_name='table1'

this only takes a second.

How can I speed up the first stored procedure execution?

May 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(ke****@gmail.com) writes:
I am trying to find the tables and columns that depends on 'table1'

sp_fkeys @pktable_name='table1'

and this takes about eight seconds, whereas if I run it for finding all
tables and columns that 'table1' depends on

sp_fkeys @fktable_name='table1'

this only takes a second.

How can I speed up the first stored procedure execution?


Are there many tables in the database?

I'm afraid there is not that much you could do. You could of course
script the database from the master database, and play around with a
copy of the procedure, to see if you can make it go faster. One idea
there would be to replace the temp tables with table variables. Tracing
the procedure in Profile would of course also be necessary to see where
the time is spent.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 18 '06 #2

P: n/a
I was having the same problem for months, so I decided to modify the
sys.sp_fkeys stored procedure and I found the problem, if you want a
fix, here's my modification

http://www2.keyvez.com:82/?q=node/12

ke****@gmail.com wrote:
I am trying to find the tables and columns that depends on 'table1'

sp_fkeys @pktable_name='table1'

and this takes about eight seconds, whereas if I run it for finding all
tables and columns that 'table1' depends on

sp_fkeys @fktable_name='table1'

this only takes a second.

How can I speed up the first stored procedure execution?


Jun 1 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.