In need of major help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

In need of major help

Author

Topic

calebbender
Starting Member

7 Posts

Posted - 2003-06-26 : 14:00:58

I have an online game thats growing very rapidly. Until lately the speed was fine but it has slowed down a ton since I am getting more and more users.

My problem is this. When the clock changes to an hour, ie 1 pm, 2pm, etc, I need to update all users information. This includes all of their resources, military units, and peasants. To do this I need to run calculations based on the spells, military, etc.

Right now I run a job that calls the following SP.. I have included the SPS that is calls as well.

CREATE PROCEDURE AOW_ChangeResourcesontheHour

AS

set Nocount on

declare @userid numeric(9,0)
declare @totalusers numeric(9,0)

--create out temp table to loop through
CREATE TABLE #tempusers
(
numID numeric(9,0) IDENTITY(1,1),
userid numeric(9,0)
)

INSERT INTO #tempusers (userid)
SELECT userid from webcuttergame.dbo.provincevaluesnew
SET @totalusers = @@ROWCOUNT --How many users

WHILE @totalusers <> 0
BEGIN
SET @userid = (SELECT userid FROM #tempusers WHERE numID = @totalusers)

/* Draft draftees */
exec DraftJob @userid=@userid

/* Change Peasants */
exec ChangePeasantsJob @userid=@userid

/* Update their resources new - changed on 6-26-03 */
exec ChangeResourcesNew @userid=@userid

--Move backward through the users
SET @totalusers = @totalusers - 1
END

DROP TABLE #tempusers
GO

CREATE Procedure DraftJob
(
@userid int
)
AS

set nocount on

Declare @numberofdraftees numeric(13,0)
Declare @draftrate numeric (13,0)
Declare @peasants numeric (13,0)
Declare @totalmilitary numeric(13,0)
Declare @drafteePk int
Declare @draftrate1 numeric (13,6)
Declare @draftrate2 numeric (13,6)
Declare @DrafteesToInsert numeric (13,0)
Declare @TotalMilitaryCalc numeric (13,6)
Declare @TotalMilitaryAfterDraft numeric (13,6)
Declare @TotalPeasantsAfterDraft numeric (13,6)
Declare @TotalDraftRateCalc numeric (13,6)
Declare @totaldraftedmilitary numeric(13,0)
Declare @totalmilitaryintraining numeric(13,0)

/** Get the total number of military units at home **/
SELECT @totaldraftedmilitary= SUM(dbo.Race_Unit_Values.value) FROM dbo.Race_Unit_Values INNER JOIN dbo.Race_Units ON dbo.Race_Unit_Values.unitfk = dbo.Race_Units.unitpk WHERE (dbo.Race_Unit_Values.userid = @userid)

/** Get the total number of military units in training and away **/
SELECT @totalmilitaryintraining = SUM(dbo.WIP.iValue)
FROM dbo.WIP INNER JOIN
dbo.WIPTypes ON dbo.WIP.iWIPTypeFk = dbo.WIPTypes.iWIPTypePk
Where ((dbo.WIPTypes.cWIPTypeDesc = 'Military') or (dbo.WIPTypes.cWIPTypeDesc = 'MilitaryInCombat')) AND (dbo.WIP.userid = @userid)
select @totalmilitaryintraining = isNull(@totalmilitaryintraining, 0)
--print '@totalmilitaryintraining: ' + convert(varchar(235),@totalmilitaryintraining)

SELECT @totalmilitary = @totaldraftedmilitary + @totalmilitaryintraining
select @totalmilitary = isNull(@totalmilitary, 0)
--print '@totalmilitary: ' + convert(varchar(235),@totalmilitary)

/**Get the number of peasants for this user**/
SELECT @peasants = peasants,@draftrate = draftrate from provincevaluesnew where userid = @userid

select @TotalMilitaryCalc = ((@totalmilitary/(@peasants+@totalmilitary)) * 100)

If (@TotalMilitaryCalc < @draftrate)
BEGIN

/* Get the total number of draftees for this user */
SELECT @numberofdraftees = Race_Unit_Values.value
FROM Race_Unit_Values INNER JOIN
Race_Units ON Race_Unit_Values.unitfk = Race_Units.unitpk
WHERE (Race_Unit_Values.userid = @userid) AND (Race_Units.unitname = 'Draftee')

--check if they have provincal pride on
Declare @iPrideOn as int

Select @iPrideOn=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 44 and userid=@userid
select @iPrideOn = isNull(@iPrideOn, 0)

if @iPrideOn > 0
BEGIN
select @draftrate1 = FLOOR(@peasants*.02)
END
else
BEGIN
select @draftrate1 = FLOOR(@peasants*.01)
END

select @draftrate2 = FLOOR((@peasants + @totalmilitary)*(@draftrate/100)-@totalmilitary)

/* Set the number of Draftees to enter */
if (@draftrate1 < @draftrate2)
BEGIN
select @DrafteesToInsert = @draftrate1
END
else
BEGIN
Select @DrafteesToInsert = @draftrate2
END

/* Get the draftee pk for this user */
SELECT @drafteePk = Race_Unit_Values.unitfk
FROM Race_Unit_Values INNER JOIN
Race_Units ON Race_Unit_Values.unitfk = Race_Units.unitpk
WHERE (Race_Unit_Values.userid = @userid) AND (Race_Units.unitname = 'Draftee')

if (@DrafteesToInsert >0)
BEGIN
Update Race_Unit_Values Set Value = convert(bigint,FLOOR(@DrafteesToInsert+@numberofdraftees)) where (unitfk= @drafteePk) and (userid = @userid)
Update provincevaluesnew Set peasants = FLOOR(@peasants -@DrafteesToInsert) where (userid = @userid)
END
END
GO

CREATE Procedure ChangePeasantsJob
(
@userid int
)
AS

set nocount on

Declare @totalbuildings numeric(19,0)
Declare @totalhomes numeric(19,0)
Declare @totalland numeric(19,0)
Declare @barrenland numeric(19,0)
Declare @totalpeasants numeric(19,0)

Declare @oldpeasants numeric(19,0)
Declare @peasantpk numeric(19,0)
Declare @totalpopulation numeric(19,0)
Declare @totalpopulationcalc numeric(19,0)
Declare @peasantstoinsert numeric(19,0)
Declare @peasantchange numeric(19,0)
Declare @totalmilitary numeric(19,0)
Declare @hourlychangepk numeric(19,0)
Declare @IncomingNonHomes numeric(19,0)
Declare @IncomingHomes numeric(19,0)

/*
This procedure will increase or decrease a user's peasants once an hour. It is based on the buildings and land the user has.
1) We need the users id
2) We need to find out how many buildings they have
3) We need to find out home many of those are homes
4) We need to find out how much total land they have
5) We need to find out how much of their total land has not been built on
6) We need to find out how much total population they have including military units

NOTES:
1) Non-Homes house 20 people
2) Homes House 30 people
3) Barren land house 10 people
*/
/**Get the number of peasants for this user**/
declare @MaxPopImprovementBonus float
Declare @ValorLevel int
declare @raceid int
SELECT @raceid = raceid, @oldpeasants = peasants, @totalland = land,@MaxPopImprovementBonus=keep,@ValorLevel=valor from provincevaluesnew where userid = @userid

select @ValorLevel = isNull(@ValorLevel,0)

/** Get the total number of buildings**/
SELECT @totalbuildings = sum(dbo.BuildingsByUser.totalbuilt)
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid = @userid) and (dbo.BuildingsByUser.buildingsFK <> 13)

