DP/AP to NPC Problem

[[GameAdvisor]Finn]

Hi Epvp! i hope anyone can help me on my problem about ap to npc problem!
because my ap to npc got delay send when the server got active pvp's like event pvp!
i use procedure method last time and its work but when pvp is active like i said .. its not effect when we sale the ap to npc and ap will send few hours!
i try trigger method too and same.. its work but got delay too.. anyone can help me? how to fix this? thank you in advance!..

This is my ActionLog

 Spoiler

USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 01/25/2019 14:32:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[usp_Insert_Action_Log_E]

@UserID varchar(18),
@ID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint,
@ bigint = null,
@ int = null,
@3 int = null,
@4 bigint = null,
@5 int = null,
@6 int = null,
@7 int = null,
@8 int = null,
@9 int = null,
@0 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '',
@ varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1
AS
DECLARE @CharLeave int

SET @ = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)
SET @CharLeave = 1

IF @ActionType = '116'--Trade Item-remove item from originator
BEGIN
WAITFOR DELAY '00:00:05'--Time delay to give the duper time to log out fully

SELECT @CharLeave=Leave
FROM PS_userdata.dbo.Users_Master
WHERE UserUID @ID

IF @CharLeave=0
BEGIN
EXEC PS_GameData.dbo.usp_Save_Char_Item_Del_E @CharID=@CharID, @IDList @
END
END

IF @ActionType = 173 AND @text2 = 'death' -- boss death, only applies to the ones from the Obelisk.ini
BEGIN
SET @ID = (SELECT TOP 1 UserUID FROM PS_GameData.dbo.Chars WHERE CharName = @text3)
INSERT INTO Boss_Death_Log VALUES @3, @text1, @ID, @text3, @MapID, @PosX, @PosY, @posz, @ActionTime)
END



IF @ActionType = '164'--Trade Gold-remove gold from originator
BEGIN

WAITFOR DELAY '00:00:05'--Time delay to give the duper time to log out fully

SELECT @CharLeave=Leave
FROM PS_userdata.dbo.Users_Master
WHERE UserUID @ID

IF @CharLeave=0
BEGIN
UPDATE PS_GameData.dbo.Chars
SET [Money] @
WHERE PS_GameData.dbo.Chars.CharID=@CharID
END
END

DECLARE
@AT int, @ItemID int
SET @ItemID = @


-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 114


-- -----------------------------------------
IF ( @ActionType = @AT )
BEGIN

-- 10 AP Card

IF (@ItemID = 100220)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 10)
WHERE UserUID = @ID

END


-- 200 AP Card

IF (@ItemID = 100223)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 200)
WHERE UserUID = @ID

END


-- 500 AP Card

IF (@ItemID = 100224)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 500)
WHERE UserUID = @ID

END

End

DECLARE
@DIP varchar (100),
@UID varchar (100),
@KIP Varchar (100)

IF (@ActionType = 103)

BEGIN
SELECT @UID = um.UserID FROM PS_UserData.dbo.Users_Master as um
INNER JOIN PS_GameData.dbo.Chars as c ON c.UserUID = um.UserUID
inner join PS_GameLog.dbo.ActionLog as a on a.Value1 = c.CharID Where c.CharID = @ AND ActionType = 103

SELECT @KIP = um.UserIp FROM PS_UserData.dbo.Users_Master as um
INNER JOIN PS_GameData.dbo.Chars as c ON c.UserUID = um.UserUID
inner join PS_GameLog.dbo.ActionLog as a on a.Value1 = c.CharID Where c.CharID = @ AND ActionType = 103

Select @DIP = um.UserIP FROM PS_UserData.dbo.Users_Master as um
INNER JOIN PS_GameLog.dbo.ActionLog as a on um.UserID = a.UserID WHERE um.UserID=@UserID AND ActionType = 103

IF @DIP = @KIP

INSERT INTO GM_Stuff.dbo.StatPadder (DeadToon,DeadIP,DeadID,KillerToon,KillerIP,Killer ID,Date,Map)
VALUES (@Text1,@KIP,@UID,@CharName,@DIP,@UserID,@ActionTi me,@MapID)
END
BEGIN
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4)
END

[[ADM]Beno™]

Try this Click Item to get DP !

 Spoiler

USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 28.12.2013 02:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008/3/15 ?? 05:16:53 ******/

