Re: Database performance

On Fri, 21 Sep 2001 16:14:06 +0200, "Marcel Braak" <mbraak_at_xs4all.nl> wrote:

>Hi there,
>
>I really need some help here.
>
>I'm developing an application in wich people can be planned on jobs.
>I need to display a diagram in wich all people are displayed in segments of
>30 minutes.
>Those segments show if a person is planned at that time or not.
>
>My problem is that my way of querying is very slow with many people
>
>Let say we have.
>
>100 persons
>I want to see a diagram from 7 to 7 hours in segments of 30 minutes.. so 12
>hours*2=24 segments
>thus for 1 person i need 24 queries to find out if that person is free or
>busy at a timesegment
>For all 100 persons i need 24*100=2400 queries.
>Let's say three people are accessing this diagram at the same time. The SQL
>server has to execute 2400*3=7200 queries.
>
>I'm using queries like
>SELECT persid FROM planning WHERE persid='name' AND starttime <= '10:00' AND
>endtime > '10:30'
>
>Is there a better way to query the database to find out if somebody is
>planned at a given timesegment?
>
>Thanx,
>Marcel
>
well, i don't know the one perfect solution, but there are a few things you could try.
first of all which DBMS are you using ?
to be tried immediately:
- in case you haven't already done, create indexes on persid - try using a stored procedure to access all of the people in a segment and then query the sp with the name you want:

create procedure people_in_segment ( start_time, end_time ) ....; and then
select * from peopls_in_segment ('10:00','10:30') where persid='name';

the improvement i hope you'll get is due to the fact that if your db supports caching (all high level DBs do ) and you have set a sufficient large cache, you'll find all of the people in a segment (i.e. the output of the sp) in the cache and the db is generally quite fast selecting on cached data,
moreover if your db supports shared caching on you o.s. (for instance interbase supports shared caching on solaris and windows but not yet on linux ) you'll feel the improvement in the conccurrent accesses too.

by the way, how are your tables structered ? i don't want to criticize, but it seems to me that your table is not really what theory would suggest.

let me know how you get it ! hope to have been useful,

Roberto Padovani