-- get the incoming buildings that are not homes
SELECT @IncomingNonHomes = sum(underconstruction) from vw_BuildingsUnderConstruction where userid=@userid and buildingsfk <> 13
SELECT @IncomingNonHomes = isNull(@IncomingNonHomes,0)

--add them together
select @totalbuildings = @totalbuildings + @IncomingNonHomes

/* Get this user's homes */
SELECT @totalhomes = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid = @userid) AND (dbo.BuildingsByUser.buildingsFK = 13)
SELECT @totalhomes = isNull(@totalhomes,0)

-- get the incoming buildings that are homes
SELECT @IncomingHomes = sum(underconstruction) from vw_BuildingsUnderConstruction where userid=@userid and buildingsfk = 13
SELECT @IncomingHomes = isNull(@IncomingHomes,0)

--add them together
select @totalhomes = @totalhomes + @IncomingHomes

/* Get the total number of military units */
SELECT @totalmilitary = SUM(value)
FROM dbo.Race_Unit_Values
WHERE (userid = @userid)
select @totalmilitary = isNull(@totalmilitary, 0)

/* Get their military thats out in combat as well */
Declare @iMilitaryInWip numeric(19,0)

SELECT @iMilitaryInWip = SUM(dbo.WIP.iValue)
FROM dbo.WIP INNER JOIN
dbo.WIPTypes ON dbo.WIP.iWIPTypeFk = dbo.WIPTypes.iWIPTypePk
Where ((dbo.WIPTypes.cWIPTypeDesc = 'MilitaryInCombat') OR(dbo.WIPTypes.cWIPTypeDesc = 'Military'))AND (dbo.WIP.userid = @userid)
select @iMilitaryInWip = isNull(@iMilitaryInWip, 0)