CREATE Proc [dbo].[usp_Insert_Action_Log_E]

@UserID varchar(18), @ID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint, @ bigint = null, @ int = null, @3 int = null, @4 bigint = null, @5 int = null, @6 int = null, @7 int = null, @8 int = null, @9 int = null, @0 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '', @ varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS

/*
usp_Insert_Action_Log_E_v2.sql RELEASE: 2011-07-14

Scripted by [Dev]HoaX
Made for: ShaiyaAlliance / Shaiya-Reloaded / Shaiya-SyFy / Elitepvpers
*/

DECLARE
@AT int, @ItemID int
SET @ItemID = @


-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 112


-- -----------------------------------------
IF ( @ActionType = @AT )
BEGIN

-- 25 AP Card

IF (@ItemID = 25219)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 25)
WHERE UserUID = @ID

END


-- 100 AP Card

IF (@ItemID = 25042)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 100)
WHERE UserUID = @ID

END


-- 1000 AP Card

IF (@ItemID = 44129)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 1000)
WHERE UserUID = @ID

END

End
-- -----------------------------------------



SET @ = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql,
N'@UserID varchar(18), @ID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint, @ bigint, @ int, @3 int, @4 bigint, @5 int, @6 int, @7 int, @8 int, @9 int, @0 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4

Credits : [Dev]Hoax

[[GameAdvisor]Finn]

Originally Posted by [ADM]Beno™

Try this Click Item to get DP !

 Spoiler

USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 28.12.2013 02:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008/3/15 ?? 05:16:53 ******/

CREATE Proc [dbo].[usp_Insert_Action_Log_E]

@UserID varchar(18), @ID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint, @ bigint = null, @ int = null, @3 int = null, @4 bigint = null, @5 int = null, @6 int = null, @7 int = null, @8 int = null, @9 int = null, @0 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '', @ varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS

/*
usp_Insert_Action_Log_E_v2.sql RELEASE: 2011-07-14

Scripted by [Dev]HoaX
Made for: ShaiyaAlliance / Shaiya-Reloaded / Shaiya-SyFy / Elitepvpers
*/

DECLARE
@AT int, @ItemID int
SET @ItemID = @


-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 112


-- -----------------------------------------
IF ( @ActionType = @AT )
BEGIN

-- 25 AP Card

IF (@ItemID = 25219)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 25)
WHERE UserUID = @ID

END


-- 100 AP Card

IF (@ItemID = 25042)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 100)
WHERE UserUID = @ID

END


-- 1000 AP Card

IF (@ItemID = 44129)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 1000)
WHERE UserUID = @ID

END

End
-- -----------------------------------------



SET @ = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql,
N'@UserID varchar(18), @ID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint, @ bigint, @ int, @3 int, @4 bigint, @5 int, @6 int, @7 int, @8 int, @9 int, @0 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4

Credits : [Dev]Hoax

that i use now but dp to npc method.. and i got delay dp on my account when pvp event is active.. i don't know why..

[[ADM]Acid]

Might be that your actionlog just overloads and the database can't respond so fast by that.. I wonder whats your CPU workload in % while this problem appears?
Maybe just try to make a backup when the problem appears, and clean the logs then to ease your SQL, and see if the problem solves by that. (Anyway, just a guess of mine)

[[DEV]Elessar]

You can try using a trigger!

 Spoiler

USE [PS_GameLog]
GO
/****** Object: Trigger [dbo].[Action] Script Date: 07/24/2011 18:54:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Action]
on [PS_GameLog].[dbo].[ActionLog]
for insert
as


DECLARE @Type tinyint,
@AT int, @ID int, @ID int, @ tinyint


/*
Action_Trigger_v2.sql RELEASE: 2011-07-14

Scripted by [Dev]HoaX
Made for: ShaiyaAlliance / Shaiya-Reloaded / Shaiya-SyFy / Elitepvpers
*/

-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 114

SET @ID = (SELECT Value2 FROM inserted)
SET @ = (SELECT Value4 FROM inserted)
SET @Type = (SELECT ActionType FROM inserted)
-- -------------------------------------------------

IF ( @Type = @AT )
BEGIN
-- -----------------------------------------

-- 25 AP Card

