如何提高此SQL更新查询的速度？(How to improve the speed of this SQL update query?)

如何提高此SQL更新查询的速度？(How to improve the speed of this SQL update query?)

对不起，这是我第一次使用这个论坛。 显然人们可以编辑我的帖子虽然有用，但已经取出了一些信息。 我会尽量让它更容易理解。

我使用SQL Compact 3.5作为本地数据库。 该程序是用VB.NET编写的。

问题是查询我的一张桌子花了太长时间。

玩家表具有id ， skill ， school ， weight ， starter 。

id是玩家的id skill是玩家的技能等级 school是指向学校桌子id的外键 weight是14种不同的数字之一

我想要做的是为给定学校给定重量的技能最高的玩家设置起始值='真'。 因此，如果一所学校有100名球员，将有14名先发球员，每名球员一名。

玩家表有170,000名玩家，每个玩家有14种不同的权重，每个玩家都属于4500所学校中的一所。

有人在下面评论并表明这一声明似乎在正确的轨道上。 我是新手，还没有实现它。

"UPDATE p " & "SET starter = 'TRUE' " & "FROM player p" & "JOIN (" & "SELECT DISTINCT school, weight, MAX(skill) AS MaxSkill " & "FROM player " & "GROUP BY school, weight" & ") q ON q.school = p.school AND q.weight = p.weight AND q.MaxSkill = p.skill"

Sorry, this is my first time using this forum. Apparently people can edit my post which although helpful, has taken some information out. I will try to make it more understandable.

I am using SQL Compact 3.5 as a local database. The program is written in VB.NET.

The problem is with querying one of my tables that is taking too long.

The player table has, among other things, id, skill, school, weight, starter.

id is the player's id skill is the player's skill level school is a foreign key pointing to the id of the school table weight is one of 14 different numbers

What I am trying to do is set the starter value = 'true' for the player with the highest skill at a given weight for a given school. So if there are 100 players at a school, there will be 14 starters, one for each weight.

The player table has 170,000 players, each having 1 of 14 different weights, and each belongs to 1 of 4500 schools.

Someone commented below and showed this statement which appears to be on the right track. I am a novice and have not gotten it implemented quite yet.

"UPDATE p " & "SET starter = 'TRUE' " & "FROM player p" & "JOIN (" & "SELECT DISTINCT school, weight, MAX(skill) AS MaxSkill " & "FROM player " & "GROUP BY school, weight" & ") q ON q.school = p.school AND q.weight = p.weight AND q.MaxSkill = p.skill"

最满意答案

此更新查询不是一行一行地逐行进行，而是一次完成所有操作：

首先，它为每个school / weight组合收集最高skill 。

然后它将其加入具有匹配的school / weight / skill组合的player ，然后将该player设置为启动器。

UPDATE p SET starter = 'TRUE' FROM player p JOIN ( SELECT school, weight, MAX(skill) AS MaxSkill FROM player GROUP BY school, weight ) maxResults ON maxResults.school = p.school AND maxResults.weight = p.weight AND maxResults.MaxSkill = p.skill

但是，在技能平衡的情况下，所有具有最高技能的球员都将被设置为首发......

Instead of doing a group-by-group, row-by-row approach, this update query does it all at once:

First, it gathers the highest skill for each school / weight combination.

It then joins that to the player that has the matching school / weight / skill combination, and then sets that player to the starter.

UPDATE p SET starter = 'TRUE' FROM player p JOIN ( SELECT school, weight, MAX(skill) AS MaxSkill FROM player GROUP BY school, weight ) maxResults ON maxResults.school = p.school AND maxResults.weight = p.weight AND maxResults.MaxSkill = p.skill

However, in the case of a tie in skill, all players with the highest skill would be set to a starter...