select @totalmilitary = @totalmilitary + @iMilitaryInWip

select @totalpopulation = @totalmilitary + @oldpeasants
--print 'Total Population Now: ' + convert(varchar(235),@totalpopulation)

select @barrenland = (@totalland - (@totalbuildings+@totalhomes))

/* now we need to get their improvements for the max population */
Select @MaxPopImprovementBonus = round(((.20*(1-(exp(-(@MaxPopImprovementBonus)/(1000*(@totalland)+15000)))))*100),2)
if @MaxPopImprovementBonus > 0
BEGIN
Select @MaxPopImprovementBonus = @MaxPopImprovementBonus / 100
END
--print '@MaxPopImprovementBonus ' + convert(varchar(235),@MaxPopImprovementBonus)

--print 'Improvement Pop Bonus: ' + convert(varchar(235),@MaxPopImprovementBonus)

/* now we need to go get the racial bonus for the max pop for this user */
declare @MaxPopRacialBonus float
SELECT @MaxPopRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes INNER JOIN
dbo.Registered_User ON dbo.Race_Attributes.racefk = dbo.Registered_User.raceid
WHERE (dbo.Race_Attributes.raceattribute = 'population') and (Registered_User.Userid = @userid)
select @MaxPopRacialBonus = isNull(@MaxPopRacialBonus, 0)
--print 'Racial Pop Bonus: ' + convert(varchar(235),@MaxPopRacialBonus)

/* Add up our bonuses */
declare @TotalPopBonus float
Select @TotalPopBonus = @MaxPopImprovementBonus + @MaxPopRacialBonus + @ValorLevel
--print 'Total Pop Bonuses: ' + convert(varchar(235),@TotalPopBonus)

/* Calculate this users total number of peasants */
select @totalpopulationcalc = floor( (@barrenland*10) + (@totalbuildings*20 ) + (@totalhomes*30))
select @totalpopulationcalc = @totalpopulationcalc * (1+@TotalPopBonus)
--print 'Total Population should be after Calculation: ' + convert(varchar(235),@totalpopulationcalc)

/* now we need to go get the racial bonus for the growth rate for this user */
declare @GrowthRacialBonus float
SELECT @GrowthRacialBonus = raceattributevalue FROM Race_Attributes where racefk = @raceid
select @GrowthRacialBonus = isNull(@GrowthRacialBonus, 0)
--print 'Racial Growth Bonus: ' + convert(varchar(235),@GrowthRacialBonus)

/*
Find out if they have a spell that increases population growth on
iMagicPk for Harmony is 18
*/
Declare @iGrowthOn as int
Declare @iGrowthRate as numeric(4,3)

Select @iGrowthOn=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 13
select @iGrowthOn = isNull(@iGrowthOn, 0)

if (@iGrowthOn = 0)
BEGIN
/* by default growth is set to 3% */
Select @iGrowthRate = 1.03 + (@GrowthRacialBonus * .03)
END
else
BEGIN
/* with harmony growth is set to 4.5% */
Select @iGrowthRate = 1.045 + (@GrowthRacialBonus * .03)
END

/* Their total population now is not what it should be */
if (@totalpopulationcalc <> @totalpopulation)
BEGIN

if( @totalpopulationcalc > @totalpopulation) /* their population by calculation is higher than what they have now */
BEGIN

select @peasantstoinsert = floor(@oldpeasants*@iGrowthRate)

if ( (@peasantstoinsert+ @totalmilitary) > @totalpopulationcalc)
BEGIN

select @peasantchange = @totalpopulationcalc - @totalpopulation
select @peasantstoinsert = @totalpopulationcalc - @totalmilitary

END

else
BEGIN
select @peasantchange = @peasantstoinsert - @oldpeasants
END

