0

I have a SQl Query which returns 30,000+ records, with 15 columns . I am passing a NVARCHR(50) parameter for the store procedure.

At the moment I am using stored procedure to get the data from the database.

As there are 30,000+ records to be fetched and its taking time, What would be the suggestions for me.

Do I get any performance benefits if I use functions with in the stored procedure(to get individual columns based on the parameter I am passing)

Please let me know, if you need more info on the same.

Thank you

Hari Gillala
  • 11,736
  • 18
  • 70
  • 117
  • may be its help u.
    [http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server][1] [1]: http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server     – 4b0 Sep 22 '11 at 09:50
  • 1
    Not really clear what you are asking or what the proposed functions would do but generally these won't cause any performance benefit whatsoever and may well cause performance problems. – Martin Smith Sep 22 '11 at 10:00

2 Answers2

0

Functions would probably not be the way to go. 30000 rows isn't that many, depending on how conplex the query is. You would be better to focus on optimising the SQL in the proc, or on checking that your indexing is setup correctly.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
0

I wouldn't use functions unless there is no other way to get your data.

From SQL2005 you have extra functionality in stored procedures such as WITH and CROSS APPLY clauses that makes easier certain restrictions we had in previous versions of SQL that could be solved using UDF's.

In terms of performance, the stored procedure will generally be quicker, but it depends how optimized is your query and/or how the tables have been designed, maybe you could give us an example of what you are trying to achieve.

David Aleu
  • 3,922
  • 3
  • 27
  • 48