pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SQL Query Performance tips
3+ messages / 2 participants
[nested] [flat]

* SQL Query Performance tips
@ 2005-01-10 10:00 Michael Ossareh <michael.ossareh@12snap.com>
  2005-01-15 08:26 ` Re: SQL Query Performance tips PFC <lists@boutiquenumerique.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Michael Ossareh @ 2005-01-10 10:00 UTC (permalink / raw)
  To: pgsql-sql

Hi All,

I have a partial table structure as below

tbl_user_main
	user_id pk
	user_level references tbl_level
	user_class references tbl_class
	user_level references tbl_level

tbl_level
	level_id pk
	level_name
	level_points

tbl_weapon
	weapon_id pk
	weapon_name
	weapon_level references tbl_level
	weapon_class references tbl_class
	weapon_alignment references tbl_alignment
	weapon_cost

tbl_class
	class_id pk
	class_name

tbl_alignment
	alignment_id pk
	alignment_name

tbl_user_weapon
	user_id references tbl_user
	weapon_id references tbl_weapon


I want to know how many weapons a given user has of a particular class or
alignment where the level of the weapons are equal or less than the players
own level. For this I have developed the following SQL:

1 | select alignment.alignment as alignment,
count(distinct(weapon.weapon_name)) as count from
2 | 	( select * from tbl_alignment where alignment_id != 1 ) alignment,
3 | 	( select * from tbl_weapon) weapon,
4 | 	( select * from tbl_user_main where user_id = $user_id ) person
5 | where weapon.weapon_id IN (
6 | 	select u.weapon_id 
7 | 	from tbl_weapon u
8 | 	where u.weapon_level <= person.user_level
9 | 	and u.cost = 0
10| 	or u.weapon_id IN (
11| 		select uu.weapon_id
12| 		from tbl_user_weapon uu
13| 		where uu.user_id = person.user_id
14| 	)
15| )
16| and alignment.alignment_id = weapon.weapon_alignment
17| group by alignment.alignment
18| order by alignment.alignment ASC;

To clarify lines 5 through 15 - a weapon can be free or cost some amount. To
track users that have bought a weapon there is the tbl_user_weapon table.
Every purchase gets listed in there. The count must take into account all
free weapons and weapons which the user has purchased. At the moment there
are some 300 weapons.

In the case above the "alignment" with id 1 is a catchall so I disregard it.
$user_id can be any user_id from tbl_user_main. The result of a query such
as this is along the lines of;

alignment | count
----------+-------
Shadow    | 4
Heavenly  | 6

This takes a long time to complete - circa 3 seconds. Which is fine when run
one off - but it appears in a section of a website that will potentially be
accessed a lot and I can see it causing a few issues in terms of table/row
locking as the game app uses these tables a lot. ideally I need it to run a
lot quicker. Can anyone see any ways to speed this up?

I have considered views but these seem to just be a way of aliasing a query
as opposed to the materialised views present in other RDBMS's. Short of
creating another 3rd form table that has 

user_id | alignment_id | count

Which would get updated upon each weapon purchase I cannot see a low
overhead way of getting the data. Creating a table such as this would need
to be a last resort as its maintenance will quickly become a headache due to
the number of purchase routes in the project.

Any help is much appreciated.

Thanks,

------------------------------
Michael Ossareh (M²)
Technical Manager
12snap UK Ltd



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Query Performance tips
  2005-01-10 10:00 SQL Query Performance tips Michael Ossareh <michael.ossareh@12snap.com>
@ 2005-01-15 08:26 ` PFC <lists@boutiquenumerique.com>
  0 siblings, 0 replies; 3+ messages in thread

From: PFC @ 2005-01-15 08:26 UTC (permalink / raw)
  To: Michael Ossareh <michael.ossareh@12snap.com>; pgsql-sql


	If I understand well a person has all the free weapons which have a level  
<= to his own level, and of course all the weapons he bought.

	1) get da weapons
	One query can only use one index. Bad for you !
	Let's split the free and non-free weapons.

	1a) free weapons
SELECT weapon_alignment, count(1) as cnt
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0
	GROUP BY weapon_alignment;

	No need for distinct anymore ! Note also that distinct'ing on weapon_name  
is a slower than on weapon_id.
	You can create an index on (weapon_cost,weapon_level) but I don't think  
it'll be useful.
	For ultimate speed, as this does not depend on the user_id, only the  
level, you can store the results of this in a table, precalculating the  
results for all levels (if there are like 10 levels, it'll be a big win).

	1b) weapons bought by the user

SELECT w.weapon_alignment, count(1) as cnt
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0
	GROUP BY weapon_alignment;

	You'll note that the weapons in 1a) had cose=0 so they cannot appear  
here, no need to distinct the two.

	2) combine the two

SELECT weapon_alignment, sum(cnt) FROM
(SELECT weapon_alignment, count(1) as cnt
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0
	GROUP BY weapon_alignment)
UNION ALL
SELECT w.weapon_alignment, count(1) as cnt
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0
	GROUP BY weapon_alignment)
GROUP BY weapon_alignment;

	You can also do this :

SELECT weapon_alignment, count(1) as cnt FROM
(SELECT weapon_alignment
	FROM weapons
	WHERE weapon_level < (user_level)
		AND weapon_cost = 0)
UNION ALL
SELECT w.weapon_alignment
	FROM weapons w, user_weapons uw
	WHERE w.weapon_id = uw.weapon_id
		AND uw.user_id = (the user_id)
		AND w.weapon_cost > 0)
GROUP BY weapon_alignment;

	How does it turn out ?








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: SQL Query Performance tips
@ 2005-01-26 13:02 Michael Ossareh <michael.ossareh@12snap.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael Ossareh @ 2005-01-26 13:02 UTC (permalink / raw)
  To: pgsql-sql

Hi PFC

Thanks for this! It has sped up complete - now in fact there is no delay!

A few tweaks had to be made to the code ; here it is:

select breakdown.alignment, sum(cnt) as num FROM
( (select alignment.name as class, count(1) as cnt
	from weapons,
	alignment
	where weapons.level < (select level_id from users where uid =
$userid)
	and cost = 0
	and alignment.id = weapons.align_id
	group by alignment.name)
UNION ALL
select b.class as class, count(1) as cnt
	from weapons w, user_weapons uu, alignment b
	where tu.weaponid = uu.weaponid
	and uu.user_id = ($userid)
	and b.id = tu.id
	group by b.class ) as breakdown
group by breakdown.class;

Essentially you had skipped a few brackets and I had forgotten to note one
of the tables. It works!

Thanks a lot!!

Mike




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-01-26 13:02 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-10 10:00 SQL Query Performance tips Michael Ossareh <michael.ossareh@12snap.com>
2005-01-15 08:26 ` PFC <lists@boutiquenumerique.com>
2005-01-26 13:02 Re: SQL Query Performance tips Michael Ossareh <michael.ossareh@12snap.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox