主机时在网站上查询性能 [英] query performance on website when host

问题描述

- >我有使用静态tablename的搜索存储过程：



select * from customer where @ datafield = @valvalfield



以上我传递列名和值...但是tablename是静态的

因此在我的数据库中有7个表用于上面的查询所以我创建了1个存储过程对于每张桌子......





- >但我认为动态搜索的商店程序有些不同：

如果我为所有7个表创建单个商店程序，如下所示：



select * from @tblname where @ datafield = @ valuefield



其中tablename，columnname，valuename动态给出的所有东西





我的问题是上面对网站搜索存储过程的影响是什么？

动态存储搜索程序减少性能然后静态表名



如果在网站或数据库性能...当我主持它

-->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

推荐答案

我认为性能不会降低很多，但你可以检查比较表名静态和给出表名，列名的实际时间和列值并检查时间差异是否太小然后它不是问题，但是当你有更多数量的表时，你可能会遇到问题，因为数据库中表格的搜索索引将花费更多时间。



我的建议是为每个表使用单独的存储过程，这是最好的编程方式。不要考虑某些条件下的代码可重用性，因为有些情况下你不能使用代码，因为所有的代码部分看起来都是一样的。



希望你理解我的意思说。



谢谢

Ganesh

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

一些建议。



0） The Evil ThatSelect * [ ^ ]

如果你的表有100多列，但你想只显示一堆列（比如10），不要使用 SELECT * 。请记住，如果您的表有百万行，那么您选择（百万行* 90列）单元格是不必要的。



1）不要使用像调用表这样的查询，列动态。同意解决方案1 ​​，肯定会降低性能。在这里查看这篇文章，肯定是给你的。

动态SQL的诅咒与祝福 [ ^ ]



2）使用最佳实践。优化您的数据库。

在SQL Server中优化数据访问的十大步骤：第一部分（使用索引） [ ^ ]

在SQL Server中优化数据访问的十大步骤：第二部分（重新考虑TSQL和应用最佳做法） [ ^ ]

在SQL Server中优化数据访问的十大步骤：第三部分（应用高级索引和非规范化） [ ^ ]

优化SQL Server中数据访问的十大步骤：第四部分（诊断数据库性能问题） [ ^ ]

在SQL Server中优化数据访问的十大步骤：第五部分（优化数据库文件并应用分区） [ ^ ]



3）为每个 SELECT创建单独的存储过程。



SQL Server DO和DONT [ ^ ]

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[^]

这篇关于主机时在网站上查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！