提高查询速度

发布于 2024-12-12 06:09:12 字数 503 浏览 0 评论 0原文

好的，我有表格：

ID Name    Description   Picture
1  Alex     Alex desc..     2
2  Maria      NULL          3
3  John      John desc..   NULL

表格图片有 ID 和 varbinary 图像。

我需要选择：如果描述存在，则描述，否则图片我这样做：

select Id,
       Name, 
       Case when Description is null then pic.Image else Description 
      from person per join picture pic on per.Picture = pic.Id

所以，如果描述不为空，看起来像是不必要的连接。无论如何。关于改进这个简单查询有什么建议吗？另外，有哪些易于使用的好工具来比较两个版本的查询的性能？

原文

Okay I have table:

ID Name    Description   Picture
1  Alex     Alex desc..     2
2  Maria      NULL          3
3  John      John desc..   NULL

table picture has ID and varbinary image.

I need to select: If description exists, then description, else picture
I do this:

select Id,
       Name, 
       Case when Description is null then pic.Image else Description 
      from person per join picture pic on per.Picture = pic.Id

So, looks like unnecessary join if description is not null.
Anyways. any suggestions on improving this simple query? also, What are good easy to use tools for performance comparison between two version of queries?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鸵鸟症 2024-12-19 06:09:12

据我所知，没有办法在单个查询中进行条件连接。我可能会将您的内容重写为：

SELECT per.ID, per.Name, COALESCE(per.Description, pic.Image) as desc
FROM Person as per
LEFT JOIN Picture as pic
ON per.picture = pic.ID

我不确定性能比较如何，但看起来更干净一些。

就比较查询而言，我建议查看两者的执行计划，看看其中一个是否正在进行任何扫描。以下是有关使用执行计划进行性能调整的一些信息：

您还可以使用 Database Tuning Advisor 作为检查是否拥有必要索引的快速方法。

请注意不要花费太多时间进行优化。虽然关注当前性能和数据库增长时的性能固然很好，但很容易在这方面花费大量时间而得不到太多回报。如果您有良好的数据库结构和编写良好的查询，那么未来的优化应该不会太痛苦。

As far as I know there is no way to conditionally join within a single query. I would probably rewrite what you have as:

SELECT per.ID, per.Name, COALESCE(per.Description, pic.Image) as desc
FROM Person as per
LEFT JOIN Picture as pic
ON per.picture = pic.ID

I'm not sure how the performance compares, but it seems a little cleaner.

As far as comparing queries goes, I would recommend looking at the execution plan for both and seeing if either are doing any scans. Here is some info on using execution plans for performance tuning:

You can also use the Database Tuning Advisor as a quick way to check to see if you have the necessary indexes.

Be careful to not spend too much time over optimizing either. While it's good to keep an eye on both current performance and performance as your database grows, it's easy to spend a lot of time on this without getting too much in return. If you have a good database structure and well written queries, then future optimizations shouldn't be too much of a pain.

回复收藏 0 原文

揪着可爱 2024-12-19 06:09:12

但是如果描述不为空，则连接是必要的......这就是您的代码应该做的对吗？

您可以通过用一对“select getdate()”语句包装查询并进行数学计算（或编写 SQL 来为您进行数学计算）来粗略计算查询的时间。

回复收藏 0 原文

筱武穆 2024-12-19 06:09:12

您也可以这样做：

 select Id, Name, Description
 from person
 where description is not null
 union all
 select Id, Name, pic.image
 from person per 
 join picture pic on per.Picture = pic.Id
 where description is null

不确定性能如何，但您只在需要时才进行连接，但代价是使用 UNION。 Union All 将消除对不同行进行排序的需要，这应该有助于提高性能。

You could also do something like this:

 select Id, Name, Description
 from person
 where description is not null
 union all
 select Id, Name, pic.image
 from person per 
 join picture pic on per.Picture = pic.Id
 where description is null

Not sure how performance would be, but you are only doing the join if needed, but at the cost of a UNION. Union All will eliminate the need for sorting out distinct rows, which should help performance.

回复收藏 0 原文

~没有更多了~