Extremely bad C# performance

Unity Engine
1

So i have a fairly big item database saved to a textfile. (As a kind of csv).
I already have a reader in my database editor. Written in C# aswell.

These three examples were performed with the exact same code on the exact same machine:
My C# DB editor project: Takes about 10 seconds to read in the database. (reading + parsing).
My Unity project with the exact same code: About 100 seconds. And this is only parsing. Without reading time.
My Unity project built for pc (just to test the load time): 90 seconds

Kind of not comparable but still noteworthy:
Performance in build (it is a mobile project, so run on my Android): 7 minutes

How can the loading time be so much longer? I mean, i understand that it takes a while on my phone because it’s slower.

But why does it take ten times longer to load in unity than in a standalone c# project?

Note: to enable loading screens in unity while parsing, i parse in a seperate System.Threading.Thread

2

I’d wager a guess that it’s down to GC allocations and collection, since this is likely involving a bunch of string operations. But you should profile that.

1 Like
3

Yes, it is pretty much nothing but string operations.

How do i profile this?
If i run it in it’s own parsing thread the profiler won’t track it and if i run it in the main thread unity just freezes until it finished parsing.

The parser does a lot of string concatenations.
Maybe i should replace the concatenations with some sort of memory stream object?
I used StreamWriter before for saving my database to a file. How do i create a StreamWriter for memory? Haven’t done much with streams yet. Should be more efficient.

And, by the way, i have to rename all my database files to .byte in order to attach them to my scripts. There has to be some way to define custom file extentions for TextAssets, right? I mean, these files are neither html, nor xml, bytes, json, csv, etc… they are it’s own format. I have to give them a misleading name in order to get them to load.

4

Just use existing DB solutions. For example SQL/SQLite

1 Like
5

Regarding memory streams: I put up this script, but it just prints out an empty string.

MemoryStream ms = new MemoryStream();
ms.Position = 0;
StreamWriter sw = new StreamWriter(ms);
StreamReader sr = new StreamReader(ms);
sw.Write("Hello");
sw.Write(" ");
sw.Write("World");
Debug.Log(sr.ReadToEnd());
6

Sadly i have no clue how they work.
I definetily don’t want this to work over a server. I used to host a wow private server for fun with an sql item databank and it was its own sql server running next to the gameserver itself. I would love to use sql as long as it would be still contained to one file that gets read by a class in my project. Along the lines of Item[ ] all_items = ReadDB(“assets\items.sql”);

EDIT:
actually, maybe i am a bit stubborn right here. Why no server? If it works, why not. it would have to start along with the game though. Also, it is an android game. I could not imagine that an android app would launch an sql server in the background. What do you think?

I always thought that SQL was only for online games?

7

A TextAsset is supposed to have the general extension “.asset” according to the docs. I have Json and XML files as TextAssets, and they have .asset extensions. I can just track their file types elsewhere.

8

@Leonetienne500 check SQLite then. You can store DB locally with a game.
There are free libraries to communicate.

I used it for my [DOTS] Hovercrafts Sandbox Prototype

9

They seem to be wrong. I renamed my database from “items.bytes” to “items.dbml.asset” (dbml is the original format) and now i get 7 errors when trying to re-import it. Nice.

10

I will definetily. Thanks. Maybe it would also fix this 7 minute loading screen aswell:smile: And these 7 minutes were exluding the loot table load :o

11

Yes it does. Though before 2019.3, hierarchy view can’t be switched to display other threads than the main thread. Timeline view shows all threads though.

12

Also I’d generally be wary of any DB implementation on mobile and profile it heavily before committing to it. GC alloc and heap fragmentation on mobile might very well come back to bite you later in the project as your frame budgets fill up and the DB has to handle more content. Not sure if there are any nice no- or low-alloc solutions out there

1 Like
13

