Extremely bad C# performance

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

 

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.

 

Just use existing DB solutions. For example SQL/SQLite

 

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

Code (CSharp):

1. MemoryStream ms = new MemoryStream();
2. ms.Position = 0;
3. StreamWriter sw = new StreamWriter(ms);
4. StreamReader sr = new StreamReader(ms);
5. sw.Write("Hello");
6. sw.Write(" ");
7. sw.Write("World");
8. Debug.Log(sr.ReadToEnd());

 

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?

 

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.

 

@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

 

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.

 

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

 

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

 

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

 

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.

 

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

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

 

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.

 

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.

 

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.

 

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

 

It is fine.

It is fine too. Many did past over decade.

But here is the problem. The DB you hold, is built for server side storing data. Or if I am incorrect, at least for desktop, as if in case, whole data is stored in game itself.
You trying move it to mobile. This DB is not optimized that.

 

Here's some real word sqlite/android performance concers
https://stackoverflow.com/questions/4015026/sqlite-optimization-for-android-application

 

Mind, posted thread is from 2010, with update in 2013. World moved on.
New benchmark is required.

 

Theory behind RDB and their optimization hasn't changed much since.

 

Theory not, but hardware and solutions have.
While it may be not most optimal, things have improved since 2010.
In the end, problem is not tools, but the app design. Like trying load whole DB at once for example.

 

This is exactly one of things what have not changed ever since. And loading whole DB at once may be a good solution if you want fast access, it is provided as in-memory database feature in sqlite, if I remember correct. This might be good solution for android and items database, if database footprint is small enough, like 100 megabytes, for example. Mobile storage devices often are slow and memory is fast and 100 mb is basically nothing today. Previous game I've worked on simply will not run on device if it has less than 1gb memory. And producer said it's okay, so it's probably okay.

 

And of course, if loading time is acceptable. If referring to OP case, it wasn't. Otherwise, need load selectively on demand. Or at least in chunks. For example changing ingame location. Or load data in background, which can be spread over the time, while game is running.

Other than that yes, once data in in memory, that shouldn't be an issue. Is just matter of way, of putting data into that memory.

 

Yes, this. From what I see in this topic, I can think the OPs parsing algorythm and text asset is the roots of performance loss here. Probably this

Code (CSharp):

1.             using (StreamReader sr = new StreamReader("TestFile.txt"))
2.             {
3.                 string line;
4.                 while ((line = sr.ReadLine()) != null)
5.                 {
6.                     // find all indicies of ';'
7.                     // parse values inbetween into class
8.                    // add instance to array
9.                 }
10.             }

would work a little faster than intiali OPs implementation, but it can be improved by reusing buffers instead of creating new strings.

Or this. Database might run all operations in another thread and that's it.