Day 6 - Crowd Control

Making sure we can see multiples in the same cell, and other fixes…

Multiples in the same cell

Previously, on Day 5:

If you watch patiently you will see that on the left side there are actually two (or more) wolves, but they overlap each other most of the time and appear as one. This clearly cannot stand, so let’s increase the granularity by shrinking them down and positioning them into a grid within the cell. At the same time we’ll start keeping and moving the wolf transforms, instead of recreating them each round.

First let’s make sure we have the unique index for each wolf:

GetWolves GitLab

90
91
92
93
94
95
96


NPC wolf = new NPC();
wolf.worldNpcID = Convert.ToInt32(dr["NPCIndex"]); // unique ID
wolf.Name = dr["name"].ToString();
wolf.X = Convert.ToInt16(dr["xCord"]);
wolf.Y = Convert.ToInt16(dr["yCord"]);
wolf.Z = Convert.ToInt16(dr["zCord"]);
npclist.Add(wolf);

We’ll put together a couple of dictionaries to map NPC IDs to their locations, and their transforms. This is begging to be a more interesting data structure, but we’ll figure that out later.

MapManager GitLab

40
41


private Dictionary<int,Transform> wolfTransforms;
private Dictionary<int, Vector3> wolfLocations;

While we go through the wolves, we’ll only create a new object if it’s an ID we haven’t seen before. If we’ve already seen it, we’ll just move that transform. At the same time we’ll keep track of which cells contain wolves and which IDs they contain.

MapManager.Update GitLab

173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191


var cellsContainingWolves = new Dictionary<Vector3,List<int>>();
foreach (NPC wolf in wolves) {
    Vector3 cell = new Vector3(wolf.X, wolf.Y, wolf.Z);
    wolfLocations[wolf.worldNpcID] = cell;
    if (!cellsContainingWolves.ContainsKey(cell)) 
        cellsContainingWolves[cell] = new List<int>();
    cellsContainingWolves[cell].Add(wolf.worldNpcID);
    Vector3 position = new Vector3((-1f) * wolf.X, (-1f) * wolf.Y, (0.1f * wolf.Z) + 0.2f);
    if (wolfTransforms.ContainsKey(wolf.worldNpcID)) {
        print("Moving wolf " + wolf.worldNpcID + " to " + position);
        wolfTransforms[wolf.worldNpcID].position = position;
    } else {
        print("Creating wolf at " + position);
        Transform tempts = Instantiate(wolfPrefab, 
                                       position,
                                       Quaternion.identity) as Transform;
        wolfTransforms[wolf.worldNpcID] = tempts;
    }
}

Then we can go through those cells to scale and arrange the wolves. We want to keep them square, so we’ll put them in the smallest x-by-x grid that will hold them all.

MapManager.Update GitLab

193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209


foreach (Vector3 cell in cellsContainingWolves.Keys) {
    int population = cellsContainingWolves[cell].Count;
    print("Cell " + cell + " has " + population + " wolves!");
    int dimension = (int) Math.Ceiling(Math.Sqrt(population));
    print("We can fit those in a " + dimension + "x" + dimension + " grid.");
    int row = 0, column = 0;
    foreach (int wolfID in cellsContainingWolves[cell]) {
        print("Wolf " + row + "," + column + " is " + wolfID);
        wolfTransforms[wolfID].localScale = new Vector3(1.0f/dimension, 1.0f/dimension, wolfTransforms[wolfID].localScale.z);
        Vector3 position = new Vector3((-1f) * cell.x, (-1f) * cell.y, (0.1f * cell.z) + 0.2f); // start at the center of the cell
        position += new Vector3(0.5f, 0.5f, 0f); // move to corner
        wolfTransforms[wolfID].position = position - new Vector3(column * (1.0f/dimension), row * (1.0f/dimension), 0f);
        wolfTransforms[wolfID].position -= new Vector3((1.0f/dimension)/2f, (1.0f/dimension)/2f, 0f);
        column += 1;
        if (column >= dimension) { column = 0; row += 1; }
    }
}

