2 replies to this topic

[Eric The Red]

I am trying to provide an XML output of current availability of dates stored in a table with @ 6000 records and it is taking 30 seconds for the page to render.

My pseudo code goes something like this:

Select a list of property numbers
Loop1
Select a list of availability records
loop2
/loop2
/loop1

I need some help on properly indexing the table to work better for the SQL call. The internal loop call which I think would most benefit from the Index is:

SELECT id, day_num, prop_num, booking_id, check_in, check_out, on_hold, booked, prop_num FROM calendar WHERE prop_num = :availability_id

Any help would be appreciated. Links to Indexing tutorials, not writing the SQL code but understanding how to maximize your indexes or if someone wants to try to explain how the whole thing is supposed to work, that would be really cool!

Thanks in advance,

e

[WingedPanther]

It sounds like you just need to create an index on prop_num.

[Eric The Red]

Yea, did that. Made a small increase in performance. What I ended up doing is combining the two loops and that made a significant difference, now the page renders in 2 or 3 seconds. I also created indexes on a few other things where the 2 tables were joining etc.

I added a ticker to see where the major delays were and for some reason every time the first loop returned to run the second loop there was a 300 to 800 ms delay. Once I got rid of that, it runs quickly again.

Thanks for your help!