我有我的查询,他们使用100%的CPU。我试图修复它们,但我不是数据库专家,它真的越来越糟。
首先,我的脚本执行这个查询。
SELECT *
FROM
(SELECT
[e].[event_id],
[e].[event_name],
[e].[event_datetime],
[v].[name] [venue_name],
[v].[city],
[s].[state_name],
[v].[venue_id],
ROW_NUMBER() OVER (ORDER BY [event_datetime]) AS RowNum
FROM
[indux].[dbo].[event] [e]
JOIN
[indux].[dbo].[venue] [v] ON [e].[venue_id] = [v].[venue_id]
JOIN
[indux].[dbo].[system_state] [s] ON [v].[state_id] = [s].[state_id]
WHERE
[e].[event_id] IN (SELECT DISTINCT event_id
FROM [indux].[dbo].ticket_group
WHERE ticket_group_id IN (SELECT DISTINCT ticket_group_id
FROM [indux].[dbo].[ticket] [t]
WHERE [t].[actual_sold_price] = -1)
)
AND [e].[event_datetime] >= '2014/08/09'
AND [e].[event_datetime] <= '2014/12/09'
) AS MyDerivedTable
WHERE
MyDerivedTable.RowNum BETWEEN 0 AND 250
在同一时间,我执行以下查询,然后我结合两者的结果显示在我的页面使用PHP
SELECT
COUNT([e].[event_id])
FROM
[indux].[dbo].[event] [e]
WHERE
[e].[event_id] IN (SELECT DISTINCT event_id
FROM [indux].[dbo].ticket_group
WHERE ticket_group_id IN
(SELECT DISTINCT ticket_group_id
FROM [indux].[dbo].[ticket] [t]
WHERE [t].[actual_sold_price] = -1)
)
AND [e].[event_datetime] >= '$today'
AND [e].[event_datetime] <= '$to'
任何人有任何想法,如果我可以修复这些查询?我认为第二个查询中的计数是一个问题。
您需要在票证组ID上连接票证组和票证,并将其连接到事件ID上的主查询。
嵌套查询对其where子句嵌套的查询的每一行运行一次。
嵌套两次就像在循环中运行循环一样,整个东西必须运行一次,并且每次查询返回一行时都要与值进行比较。很明显,从你的选择,你可以连接这些值。
SELECT *Whatever*
FROM [indux].[dbo].[event] [e]
JOIN [indux].[dbo].[venue] [v]
ON [e].[venue_id] = [v].[venue_id]
JOIN [indux].[dbo].[system_state] [s]
ON [v].[state_id] = [s].[state_id]
JOIN indux.dbo.ticket_group tg on tg.event_id=r.event_id
JOIN indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id
WHERE t.actual_sold_price=-1
SELECT COUNT(e.event_id)
FROM dbo.event e
JOIN indux.dbo.ticket_group tg on tg.event_id=e.event_id
JOIN indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id
WHERE e.event_datetime >= '$today'
AND e.event_datetime <= '$to'
修改
SELECT *Whatever*
FROM [indux].[dbo].[event] [e]
JOIN [indux].[dbo].[venue] [v]
ON [e].[venue_id] = [v].[venue_id]
JOIN [indux].[dbo].[system_state] [s]
ON [v].[state_id] = [s].[state_id]
JOIN (
SELECT DISTINCT tg.event_id
FROM indux.dbo.ticket_group tg
JOIN indux.dbo.ticket t on t.ticket_group_id=tg.ticket_group_id
WHERE actual_sold_price = -1) de
on de.Event_id=e.event_id
使用临时表如
CREATE TABLE #EventID (id int)
INSERT INTO #EventID SELECT tg.event_id etc...
将允许您重用表,而不是重新查询count语句。