Discussion Reason of delay?

[Ο Χάρος]

This also have a method i didnt see: 

activeChar.sendMessage("You gave " + player.giveAvailableSkills(includedByFs, true) + " skills to " + player.getName());

But the giveAvailableSkills is just this:

public int giveAvailableSkills(final boolean includedByFs, final boolean includeAutoGet)
	{
		int unLearnable = 0;
		int skillCounter = 0;

		List<L2SkillLearn> skills = SkillTreesData.getInstance().getAvailableSkills(this, getClassId(), includedByFs, includeAutoGet);
		while (skills.size() > unLearnable)
		{
			for (final L2SkillLearn s : skills)
			{
				final L2Skill sk = SkillTable.getInstance().getInfo(s.getSkillId(), s.getSkillLevel());
				if (sk == null || sk.getId() == L2Skill.SKILL_DIVINE_INSPIRATION && !Config.AUTO_LEARN_DIVINE_INSPIRATION && !isGM())
				{
					unLearnable++;
					continue;
				}
				if (getSkillLevel(sk.getId()) == -1)
				{
					skillCounter++;
				}
				// fix when learning toggle skills
				if (sk.isToggle())
				{
					final L2Effect toggleEffect = getFirstEffect(sk.getId());
					if (toggleEffect != null)
					{
						// stop old toggle skill effect, and give new toggle
						// skill effect back
						toggleEffect.exit();
						sk.getEffects(this, this);
					}
				}
				addSkill(sk, true);
			}
			// Get new available skills, some skills depend of previous skills to be available.
			skills = SkillTreesData.getInstance().getAvailableSkills(this, getClassId(), includedByFs, includeAutoGet);
		}
		sendMessage("You have learned " + skillCounter + " new skills.");
		return skillCounter;
	}

 

No db relative or flooding with sendMessage or packet flood. 

[Ο Χάρος]

Damn it, the     addSkill(sk, true); 

at the code i showed you have  SQL relative code pff

public L2Skill addSkill(final L2Skill newSkill, final boolean store)
	{
		// Add a skill to the L2PcInstance _skills and its Func objects to the
		// calculator set of the L2PcInstance
		final L2Skill oldSkill = super.addSkill(newSkill);
		// Add or update a L2PcInstance skill in the character_skills table of
		// the database
		if (store)
		{
			storeSkill(newSkill, oldSkill, -1);
		}
		return oldSkill;
	}

You were right... damn this fucking SQL. 

private void storeSkill(final L2Skill newSkill, final L2Skill oldSkill, final int newClassIndex)
	{
		int classIndex = _classIndex;
		if (newClassIndex > -1)
		{
			classIndex = newClassIndex;
		}
		Connection con = null;
		try
		{
			con = L2DatabaseFactory.getInstance().getConnection();
			PreparedStatement statement;
			if (oldSkill != null && newSkill != null)
			{
				statement = con.prepareStatement(UPDATE_CHARACTER_SKILL_LEVEL);
				statement.setInt(1, newSkill.getLevel());
				statement.setInt(2, oldSkill.getId());
				statement.setInt(3, getObjectId());
				statement.setInt(4, classIndex);
				statement.execute();
				statement.close();
			}
			else if (newSkill != null)
			{
				statement = con.prepareStatement(ADD_NEW_SKILL);
				statement.setInt(1, getObjectId());
				statement.setInt(2, newSkill.getId());
				statement.setInt(3, newSkill.getLevel());
				statement.setInt(4, classIndex);
				statement.execute();
				statement.close();
			}
			else
			{
				_log.warning("could not store new skill. its NULL");
			}
		}
		catch (final Exception e)
		{
			_log.log(Level.WARNING, "Error could not store char skills: " + e.getMessage(), e);
		}
		finally
		{
			L2DatabaseFactory.close(con);
		}
	}

 

[Elfo]

a

Edited October 1, 2021 by Elfocrash

[Ο Χάρος]

Just now, .Elfocrash said:

Ofc there would be sql related stuff. There is no way simple datastructures would lock things like this.

This look is draining the shit out of the db pool. H5 is only faster because it is using HikariCP (and potentially better code) which allows a bigger amound of connections in the pool to be used.

I'm not the best in SQL, what u suggest?  Maybe instead of open 1 connection for each skill to add all in 1 map and open 1 con and do a for e.t.c ? wouldnt this be faster?

Else what ? 

[Elfo]

a

Edited October 1, 2021 by Elfocrash

[Ο Χάρος]

Just now, .Elfocrash said:

Well realistically what you can do now is to batch the datain the prepared statement and execute the batch. This should use one connection.

Here is an example in case you don't know what im talking about: https://www.mkyong.com/jdbc/jdbc-preparedstatement-example-batch-update/

However all this should be asynchronous but no one bothers to optimize performance in sql because they dont know how.

I set the addSkill(skill, false); so it won't store each skill individual using DB

and bellow i add the skill onto a List  and at the end of the code i call this method that i added