Does it work?

No more hidden wolves

One issue down, three to go.

Stop hammering the DB server with NPC location updates

The quick-and-dirty way we implemented the NPC location update, we open a new connection to the database every time an NPC finishes its round event. At 1750 NPCs and 5-second rounds, even though we dispose of the connection as soon as we’re done, we wind up with a bunch of extra processes on the SQL server: Lots of processes on the SQL server

So let’s try keeping a list of NPCs to update, and only flush it to the database when the round changes:

DAL.DBNPC GitLab

 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132


internal static List<NPC> npcsToUpdate = new List<NPC>();
internal static int roundToUpdate = 0;
internal static object lockObjectNpcLocationUpdate = new object();

internal static int SaveNPCLocation(NPC npc) {
    int result = 0;
    lock (lockObjectNpcLocationUpdate) {
        if (DragonsSpineMain.GameRound != DAL.DBNPC.roundToUpdate) {
            try {
                String sptouse = "prApp_NPCLocation_Update";
                using (SqlConnection tempConnection = DataAccess.GetSQLConnection()) {
                    Utils.Log("Round " + DragonsSpineMain.GameRound + ": Inserting " + DAL.DBNPC.npcsToUpdate.Count + " NPC locations.", Utils.LogType.Unknown);
                    foreach (NPC currNpc in DAL.DBNPC.npcsToUpdate) {
                        using (SqlStoredProcedure sp = new SqlStoredProcedure(sptouse, tempConnection)) {
                            int NPCIndex = Character.NPCList.IndexOf(currNpc);
                            sp.AddParameter("@NPCIndex", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.worldNpcID);
                            sp.AddParameter("@active", SqlDbType.Int, 4, ParameterDirection.Input, DAL.DBNPC.roundToUpdate);
                            sp.AddParameter("@name", SqlDbType.NVarChar, 255, ParameterDirection.Input, currNpc.Name);
                            sp.AddParameter("@facet", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.FacetID);
                            sp.AddParameter("@land", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.LandID);
                            sp.AddParameter("@map", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.MapID);
                            sp.AddParameter("@xCord", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.X);
                            sp.AddParameter("@yCord", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.Y);
                            sp.AddParameter("@zCord", SqlDbType.Int, 4, ParameterDirection.Input, currNpc.Z);
                            result = sp.ExecuteNonQuery();
                        }
                    }
                    DAL.DBNPC.npcsToUpdate.Clear();
                    DAL.DBNPC.roundToUpdate = DragonsSpineMain.GameRound;
                }
            } catch (Exception e) {
                Utils.LogException(e);
                result = -1;
            }
        }
        npcsToUpdate.Add(npc);
        return result;
    }
}

Since multiple threads are running NPC events, we need to lock. We flush the NPC list to the DB when the round changes, and always add the NPC whose round event just ended to the list. (My first pass at this had a bug where the first NPC in a new round wouldn’t have its location recorded, oops! Check the commit history if you’re curious.)

Does it work? Only a few processes on the SQL server Only a few processes, and we can see from the logs that we’re only writing once per round. Two issues down, two to go.

See what I did there?

We’ll attack the other two issues tomorrow, but just for fun let’s remove just one conditional and see the results:

DAL.DBNPC.GetWolves

1
2
3
4
5
6
7
8
9


// if (dr["name"].ToString() == "wolf") {
    NPC wolf = new NPC();
    wolf.worldNpcID = Convert.ToInt32(dr["NPCIndex"]);
    wolf.Name = dr["name"].ToString();
    wolf.X = Convert.ToInt16(dr["xCord"]);
    wolf.Y = Convert.ToInt16(dr["yCord"]);
    wolf.Z = Convert.ToInt16(dr["zCord"]);
    npclist.Add(wolf);
// }

We are all wolves.

Day 6 code - client

Day 6 code - server

301 Days (redux)

Another metric leap year of gamedev experiments and such

Day 6 - Crowd Control

Multiples in the same cell

Stop hammering the DB server with NPC location updates

See what I did there?