END
else /* their population by calculation should is lower than what they have now */
BEGIN

select @peasantstoinsert = floor(@oldpeasants*.97)

if ( (@peasantstoinsert+ @totalmilitary) < @totalpopulationcalc)
BEGIN

select @peasantchange = @totalpopulationcalc - @totalpopulation
select @peasantstoinsert = @totalpopulationcalc - @totalmilitary

END

else
BEGIN
select @peasantchange = @peasantstoinsert - @oldpeasants
END

END

Update provincevaluesnew Set peasants = floor(@peasantstoinsert) where (userid = @userid)

/* Get this houlychangepk and Update this users peasant change for the hour */
SELECT @hourlychangepk = dbo.province_hourlychanges_values.hourlyvaluepk
FROM dbo.province_hourlychanges INNER JOIN
dbo.province_hourlychanges_values ON dbo.province_hourlychanges.hourlychangepk = dbo.province_hourlychanges_values.hourlychangefk
WHERE (dbo.province_hourlychanges_values.userid = @userid) AND (dbo.province_hourlychanges.hourlychangename = 'Peasant Change')

Update province_hourlychanges_Values Set hourlyvalue = @peasantchange Where (userid = @userid) and (hourlyvaluepk = @hourlychangepk)
END
else
BEGIN
/* Get this houlychangepk and Update this users peasant change to zero for this hour */
SELECT @hourlychangepk = dbo.province_hourlychanges_values.hourlyvaluepk
FROM dbo.province_hourlychanges INNER JOIN
dbo.province_hourlychanges_values ON dbo.province_hourlychanges.hourlychangepk = dbo.province_hourlychanges_values.hourlychangefk
WHERE (dbo.province_hourlychanges_values.userid = @userid) AND (dbo.province_hourlychanges.hourlychangename = 'Peasant Change')

Update province_hourlychanges_Values Set hourlyvalue = 0 Where (userid = @userid) and (hourlyvaluepk = @hourlychangepk)

END
GO

CREATE Procedure ChangeResourcesNew
(
@userid int
)
AS

set nocount on

Declare @iWizStr int
Declare @iWizStrMod int
Declare @iWizStrPK int
Declare @iSpyStr int
Declare @iSpyStrMod int
Declare @iSpyStrPK int
Declare @NumberofTowers int
Declare @MagicTheyHave numeric(9,0)
Declare @Magicpk int
Declare @MagicProduced int
Declare @goldtoinsert bigint
Declare @goldproduced bigint
Declare @goldtheyHave bigint

Declare @GoldPerHour bigint
Declare @GoldImprovementBonus numeric(9,0)
Declare @NumberofAlchemies bigint

Declare @crystalsproduced bigint
Declare @NumberofCrystalMines bigint
Declare @crystalstheyhave bigint

Declare @foodproduced bigint
Declare @foodeaten bigint
Declare @netfoodoutput bigint
Declare @fooddecayed bigint
Declare @foodtheyhave bigint
Declare @NumberofFarms bigint
Declare @Totalpeasants bigint
Declare @foodpk int
Declare @sMessage varchar(200)
Declare @TotalSlaves numeric(9,0)
Declare @IrrigationImprovementBonus numeric(18,4)
Declare @raceid int
declare @valorlevel int
declare @totalland numeric(9,0)
Declare @lumberproduced bigint
Declare @lumberrot bigint
Declare @netlumberoutput bigint
Declare @lumbertheyhave bigint
Declare @NumberofLumberYards bigint
declare @KingStatus int
declare @guardlevel int

-- get all our variables
select @Totalpeasants = peasants,@raceid = raceid,@ValorLevel = valorlevel,@TotalSlaves = slaves,@IrrigationImprovementBonus = irrigation,@foodtheyhave = food,
@MagicTheyHave = magic,@lumbertheyhave = lumber,@TotalSlaves = slaves,@GoldImprovementBonus = banking,@KingStatus = King,@guardlevel = guard,
@goldtheyhave = gold,@crystalstheyhave = crystals, @totalland = land from provincevaluesnew where userid=@userid

--get our race pk for the storyline bonuses
declare @GoldStorylineBonus float
declare @FoodStorylineBonus float
declare @LumberStorylineBonus float
declare @CrystalStorylineBonus float
exec sp_GetStorylineProductionBonus @raceid=@raceid,@GoldBonus=@GoldStorylineBonus OUTPUT,@FoodBonus=@FoodStorylineBonus OUTPUT,@LumberBonus=@LumberStorylineBonus OUTPUT,@CrystalBonus=@CrystalStorylineBonus OUTPUT

