SQL查询优化 [英] Sql Query Optimization

问题描述

大家早上好，

我一直在进行一个查询，该查询将两台服务器连接起来以获取所需的数据.问题是我当前运行查询的方式，我的网站超时花了很长时间.我浏览了一些网站，其中一些网站看起来很有希望，但未能提供我需要的结果.我将在下面附加查询.

Good Morning everyone,

I''ve been working on a query that connects 2 servers for the data I want. the issue is the way I''m currently running the query, it takes so long my website times out. I''ve been browsing a few sites and have had some that seemed promising but failed to deliver the results I needed. I will attach the query below.

select distinct isnull(t2.position_id,'') + '' + t2.last_name as [certifier name], t1.order_number as [order number]
from shop.dbo.orders t1, shop.dbo.managers t2
where t2.manager_ssn in (select t1.manager_ssn_id from shop.dbo.orders) and
t1.order_number collate database_default not in (select substring(t3.leafname,0,6) from server2.wsscontent.dbo.alldocs t3 where t3.dirname like 'intranet/supporting documents/%') and
t1.manager_ssn_id = ''
order by t1.order_number

关于此查询的更多信息，"collat​​e database_default"用于解决两个服务器之间的排序规则问题.此查询运行2分钟以上，我需要运行它不超过5秒.我已经尝试过许多不同的方式来编写此查询，但是没有运气，因此我们将不胜感激.如果您需要更多信息，请让我知道.

Just a little more information on this query, the "collate database_default" was to solve a problem with the collation between the two servers. this query runs over 2 minutes, and I need it to run for no more than 5 seconds. I''ve tried many different ways of writing this query with no luck so any assistance will be greatly appreciated. if you need anymore information please let me know.

推荐答案

查询提示可以加快查询速度

您可以使用此:

http://msdn.microsoft.com/en-us/library/ms181714.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ms187713.aspx [ ^ ]

Query hints can speed up query

you can use this:

http://msdn.microsoft.com/en-us/library/ms181714.aspx[^]

http://msdn.microsoft.com/en-us/library/ms187713.aspx[^]

这篇关于SQL查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！