Click here to Skip to main content
14,668,418 members
Rate this:
Please Sign up or sign in to vote.
See more:
-->i have store procedure of search with static tablename:

"select * from customer where @datafield=@valuefield"

in above i pass column name and values... but tablename is static
so in my database haveing 7 table for above query so i create 1 store procedure for each table...


-->but i thinks some different for store procedure of search with dynamically:
if i create single store procedure for all 7 table like this:

"select * from @tblname where @datafield=@valuefield"

where tablename,columnname,valuename all thing given dynamically


my question is "what is affect of above to store procedure of search on website ??
dynamically store procedure of search decrease performance then static table name"

it if on website or database performance ...when i host it
Posted
Updated 10-Nov-13 4:14am
v2
Comments
CHill60 10-Nov-13 9:02am
   
Please use the Improve question link to clarify your question. It looks like the end of it is missing

Rate this:
Please Sign up or sign in to vote.

Solution 1

I think the performance will not degrade much, but you can check comparing the actual time taken when the table name is static and when you have given a table name, column name and column value and check if the time diff is too small then it is not an issue, but u might land in a problem when u have more number of tables, as the search index for the tables in the database will take more time.

My suggestion is to use a separate stored proc for each table and that is the best way of programming. Dont think about code reusability in certain conditions as there are situations where you cannot use the code as all of the code part seems same.

Hope u understand what I mean to say.

Thanks
Ganesh
   
Comments
priyanshbhaliya 10-Nov-13 13:40pm
   
ok thnks
Rate this:
Please Sign up or sign in to vote.

Solution 2

Couple of suggestions.

0) The Evil That is "Select *"[^]
If your table has 100+ columns but you want to display only bunch of columns(say 10), don't use SELECT *. Remember if your table has million rows then you're selecting (million rows * 90 columns) cells which is unnecessary.

1) Don't use queries like calling table, columns dynamically. Agree with Solution 1, surely it'll degrade the performance. Here check this article, surely it's for you now.
The Curse and Blessings of Dynamic SQL[^]

2) Use best practices. Optimize your database.
Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

3) Create separate stored procedure for each SELECT.

SQL Server DO's and DONT's[^]
   
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100