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?

(keyvez@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, esquel@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

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

keyvez@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?