private void storeSkills(List<L2Skill> newSkills, int newClassIndex)
	{
		if (newSkills.isEmpty())
		{
			return;
		}
		
		final int classIndex = (newClassIndex > -1) ? newClassIndex : _classIndex;
		try (Connection con = L2DatabaseFactory.getInstance().getConnection();
			PreparedStatement ps = con.prepareStatement(ADD_NEW_SKILLS))
		{
			con.setAutoCommit(false);
			for (final L2Skill addSkill : newSkills)
			{
				
				ps.setInt(1, getObjectId());
				ps.setInt(2, addSkill.getId());
				ps.setInt(3, addSkill.getLevel());
				ps.setInt(4, classIndex);
				ps.addBatch();
			}
			ps.executeBatch();
			con.commit();
		}
		catch (SQLException e)
		{
			
		}
	}

Which simply open 1 DB c and store all together. But how faster the HikariCP is? Can i upgrade to it? im not sql fan.

[Elfo]

a

Edited October 1, 2021 by Elfocrash

[Ο Χάρος]

Just now, .Elfocrash said:

What i would do is this:

Creatin a new method called addSkills which allows an array of skills to be handled. You will need old and new skill separation for update and add

Ok i did. Ill go check in game to see the thread how much time it get sleep until db stuff finish. Thanks for ur help i wouldnt search further if u didn't say the word "DB" .

I kinda liked procedures in SQL but the overal structure and coding i dont like it. 

[Ο Χάρος]

28 minutes ago, .Elfocrash said:

What i would do is this:

Creatin a new method called addSkills which allows an array of skills to be handled. You will need old and new skill separation for update and add

So process so far. I did what i wrote. Created new Map, stored each new skill on it. Changed the addSkill from true to false so it wont store individualy

at the end of the class i added the storeSkills. The method simply store all L2Skills at once without open 1000 DB con. Still in game 1.5 sec delay like i never changed anything.

The method turns out to Finalize after 77 milliseconds. I refer to this method 

public int giveAvailableSkills(final boolean includedByFs, final boolean includeAutoGet)
	{
		long ms = System.currentTimeMillis();
		
		int unLearnable = 0;
		int skillCounter = 0;

		List<L2SkillLearn> skills = SkillTreesData.getInstance().getAvailableSkills(this, getClassId(), includedByFs, includeAutoGet);
		List<L2Skill> skillsForStore = new ArrayList<>();
		
		while (skills.size() > unLearnable)
		{
			for (final L2SkillLearn s : skills)
			{
				final L2Skill sk = SkillTable.getInstance().getInfo(s.getSkillId(), s.getSkillLevel());
				if (sk == null || sk.getId() == L2Skill.SKILL_DIVINE_INSPIRATION && !Config.AUTO_LEARN_DIVINE_INSPIRATION && !isGM())
				{
					unLearnable++;
					continue;
				}
				if (getSkillLevel(sk.getId()) == -1)
				{
					skillCounter++;
				}
				// fix when learning toggle skills
				if (sk.isToggle())
				{
					final L2Effect toggleEffect = getFirstEffect(sk.getId());
					if (toggleEffect != null)
					{
						// stop old toggle skill effect, and give new toggle
						// skill effect back
						toggleEffect.exit();
						sk.getEffects(this, this);
					}
				}
				addSkill(sk, false); //addSkill(sk, true);
				skillsForStore.add(sk);
			}
			skills = SkillTreesData.getInstance().getAvailableSkills(this, getClassId(), includedByFs, includeAutoGet);
		}
		
		storeSkills(skillsForStore, -1);
		
		if (Config.AUTO_LEARN_SKILLS && (skillCounter > 0))
		{
			sendMessage("You have learned " + skillCounter + " new skills.");
		}
		
		System.out.println(System.currentTimeMillis() - ms);
		return skillCounter;
	}

 

So either the SQL data in Freya is shit as u said or client relative... idk any suggestion or could we implement HikariCP ?B

But again if it was SQL slow, it would be captured on the system.out..  it would say 3500 millisecond or something no 35... cause in this berchmark the sql con is included.

So it might be client lag? But i tried rotate immidiately after i get the skill list and the L2 respond fine, it rotate perfectly just the char doesnt move and suddedly he move x10 fast to the spot 

i clicked.

Edited September 8, 2017 by Ο Χάρος

[Tryskell]

Use MySQL workbench and you will figure by yourself. I told you the problem.

[Elfo]

a

Edited October 1, 2021 by Elfocrash

[Tryskell]

Just now, .Elfocrash said:

It is the shitty sql which is made worse by the shitty cp

Using another db pooler simply hide the real problem, and hide all possible problems. It's sometimes good to get shitty db pooler.

:P

[Elfo]

a

Edited October 1, 2021 by Elfocrash

[Tryskell]

It doesn't enhance the problem, it highlights it. Which is great, otherwise it would be hidden.

Btw addBatch correct use depends of numerous factors ; used jdcb, mysql version, db type (myISAM/etc) etc. Using addBatch, it's not even sure it correctly processes the batch, it can transform it to regular single query.

Edited September 10, 2017 by Tryskell

[Elfo]

a

Edited October 1, 2021 by Elfocrash