Horrible Performance - 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)

Horrible Performance

Author

Topic

calebbender
Starting Member

7 Posts

Posted - 2003-05-30 : 11:24:44

Well.. I have run into a bit of a problem.

I developed a browser based fantasy war game that currently has about 600 players and growing everyday. The entire site is run from a SQL 2k SP3 backend.

The problem is the game runs and must do updates to a users resources (ie. gold, crystals, land, etc) and needs to do these calculations once per hour per user. I have a job running that runs once a minute to see which users hour has come up to do the the processing against. Its currently taking so much time and system resources to update the users, which happens almost constantly that the system is always slow and the more users the slower it gets.

I've developed applications before, but nothing as user/database intense as this. I'm getting about 4 million hits a month with the users I have now.

I've added indexes and optimized the queries as much as I can for what I know what to do. Can anyone give me pointers/help so that I can fix this?

Ideally, I'd like to run the processing once per hour on the hour but that would take around 5 minutes to run and thats not acceptable.

Please help. Thanks alot..

BTW.. the game is located at agesofwar.com so you can check out the speed for yourselves.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 12:02:34

How big is the box? Whay aren't you doing the updates real time?

Brett

8-)

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-05-30 : 12:13:30

quote:

Whay aren't you doing the updates real time?

I checked out the site and since everything is turn based I think the scheduled jobs facilitate the "turns" concept.

Justin

"Contrary to its syntax GETDATE() still leaves you alone on Friday night."

calebbender
Starting Member

7 Posts

Posted - 2003-05-30 : 12:42:54

The game is real time in a sense. Everything is done on a time basis. For example, to train military it takes 8 hours.

So, I use a work in progress system where I query my WIP table to see if the time is up to process whatever record I have.

The process that slows everything down though is the changing or resources.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 12:51:26

I'm guessing all of the "resources" are in 1 table, right?

Brett

8-)

calebbender
Starting Member

7 Posts

Posted - 2003-05-30 : 12:55:02

Yeah.. basically.

The resources are in one table. It does many lookups to get the calculations for your gold output.

For example, It looks up your peasants, improvements, and spells. Then does a calculation based on those to return a number and then updates the table.

The SP is 595 lines.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 13:00:23

How many distinct types of resources are there?

Can you split them out in to separate tables and create a partitioned view with the current name of the table as to not impact the code?

Brett

8-)

calebbender
Starting Member

7 Posts

Posted - 2003-05-30 : 13:12:11

Well... Heres my setup right now.

I have a province attributes table and a province values table. The attributes are the names of the resources and the values for each user are saved in the province values table. There are currently 20 attributes and each province has one of each.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 14:12:51

As rows or columns?

My point though is you've got 1 (mostly) single point of activity...all transactions have to go through it

Can you break it up like:

Province_Gold
id
Gold_attribute1
Gold_attribute1

Province_Crystal
id
Crystal_attribute1
Crystal_attribute1

Province_Land
id
Land_attribute1
Land_attribute1

So if your existing table is call "Province", replace it with a partitioned view that is a union of all that..

Can you post your table DDL?

Cool looking site btw

Brett

8-)

calebbender
Starting Member

7 Posts

Posted - 2003-05-30 : 15:03:56

Thanks. Glad you like the site.

Heres my province_attributes table structure
attributepk int 4
attributename char 100
investable char 10
initialValue numeric 9
show char 10 0
statustype char 50
priority int 4
overallPriority int 4

Heres my province values table structure
valuesid numeric 9
attributefk int 4
userid numeric 9
[value] char 1000

I know the naming sucks but this game started a project for some of our interns about 2 years ago and some of the original table structures are still in place.

chadmat
The Chadinator

1974 Posts

Posted - 2003-06-02 : 00:27:11

Maybe it's all the freaking popups that is causing the performance prob.

J/K it's a pretty cool game, but the popup ads drive me crazy.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources