Making sure we can see multiples in the same cell, and other fixes…
Multiples in the same cell
Previously, on Day 5:
First let’s make sure we have the unique index for each wolf:
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.
|
|
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.
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.
|
|
Does it work?

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:
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;
}
}
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:
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);
// }