Mostly yes. But SQLite brings power of sql database into desktop and mobile app. It is an in-process single user sql database and query processor. The database is just a file on disk. You add reference to SQLite to your project, point it to that file, and use it like any regular sql data source is used in C#. This might help you. https://github.com/robertohuertasm/SQLite4Unity3d

14

Had a look at it. I mean, it’s really awesome. If it would be performant it would be perfect.
I mean, all i have done with it by now is to add bunch of cows with colors and names to a learning db:smile: so i can’t say anything about speed just now.

Bummer that the nuget package won’t work with unity. Guess i have to buy one from the AS. At least i have a few hours until black friday ends. Would be difficult to find a nuget package that works for android and windows at once, anyways^^

15

I got really strong feeling you are doing something wrong.
What kind of data you are actually try to load? What you do with it?
Why loading so much data at once.
Show us sample of data, and your parsing code.

16

I am trying to load an item database. (eg a bunch of rows with columns like itemID, name, requiredLevel, description, armor, weight, iconID etc…) Seems to be really fitting for sqlite. Can’t have 115000 prefabs :smile:

Until now i had to read / write (in case of my editor) all entries at once because my former format basically was glorified csv.

I am now intentending to switch to sqlite. Then i would no longer have to load everything at once. I could just do a query for the exact itemID that i want.

My converter is converting this csv madness to an sqlite db right now. When it finished, i’ll attach a screenshot of my database, as requested.

My parsing code will be deprecated, so why send it all. No need for parsing anything when i have an sqlite db.

The only thing i’m kind of unsure is how to save loot tables. (eg which chests drops which items how likely and how many). My sqlite solution is a seperate database file called lootTables.db with many tables (chest eg “chest4923” and these tables would have columns like “itemID, dropChance, minAmount, maxAmount”.

my item database

And one of the loot tables in lootTable.db i’ve mentioned

Am i doing this right? This is the first time working with SQL

17

Do you have over 160k items?
Any reason, your icons use +2mln ID?
I doubt you have +2mln icons. And most likely you reuse some of them.

When you start repeating text in columns, or set of data, this indicates, you need split part of data to another table.
Then use ID, to reference that element in the table.

For example look quality column.
You create Quality table, with all possible options.

ID: 1, Name: Epic,
ID: 2, Name: Perfect,
ID: 3, Name: Good,
ID: 4, Name: Standard,
ID: 5, Name: Poor,
ID: 6, Name:.Broken,
ID: 7, Name: NaN

Now you use these IDs to reference quality. You reduce amount of data in main table of items.
Parsing and comparing string instead of numbers is expensive for CPU. And you create lot of GC, as it has been mentioned few post earlier.

Remember, if you start fetching whole row, you get all data in row, even if you dont need it.
You can of course fetch only selected columns, of filtered rows.

There is few ways to approach the problem. But I describe briefly one.
Lets say you got chest, with ID 5,

You got Quality table (see above)

You got Items table.

ID: 35, Type: boots, Quality ID: 5
ID: 70, Type: coins, Quality ID: 7
ID: 113, Type: bread, Quality ID: 3

Now in Chests table

ID: 5, Item ID: 35, Count: 1
ID: 5, Item ID: 70, Count: 541
ID: 5, Item ID: 113, Count: 3

Etc.

18

Okay, thanks.
My reason is that this project is kind of a fun project for learning. I am not planning on releasing it. I needed a database. I figured, it would be nice to copy paste the entire World of Warcraft item database. Ask blizzard why their IDs are set up in this weird way^^

I think, it would suffice to just replace the quality with numbers. No need for an extra table with their names.

19

This phrase and where you changed your mind and said that you would try out SQL make me happy for you: I think a) you will enjoy the learning process of integrating SQLite into Unity (it’s easy, honest! Just be ready to google a little bit…), and b) you will be impressed with the performance.

Ideally, you will never have to load all your items at once: on demand you can hit the DB and say “I need a Sword Of TableJoining with Three INSERT Sockets” and it will be so performant, you can easily get away with it.

1 Like
20

You could just add crateId field and have them all in one table Loots.