I guess I did not respond to a lot of the original question. (Got a phone call and
was side tracked) There should be no speed difference between a temp and static view. Proper indexing on the tables is a must for performance when using views. (Either type) The linking column needs to be indexed. Rule of thumb... the fewer the linking columns, usually the better performance. But they need indexed if the tables have larger number of rows in them. In my experience, it seems that reports are faster when temp tables are used versus views. However, this is not scientific proven, just my opinion. I am not sure about DB grids. I would not expect compacting the form or compiling would effect the db grid speed much. Compacting might cause the form to startup faster, but not perform faster. If your view runs very fast with a select command, but is slow in the form DB grid, you might have double where clauses. I.E. your view definition might be "Where t1.part# = t2.part#" and then your DB grid where clause be something else, such as "Where company = 'ACME'. You might get better performance using a temp view, defining it at form startup or even when moving into the dbgrid with the single where clause "Where t1.part# = t2.part# and company = 'Acme'. This is another reason to use tempviews. Create them on the fly. The above is not a confirmed logic. I am unsure if the the DB grid executes the view first and then narrows the data. But if so, having two where clauses would be slower. -Bob ----- Original Message ----- From: "Paul InterlockInfo" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, January 21, 2010 5:10:36 PM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Re: form /db grid / network Thank You Bob ;) I will quit manipulating the views etc.. and concentrate on form options/settings. Sincerely, Paul Dewey Just need to know where to set focus (-ON) gig not that it was off mentally. From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, January 21, 2010 4:48 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: form /db grid / network All views are simply stored "Select" commands. There is no data stored or collected ahead of time. The select command is fired at the time of use. One of the main differences between temp views and permanent ones are that temp views are session specific. I.E. only accessible by the session that created it. This is very handy in a multi user environment. For example, a temp view similar to : set var vUserID = (CVAL(NetUser)) create temp view tmpView (UserID, State, TotalSales) + as select UserID, State, Sum(Sales) from TableX + group by UserID, State where UserID = .vUserId would retrieve and sum only records where UserID was the login name of the user on the PC. Your application code could use the above to hold data for any user and any number of users at any given time, without a lot of special coding and is easier than with a permanent view. This is a simplistic example but may point to the idea for temp views and tables. -Bob ----- Original Message ----- From: "Paul InterlockInfo" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, January 21, 2010 1:50:26 PM GMT -06:00 US/Canada Central Subject: [RBASE-L] - Re: form /db grid / network I have a form that is basically a dbgrid type that uses a temp_view and the temp_view is based off two temp_tables. It has one DB Grid cell click eep, I am assuming these are not implemented until you click on them? So out of curiosity are views static or populated on request? If you have a view in a db is that view ready and waiting WHEN you use a report or view. Or is it populated when you browse and/or use in a report or form. (A discussion I had and I could not answer) if true, then the assumption would be that in some cases (local) a temp_view has limited value. Using dbgrid: Faster using a: 1. Temp_Table 2. Temp_View ? I could project the view (temp or not) into a table then use that Table verses using a view if it is faster. I tried both and could not see a difference. This form I am using is sluggish compared to what I am used to. No special calls/eeps/etc… stripped it down to bear dbgrid only. I have the sort and where clause being done in the temp_table & temp_view Now that part is real fast. Then the form fires and bang, the slow part kicks in. I have tried running the form compacted within the compiled eep and not compiled. Over the network is the same as local. As Expected ;) a small difference but not noticeable really. I tried enhanced and not. Heck I tried just using db_edit and it was the same. Is it bad news to use a db_grid? In this case using a view even a temp view? All the other forms are fast (Most one table based, order entry etc…). Way fast! Even on a Networked. Just this one pulling two tables into a view and using that view to drive the form. I tried temp_view and static_view (one in the db, not temp) Sincerely, Paul D. Background Information: I did the usual unload/run output/pack/error messages on/latest ver. 9.0 XP-3 and same on Win 7.0, Novell-Suse Linux Network 100/100/1000 RFF Code Example w/edits that are not important for this example, but wanted to show incase. This is in a bit button on the rff. CONNECT apar_part PAUSE 3 USING '1 Calculating 125,000+ records... Please Stand By ... ' + CAPTION 'BE PATIENT! - BE PATIENT! - BE PATIENT!!!' + ICON WINDOWS OPTION GAUGE_VISIBLE ON + |GAUGE_COLOR RED + |GAUGE_INTERVAL 50 + |MESSAGE_FONT_NAME VERDANA + |MESSAGE_FONT_SIZE 10 + |MESSAGE_FONT_COLOR WHITE + |THEMENAME Steel Blue SET ERROR MESSAGE 2758 OFF PROJECT TEMP treturn FROM return USING returndate,-- plus 5 more columns -- 3k rows PROJECT TEMP td_invoice FROM d_invoice USING custno, -- plus 5 more columns --67k rows SET ERROR MESSAGE 2758 ON SET ERROR MESSAGE 677 OFF DROP VIEW tvreturn CREATE TEMP VIEW tvreturn AS SELECT t1.returndate,--etc…. + FROM treturn t1,td_invoice t2 WHERE t1.id= t2.serial_id and t2.invoicedate > t1.returndate ORDER BY t2.serial_id,t2.invoicedate --8k rows SET ERROR MESSAGE 677 ON Cls -- Just using this part to watch the process change and it does the above part FAST then below pause for 3 seconds approx then wait! Cls is on before start form eep. So I can see that happen. PAUSE 3 USING '2 Loading 10,000+ records into the Form... Please Stand By ... ' + CAPTION 'BE PATIENT! - BE PATIENT! - BE PATIENT!!!' + ICON WINDOWS OPTION GAUGE_VISIBLE ON + |GAUGE_COLOR RED + |GAUGE_INTERVAL 30 + |MESSAGE_FONT_NAME VERDANA + |MESSAGE_FONT_SIZE 10 + |MESSAGE_FONT_COLOR WHITE --+ --|THEMENAME Steel Blue EDIT USING auditinvtoreturn DISCONNECT apar_part RETURN
