Per a thread a while back the discussion was along the lines of serving data up to the web quick.
Our app currently pulls a bunch of data to several query pages.
I have tried and not gotten the queries to return as fast as they do now which is a huge disappointment as the hardware is twice as powerful as our current production. I can get it pretty close on some thing but others are just plain slower.
My idea is to use the limit and offset to return just the first 50 records, if they hit next I can set the offset.
My understanding was this gets slower as you move further into the data, but we have several options to modify the search, and I do not believe our clients will page very far intro a dataset.
One problem I think I will have though is they currently have count of the records matching their request and I would like to keep that as a display field
So given a table of associates my default query will be something like
Select * from tblassoc where clientnum = ‘WAZ’ and isdeleted is false
The user could add stuff like and where first name like ‘Joel’
Currently it returns all records with a count and a display of the records your viewing like 1-50 of 470, next page is 51-100 etc.
Is there a fast way to get the count? Will this concept fly? Also I am getting heat that my search is now case sensitive. What is the best way to get a case insensitive search? I could use ~* or perhaps do an UPPER(firstname) in the select etc?
Thanks for any ideas here. I have tried playing with various settings and have not seen my times change much, I will persue this on the performance mailing list.
Joel Fradkin