--check if natures mystery is on
Declare @iNaturesMystery as int
Declare @NatureBonus float
Select @iNaturesMystery=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 38 and userid=@userid
select @iNaturesMystery = isNull(@iNaturesMystery, 0)

if @iNaturesMystery > 0
BEGIN
select @NatureBonus = .025
END
else
BEGIN
select @NatureBonus = 0
END

--check if greatflood is on
Declare @iGreatFlood as int
Declare @GreatFloodBonus float
Select @iGreatFlood=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 26 and userid=@userid
select @iGreatFlood = isNull(@iGreatFlood, 0)

if @iGreatFlood > 0
BEGIN
select @GreatFloodBonus = .2
END
else
BEGIN
select @GreatFloodBonus = 0
END

Declare @iFoodOn as int
Select @iFoodOn=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 14 and userid=@userid
select @iFoodOn = isNull(@iFoodOn, 0)

Declare @iCrystalsOn as int
Select @iCrystalsOn=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 36 and userid=@userid
select @iCrystalsOn = isNull(@iCrystalsOn, 0)

Declare @iGoldOn as int
Select @iGoldOn=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 13 and userid=@userid
select @iGoldOn = isNull(@iGoldOn, 0)

--check if whisperingwood is on
Declare @iwhisperingwood as int
Declare @whisperingwoodBonus float
Select @iwhisperingwood=iMagicOperationPk from vw_CurrentMagic where iMagicOperationPk = 41 and userid=@userid
select @whisperingwoodBonus = isNull(@whisperingwoodBonus, 0)

Declare @TotalNonHomes numeric(9,0)
Declare @TotalJobs numeric(9,0)
Declare @EmploymentPercentage numeric(5,4)

/* first we need to get our total non homes for calcing the employment percentage */
SELECT @TotalNonHomes = SUM(totalbuilt) FROM BuildingsByUser WHERE userid =@userid and buildingsfk<>13

/* get our total employable peasants */
declare @EmployedPeasants numeric(19,0)
if (@TotalNonHomes * 25) > @TotalPeasants
BEGIN
Select @EmployedPeasants = @TotalPeasants
END
else
BEGIN
Select @EmployedPeasants = @TotalNonHomes * 25
END

-- now we need to get the number of docks they have.. docks add 25 bushels of food and 30 gold per dock
--irrigaition does not affect the production of docks food ..
Declare @NumberofDocks bigint
Declare @FoodFromDocks numeric(18,4)

SELECT @NumberofDocks = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Dock')

select @FoodFromDocks = floor(25 * @NumberofDocks) * (1+@GreatFloodBonus)

--get the total number of slaves and the gold they produce and food they eat
Declare @TotalFoodEatenBySlaves numeric(9,0)
select @TotalFoodEatenBySlaves = 1 * @TotalSlaves

/*
Second Step: Calculate their food output - Each farm produces 50 bushels of food
*/
Select @IrrigationImprovementBonus = round(((.20*(1-(exp(-(@IrrigationImprovementBonus)/(1000*(@totalland)+15000)))))*100),2)
if @IrrigationImprovementBonus > 0
BEGIN
Select @IrrigationImprovementBonus = @IrrigationImprovementBonus / 100
END

SELECT @NumberofFarms = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Farm')

/* now we need to go get the racial bonus for the food production for this user */
declare @FoodProductionRacialBonus float
SELECT @FoodProductionRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes WHERE (raceattribute = 'foodproduction') and (racefk = @raceid)
select @FoodProductionRacialBonus = isNull(@FoodProductionRacialBonus, 0)
--print '@FoodProductionRacialBonus: ' + convert(varchar(235),@FoodProductionRacialBonus)

if (@iFoodOn = 0)
BEGIN
select @foodproduced = (((@NumberofFarms * 50) + @FoodFromDocks) * (1+@IrrigationImprovementBonus+@FoodProductionRacialBonus+@ValorLevel+@NatureBonus+@FoodStorylineBonus))/* in bushels */
END
else
BEGIN
select @foodproduced = (((@NumberofFarms * 50) + @FoodFromDocks) * (1.1+@IrrigationImprovementBonus+@FoodProductionRacialBonus+@ValorLevel+@NatureBonus+@FoodStorylineBonus))/* in bushels */
END

