8 replies to this topic

[Eric The Red]

I've created a bit of code to display an availability calendar that has a double loop in it and in each iteration of the loop there is a call to the DB to see if that particular date is booked or not. Everything works but WOW is it ever slow at times.

The page is only 4K but it can sometimes take 10 seconds to appear. I did some checking and it is for sure the length of time for the DB to return the results. I open the DB connection, run the double loops, and then close the connection so it is not opening and closing in each loop.

When I asked someone who knows these things what he thought I should do, he said "Don't really have time to look at this in detail. Could probably be done faster in a function in the db for a bunch of it."

Great, but now I have to figure out how to create a function in the DB, in PostgreSQL in particular.

There are 3 SQL calls in the loops:

SELECT id FROM calendar WHERE prop_num=:id AND (check_in=':year/:month/:day')

If the record is not empty, it returns a "checked-in" image.

SELECT id FROM calendar WHERE prop_num=:id AND (check_out=':year/:month/:day')

If the record is not empty, and there has already been a return for the previous SQL call, it returns a "booked" image, otherwise it returns a "checked-out" image.

SELECT id FROM calendar WHERE prop_num=:id AND (':year/:month/:day' > check_in) AND (':year/:month/:day' < check_out)

Returns a "booked" image.

So the question is, how would I create a Function in the DB to handle some of this stuff internally? Any advice would be greatly appreciated.

[WingedPanther]

I would rethink this. What is the query that is driving this loop? Ideally, a join or (ick) a sub-select would let you retrieve EVERYTHING you need in one query.

[Eric The Red]

Hmm, yea, I have thought this but have yet to come up with a solution.

There is no query driving the loop actually. The loop is simply creating the calendar pattern.

In pseudo code:

Open loop 18 times (for 18 months)
Open loop vertical 6 times (for each week of a month)
Open loop horizontal 7 times (for each day of the week)
Close 7 times loop
Close 6 times loop
Close 18 times loop

And each time the loop runs, it runs the 3 calls. A lot I know, no wonder it is slow at times.

Thoughts on that?

[WingedPanther]

You are making 3*7*6*18 = 2268 database queries.

What's the database structure of calendar?

[Eric The Red]

Table structure is:

ID - int
day_num - int
prop_num - int
contact_id - int
booking_id - int
check_in - timestamp with time zone
check_out - timestamp with time zone
on_hold - smallint
booked - smallint
emr_added - smallint

Thanks very much for your help!

[Eric The Red]

I rebuilt the way the IF statements were working to first see if the most common results were true, where the date is in the middle of a booking or if the date is not booked at all and that shaved a couple of seconds off. Now down to about 3 seconds when the 'net is fast but it is far from instantaneous.

I also checked the indexes on the table and they seem relevant to the SQL calls.

I look forward to reading your next opinion in the AM.

[WingedPanther]

These two queries look like good starts:

SELECT id FROM calendar WHERE prop_num=:id AND (check_in >= ':min_year/:min_month/:min_day' and check_in <= ':max_month/:max_month/:max_day')
SELECT id FROM calendar WHERE prop_num=:id AND (check_out >= ':min_year/:min_month/:min_day' and check_out <= ':max_month/:max_month/:max_day')

[Eric The Red]

Hmm, smart.

Ok, so the table has date range records. The query you created pulls all of the date range records that apply so then I just have to figure out how to display that in a calendar is what you are saying? And if the date is not in one of the ranges, display blank. If it is a check in, display the check in image, if it is a check out, display the check out image and if it is both, or in the middle of a range, display the booked image?

Have you done stuff like this before? Do you have any recommendations as to the best way to display the calendar? Do you think my looping system is a waste of time? It does check to make sure the dates are correct though and does work with leap years. If, as you have recommended, I have already pulled all the records to begin with, I can then just apply the data through the loops and that should significantly cut down on the DB queries.

I will give this more thought. If you have any more insight, I very much appreciate it! Thank you so very much for the help!!!!!

Eric

[WingedPanther]

You can also add sorting in the query, so you get them in sequential order (makes the processing easier). You'll still need to do some looping, but if you get the data only once, that should speed things up a lot.

I've done a lot of database migrations, and other variations on queries, including meta-data queries. Databases are my friends :)