IF @ID = 25230)
BEGIN
SET @ID = (SELECT UserUID FROM inserted)

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @ * 100)
WHERE UserUID = @ID
END


-- 100 AP Card

IF @ID = 25231)
BEGIN
SET @ID = (SELECT UserUID FROM inserted)

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @ * 1000)
WHERE UserUID = @ID
END


-- 1000 AP Card

IF @ID = 25232)
BEGIN
SET @ID = (SELECT UserUID FROM inserted)

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @ * 3000)
WHERE UserUID = @ID
END
-- -----------------------------------------
End

[{Skrillex}]

As Acid already told: your ActionLog is overloaded.
The best way to do is makeing with Assembly.

[Munat]

you can try to somthing like nubness used to his admin panel most action type are not Records

For having a more functional log system, and decreasing log processing time, I have changed the PS_GameLog database, by spreading the information in multiple tables and not recording the so called junk logs, which are useless. From now on, your logs will only contain useful information.

[[GameAdvisor]Finn]

Originally Posted by [ADM]Beno™

Try this Click Item to get DP !

 Spoiler

USE [PS_GameLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Insert_Action_Log_E] Script Date: 28.12.2013 02:15:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008-6-7 18:32:40 ******/
/****** Object: Stored Procedure dbo.usp_Insert_Action_Log_E Script Date: 2008/3/15 ?? 05:16:53 ******/

CREATE Proc [dbo].[usp_Insert_Action_Log_E]

@UserID varchar(18), @ID int,
@CharID int,
@CharName varchar(50),
@CharLevel tinyint,
@CharExp int,
@MapID smallint,
@PosX real,
@PosY real,
@PosZ real,
@ActionTime datetime,
@ActionType tinyint, @ bigint = null, @ int = null, @3 int = null, @4 bigint = null, @5 int = null, @6 int = null, @7 int = null, @8 int = null, @9 int = null, @0 int = null,
@Text1 varchar(100) = '',
@Text2 varchar(100) = '',
@Text3 varchar(100) = '',
@Text4 varchar(100) = '',
@Sql nvarchar(4000) = '', @ varchar(4) = '',
@mm varchar(2) = '',
@dd varchar(2) = '',
@Bucket smallint = -1

AS

/*
usp_Insert_Action_Log_E_v2.sql RELEASE: 2011-07-14

Scripted by [Dev]HoaX
Made for: ShaiyaAlliance / Shaiya-Reloaded / Shaiya-SyFy / Elitepvpers
*/

DECLARE
@AT int, @ItemID int
SET @ItemID = @


-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 112


-- -----------------------------------------
IF ( @ActionType = @AT )
BEGIN

-- 25 AP Card

IF (@ItemID = 25219)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 25)
WHERE UserUID = @ID

END


-- 100 AP Card

IF (@ItemID = 25042)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 100)
WHERE UserUID = @ID

END


-- 1000 AP Card

IF (@ItemID = 44129)
BEGIN

UPDATE PS_UserData.dbo.Users_Master
SET Point = (Point + @4 * 1000)
WHERE UserUID = @ID

END

End
-- -----------------------------------------



SET @ = datepart(yyyy, @ActionTime)
SET @mm = datepart(mm, @ActionTime)
SET @dd = datepart(dd, @ActionTime)


IF(LEN(@mm) = 1)
BEGIN
SET @mm = '0' + @mm
END

IF(LEN(@dd) = 1)
BEGIN
SET @dd = '0' + @dd
END

SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4)'

EXEC sp_executesql @Sql,
N'@UserID varchar(18), @ID int, @CharID int, @CharName varchar(50),
@CharLevel tinyint, @CharExp int, @MapID smallint, @PosX real, @PosY real, @PosZ real, @ActionTime datetime, @ActionType tinyint, @ bigint, @ int, @3 int, @4 bigint, @5 int, @6 int, @7 int, @8 int, @9 int, @0 int, @Text1 varchar(100), @Text2 varchar(100), @Text3 varchar(100), @Text4 varchar(100)',
@UserID, @ID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType, @, @, @3, @4, @5, @6, @7, @8, @9, @0, @Text1, @Text2, @Text3, @Text4

Credits : [Dev]Hoax

Originally Posted by Munat

you can try to somthing like nubness used to his admin panel most action type are not Records

Thanks its not delay now but actionlog not recording..