/* figure in the employment level */
if @EmploymentPercentage < 1
BEGIN
Select @foodproduced = floor(@EmploymentPercentage * @foodproduced)
END
/*
End the second Step
*/

/* Step 1: Food variables */

select @fooddecayed = floor(@foodtheyhave * .01)

/* Food eaten must also take into account their total military as well as their peasants*/
Declare @iTotalMilitary int
Declare @iMilitaryInWip int
Declare @iTotalMilitaryAtHome int

SELECT @iTotalMilitaryAtHome = SUM(dbo.Race_Unit_Values.value)
FROM dbo.Race_Unit_Values INNER JOIN
dbo.Race_Units ON dbo.Race_Unit_Values.unitfk = dbo.Race_Units.unitpk
WHERE (dbo.Race_Unit_Values.userid = @userid)
select @iTotalMilitaryAtHome = isNull(@iTotalMilitaryAtHome, 0)

SELECT @iMilitaryInWip = SUM(dbo.WIP.iValue)
FROM dbo.WIP INNER JOIN
dbo.WIPTypes ON dbo.WIP.iWIPTypeFk = dbo.WIPTypes.iWIPTypePk
Where ((dbo.WIPTypes.cWIPTypeDesc = 'Military') or (dbo.WIPTypes.cWIPTypeDesc = 'MilitaryInCombat')) AND (dbo.WIP.userid = @userid)
select @iMilitaryInWip = isNull(@iMilitaryInWip, 0)

select @iTotalMilitary = @iTotalMilitaryAtHome + @iMilitaryInWip

/* now we need to go get the racial bonus for the food consumption for this user */
declare @foodconsumptionRacialBonus float
SELECT @foodconsumptionRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes
WHERE (dbo.Race_Attributes.raceattribute = 'foodconsumption') and (racefk = @raceid)
select @foodconsumptionRacialBonus = isNull(@foodconsumptionRacialBonus, 0)
--print '@foodconsumptionRacialBonus: ' + convert(varchar(235),@foodconsumptionRacialBonus)

select @foodeaten = (1+@foodconsumptionRacialBonus) *(((@Totalpeasants+@iTotalMilitary) * .25)) + @TotalFoodEatenBySlaves
--print '@foodeaten: ' + convert(varchar(235),@foodeaten)

select @netfoodoutput = @foodproduced - @foodeaten - @fooddecayed

/* Now we need to update this user's networth in the province attributes table */
Declare @foodtoinsert bigint
if (@foodtheyhave + @netfoodoutput) <= 0

BEGIN
select @foodtoinsert = 0

/* run the stored procedure that sends units and peasants on crusade for food */

END
else
BEGIN
select @foodtoinsert = @foodtheyhave + @netfoodoutput
END

/* if their food is zero or less send them a message that they are starving */
if @Raceid <> 1
BEGIN
if @foodtoinsert = 0
BEGIN
exec sp_LosingFood @userid=@userid
END
END

-- START CRYSTALS SECTION

SELECT @NumberofCrystalMines = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Crystal Mine')

/* now we need to go get the racial bonus for the crystal production for this user */
declare @crystalProductionRacialBonus float
SELECT @crystalProductionRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes
WHERE (dbo.Race_Attributes.raceattribute = 'crystalproduction') and (racefk = @raceid)
select @crystalProductionRacialBonus = isNull(@crystalProductionRacialBonus, 0)
--print '@crystalProductionRacialBonus: ' + convert(varchar(235),@crystalProductionRacialBonus)

if (@iCrystalsOn = 0)
BEGIN
select @crystalsproduced = (1+@crystalProductionRacialBonus+@ValorLevel+@NatureBonus+@CrystalStorylineBonus) * (@NumberofCrystalMines*15) /* in crystals */
END
else
BEGIN
select @crystalsproduced = (1.5+@crystalProductionRacialBonus+@ValorLevel+@NatureBonus+@CrystalStorylineBonus) * (@NumberofCrystalMines * 15) /* in crystals */
exec sp_DeleteRandomMines @userid
END

/* figure in the employment level */
if @EmploymentPercentage < 1
BEGIN
Select @crystalsproduced = floor(@EmploymentPercentage * @crystalsproduced)
END

