What is the memory use on my server of grids?

Scriptcase 8 Discussion
#1

I’m trying to understand how much memory my user will use on my server when the user uses a Grid app. I need to know if my SC8 app retrieves all the records from the database into memory, and hold them there, then uses it’s own pagination to pull back records in groups (10, 20, 50, etc) or, is the SC8 app smart enough to parse the SQL and pull into memory, only the records it needs. Please indicate which scenario is correct:

(In both scenarios, the user is accessing a table with 7,000 rows)
(In both scenarios, the app is started in non-filtered, non-quick searched, non-advanced searched, non-dynamic search mode… just regular mode)

SCENARIO A:

  • User clicks menu item to start the app
  • The App’s SQL is submitted unchanged, that pulls all records from the DB. There is no WHERE clause.
  • 7000 records are held in memory on the server (for each user that is currently using this same app
  • Apache/SC8 PHP paginate through the records held in memory, sending only 10,20 or 50 over the wire to the user at a time

SCENARIO B:

  • User clicks menu item to start the app
  • A modified version of the SQL is submitted to the DB. It has been modified to include:

FIRST 10 SKIP 0

so it now will only pull 10 records from the DB. There is no WHERE clause.

  • 10 records are held in memory on the server (for each user that is currently using this same app

  • User clicks for next page

The SC8 app changes the FIRST/SKIP to read

FIRST 10 SKIP 10

and so on, meaning pagination is done via SQL (holding in memory only those records that meet the current page request) and not through PHP where ALL the records are held in memory, and PHP sends only a page over the wire at a time.

Thanks for your help.

#2

dude, why this complication? i mean use pagination by default and ask user to select/change based on the request… having 7000 records at once could load your db connection/server and even browser cache… you have powerful features you can utilize, make for example having 10 records first, then allow user to change that by having a list of 100, 200, 300 to display, at least scriptcase will load them on request… just saying

#3

I don’t think you understood my question Mike. The SC grid “knows” what the total record count is, plus it paginates. I want to know how it knows the total count, and how it pulls back say, page 15 of a 400 page table. Either it’s holding all the records in memory at the server, (just using the SQL statement I supplied to it) or, it is using intelligent parsing/construction of SQL, based on my original SQL statement. I’m curious as to which method it uses. Most app components I use are not that smart, they would just load the entire table if I gave it the same SQL statement as what I’m using in my SC “SQL” setting.

#4

yes i guess it is limites the records all times, check the sql in debug… always is not what you want :slight_smile: i mean it is always revising the statement before applying it, and also you have the infinite feature in the grid presented few versions back, it is nice, you can make sure it is not loading all at once and have it nicely loaded… check that

#5

Hi Mike, how do I check the SQL in debug? I’m still learning how to use SC to maximum benefit. If SC is revising the SQL that would be best. That way it would not be loading all records at once, ever. It might also mean that the total count is being done as a SELECT COUNT(<primary field>) instead of a <table_component>.RecordCount. I’m used to working with middleware that I can get into and tinker with. SC is not like that.

#6

well, from application, enable debug and you can see the sql statements that will be used… it is recommended to add complete statement like limit xx then sc will obay, otherwise, how i know sc is revising the statement… many times noticed that is adding order by, without we add it in the sql part… unless added by developer, then sc will obay… adding count, same thing, limit, same thing… just enable the debug and see how the code is being executed… again, having global variables in sql statement is possible, so you can actually tweak your statement as rquired, given that you also have many options to limit and customize the grid display… see also the infinit scrolling option from grid settings, limit records, paginate…etc.

#7

Very good advice Mike. Thank you…

UPDATE: I found the Application/Settings/ Debug Mode. It answers my question perfectly.