慢SQL查询，如何改进？

This query works perfectly, I think. Other than it is so slow. What am I doing wrong? How can I improve the speed as well at the code in general?

我认为这个查询非常有效。除了这么慢。我究竟做错了什么?如何提高代码的速度?

<sql:query var="UserQuery" dataSource="jdbc/UBWT">
SELECT top 100 
dbo.v_r_system.Name0                                        as PC,
dbo.v_computer_owners_preferred.User_ID                     as UBOCUBID,
dbo.v_r_user.User_Name0                                     as UBID,
dbo.v_r_user.ResourceID                                     as ResID,
dbo.v_r_user.displayName0                                   as FoolName,
dbo.v_r_user.givenName0                                     as FirstName,          
dbo.v_r_user.sn0                                            as LastName,
dbo.v_r_user.mail0                                          as eMail,
dbo.v_computer_owners_preferred.Recent_Login_TimeStamp      as InFor,
dbo.v_r_user.departmentNumber0                              as DeptNum,
dbo.v_r_user.department0                                    as Dept,
dbo.v_r_user.telephoneNumber0                               as Phone
FROM        
dbo.v_r_system
inner join dbo.v_computer_owners_preferred on dbo.v_r_system.name0 = dbo.v_computer_owners_preferred.computer_name
inner join dbo.v_r_user on dbo.v_computer_owners_preferred.user_id = dbo.v_r_user.unique_user_name0
where dbo.v_r_system.Name0 = ? <sql:param value="${param.ubid}" />
or dbo.v_computer_owners_preferred.User_ID = ? <sql:param value="${param.ubid}" />
or dbo.v_r_user.User_Name0 = ? <sql:param value="${param.ubid}" />
or dbo.v_r_user.displayName0 = ? <sql:param value="${param.ubid}" />
or dbo.v_r_user.telephoneNumber0 = ? <sql:param value="${param.ubid}" />
order by dbo.v_computer_owners_preferred.Recent_Login_TimeStamp desc;

I really dont like all the OR statements. Do I need to convert to some sort of prepared statement?

我真的不喜欢所有的OR声明。我是否需要转换为某种准备好的声明?

4 个解决方案

#1

Use EXPLAIN PLAN to see why your query is slow. If you see TABLE SCAN, eliminate them. Or make the size of the set you scan smaller by rearranging your WHERE clauses.

使用EXPLAIN PLAN查看查询速度慢的原因。如果您看到TABLE SCAN,请将其删除。或者通过重新排列WHERE子句使您扫描的集的大小更小。

Indexes might make a difference. You should have one for every column that appears in a WHERE clause.

索引可能会有所作为。对于WHERE子句中出现的每个列,您应该有一个。

#2

On first glance, nothing wrong with your query and nothing wrong with 'or' either. Speak with the database owners to investigate which columns and combination of columns in the database are indexed. The query optimiser may be resorting to a trundle through the database tables.

乍一看,您的查询没有任何问题,也没有“错误”。与数据库所有者交谈,以调查数据库中的哪些列和列组合已编制索引。查询优化器可能正在通过数据库表求助于滚动。

Rather insipid answer I know but not much more to say.

相当平淡的回答,我知道但不多说。

#3

If the query returns a large number of results without limiting it to 100, that could be the issue you are having.

如果查询返回大量结果而不将其限制为100,则可能是您遇到的问题。

Due to the fact that you have an ORDER BY statement means that if you return 10,000,000 rows with your query, it will order all of them before taking the top 100 and returning them to you, however if you leave that out, it will just return the first 100 results it comes across.

由于您有一个ORDER BY语句意味着如果您使用查询返回10,000,000行,它将在排名前100并将它们返回给您之前对它们进行排序,但是如果您将其保留,则它将返回它遇到的前100个结果。

The only way I can think of getting round that is to get a proper index on the table, or return all the results where Recent_Login_Timestamp is within a reasonable range (i.e. one day if there are many entries).

我能想到的唯一方法就是在表上获得正确的索引,或者返回Recent_Login_Timestamp在合理范围内的所有结果(即如果有很多条目,则返回一天)。

#4

Generally I noted that JSTL SQL taglib is much slower than Java code, because it put all the result set into an HashMap. I think it should be used just for prototyping.

通常我注意到JSTL SQL taglib比Java代码慢得多,因为它将所有结果集放入HashMap中。我认为它应该只用于原型设计。

Anyway you could try to simplify your where-clause in following way and see if it helps:

无论如何,您可以尝试以下列方式简化您的where子句,看看它是否有帮助:

where ? <sql:param value="${param.ubid}" /> in (
    dbo.v_r_system.Name0,
    dbo.v_computer_owners_preferred.User_ID,
    dbo.v_r_user.User_Name0,
    dbo.v_r_user.displayName0,
    dbo.v_r_user.telephoneNumber0
    )

#1