--START THE GOLD SECTION
declare @GoldFromDocks numeric(9,0)
select @GoldFromDocks = floor(30*@NumberofDocks) * (1+@GreatFloodBonus)

--get the total number of slaves and the gold they produce and food they eat
Declare @TotalGoldFromSlaves numeric(9,0)
select @TotalGoldFromSlaves = 15 * @TotalSlaves

Select @GoldImprovementBonus = round(((.20*(1-(exp(-(@GoldImprovementBonus)/(1000*(@totalland)+15000)))))*100),2)

if @GoldImprovementBonus > 0
BEGIN
Select @GoldImprovementBonus = @GoldImprovementBonus / 100
END
--print '@GoldImprovementBonus ' + convert(varchar(235),@GoldImprovementBonus)

/* Find out how many alchemies they have - Alchemies produce 60 gold per hour */

SELECT @NumberofAlchemies = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Alchemy')

/* now we need to go get the racial bonus for the gold for this user */
declare @GoldRacialBonus float
SELECT @GoldRacialBonus = Race_Attributes.raceattributevalue FROM dbo.Race_Attributes WHERE (raceattribute = 'gold') and (racefk = @raceid)
select @GoldRacialBonus = isNull(@GoldRacialBonus, 0)
-- print 'Gold Pop Bonus: ' + convert(varchar(235),@GoldRacialBonus)

-- see if they get a king bonus
Declare @KingBonus float
if @KingStatus = 1
BEGIN
select @KingBonus = .05
END
else
BEGIN
select @KingBonus = 0
END

/*
Find out if they have a spell that increases gold production on
iMagicPk for Midas Touch is 13
*/

if (@iGoldOn = 0)
BEGIN
Select @GoldPerHour = floor( ((@EmployedPeasants*3)+(@NumberofAlchemies*60)+(@GoldFromDocks)+(@TotalGoldFromSlaves)) * (1+@GoldRacialBonus + @ValorLevel + @KingBonus + @NatureBonus + @GoldStorylineBonus + ((@GoldImprovementBonus))))
--add in our costs for the guard
if @guardlevel > 0
BEGIN
Select @GoldPerHour = @GoldPerHour * ( 1 - (.025*@guardlevel))
END
END
else
BEGIN
Select @GoldPerHour = floor( (1.1 * ((@EmployedPeasants*3)+(@NumberofAlchemies*60)+(@GoldFromDocks)+(@TotalGoldFromSlaves))) * (1+@GoldRacialBonus + @ValorLevel + @KingBonus + @NatureBonus + @GoldStorylineBonus + (@GoldImprovementBonus)))
--add in our costs for the guard
if @guardlevel > 0
BEGIN
Select @GoldPerHour = @GoldPerHour * ( 1 - (.025*@guardlevel))
END
END

/* figure in the employment level */
if @EmploymentPercentage < 1
BEGIN
Select @GoldPerHour = floor(@EmploymentPercentage * @GoldPerHour)
END

-- START THE LUMBER SECTION
SELECT @NumberofLumberYards = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Lumberyard')

/* now we need to go get the racial bonus for the lumber production for this user */
declare @lumberProductionRacialBonus float
SELECT @lumberProductionRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes
WHERE (dbo.Race_Attributes.raceattribute = 'lumberproduction') and (racefk = @raceid)
select @lumberProductionRacialBonus = isNull(@lumberProductionRacialBonus, 0)
--print '@lumberProductionRacialBonus: ' + convert(varchar(235),@lumberProductionRacialBonus)

if @iwhisperingwood > 0
BEGIN
select @whisperingwoodBonus = .3
exec sp_DeleteRandomLumberYards @userid
END
else
BEGIN
select @whisperingwoodBonus = 0
END

select @lumberproduced = (1+@lumberProductionRacialBonus+@ValorLevel+@NatureBonus+@LumberStorylineBonus + @whisperingwoodBonus) * @NumberofLumberYards * 40 /* in lumber */

/* figure in the employment level */
if @EmploymentPercentage < 1
BEGIN
Select @lumberproduced = floor(@EmploymentPercentage * @lumberproduced)
END

/*
End the Fourth Step
*/

/* Step 2: Lumber variables */

select @lumberrot = @lumbertheyhave * .01
select @netlumberoutput = @lumberproduced - @lumberrot

-- START THE MAGIC SECTION

