如何提高这个 SQL 更新查询的速度? [英] How to improve the speed of this SQL update query?

问题描述

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

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.

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

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.

玩家表有id、skill、school、weight、>入门.

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

• 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

我想要做的是为给定学校的给定重量下具有最高技能的玩家设置 starter 值 = 'true'.因此，如果一所学校有 100 名球员，那么将有 14 名首发球员，每个体重一名.

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.

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

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"

推荐答案

此更新查询不是按组、按行执行方法，而是一次性完成所有操作:

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

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

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

然后将其与具有匹配的 school/weight/skill 组合的 player 结合，并且然后将该 player 设置为 starter.

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...

这篇关于如何提高这个 SQL 更新查询的速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！