SELECT @NumberofTowers = dbo.BuildingsByUser.totalbuilt
FROM dbo.Buildings INNER JOIN
dbo.BuildingsByUser ON dbo.Buildings.buildingsPK = dbo.BuildingsByUser.buildingsFK
WHERE (dbo.BuildingsByUser.userid =@userid) AND (dbo.Buildings.buildingname = 'Tower')

/* now we need to go get the racial bonus for the magic production for this user */
declare @magicproductionRacialBonus float
SELECT @magicproductionRacialBonus = Race_Attributes.raceattributevalue
FROM dbo.Race_Attributes
WHERE (dbo.Race_Attributes.raceattribute = 'magicproduction') and (racefk = @raceid)
select @magicproductionRacialBonus = isNull(@magicproductionRacialBonus, 0)
--print '@magicproductionRacialBonus: ' + convert(varchar(235),@magicproductionRacialBonus)

select @magicproduced = (1+@magicproductionRacialBonus) * @NumberofTowers * 30 /* in magic */

/* figure in the employment level */
if @EmploymentPercentage < 1
BEGIN
Select @magicproduced = floor(@EmploymentPercentage * @magicproduced)
END
/*
End the Fifth Step
*/

/* Step 5: Magic variables */
select @magicproduced = (@magictheyhave + @magicproduced)

/*Lets update thier Wizard and Spy Strength, at the rate of 3% an hour*/
Select @iWizStr = dbo.province_values.value, @iWizStrPK = province_attributes.attributepk
FROM dbo.province_attributes INNER JOIN
dbo.province_values ON dbo.province_attributes.attributepk = dbo.province_values.attributefk
WHERE (dbo.province_attributes.attributename = 'Wizard Strength') AND (dbo.province_values.userid = @userid)

Select @iSpyStr = dbo.province_values.value, @iSpyStrPK = province_attributes.attributepk
FROM dbo.province_attributes INNER JOIN

dbo.province_values ON dbo.province_attributes.attributepk = dbo.province_values.attributefk
WHERE (dbo.province_attributes.attributename = 'Spy Strength') AND (dbo.province_values.userid = @userid)

If (@iWizStr < 100)
Begin

--get the wizard guilds for the refresh rate
Declare @WizardGuilds float
Declare @TheWizardRefresh int
exec sp_CalculateBuildingPercentage @userid=@userid,@incomingvariable=17, @ReturnValue =@WizardGuilds OUTPUT

select @TheWizardRefresh = floor(@WizardGuilds / .1) + 5
declare @wizardstrengthnew int
if (@iWizStr + @TheWizardRefresh) > 100
BEGIN
select @wizardstrengthnew = 100
END
else
BEGIN
select @wizardstrengthnew = @iWizStr + @TheWizardRefresh
END
End

If (@iSpyStr < 100)
Begin
--get the spy guilds for the refresh rate
Declare @SpyGuilds float
Declare @TheSpyRefresh int
exec sp_CalculateBuildingPercentage @userid=@userid,@incomingvariable=19, @ReturnValue =@SpyGuilds OUTPUT

select @TheSpyRefresh = floor(@SpyGuilds / .1) + 5
declare @spystrengthnew int

if (@iSpyStr + @TheSpyRefresh) > 100
BEGIN
select @spystrengthnew = 100
END
else
BEGIN
select @spystrengthnew = @iSpyStr + @TheSpyRefresh
END
End

--update our values
Update provincevaluesnew set food = @foodtoinsert,crystals= (@crystalstheyhave + @crystalsproduced),
gold = (@goldtheyhave + @GoldPerHour),lumber = (@lumbertheyhave + @netlumberoutput),
wizardstrength = @wizardstrengthnew,spystrength = @spystrengthnew where (userid = @userid)

--check their slaves
exec sp_CheckDungeonsforRoom @userid

GO

What else can I do to speed this up? I am looking at having close to 10k users within a year and this can't take 10 minutes to run.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-06-26 : 14:09:26

Maybe you can split this job up somehow. Update 25% of the users every 15 minutes or so.

Also, do you have to run each of those stored procs in order for each user or can you do something like:

exec DraftJob 'for all users

exec ChangePeasantsJob 'for all users

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

calebbender
Starting Member

7 Posts

Posted - 2003-06-26 : 14:12:52

Yes, they must be run in the order of the first procedure.

I'd like to run them once per hour, all at once. There are other games out there, similiar to mine, that do this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources