Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment   Page 1 of 2      1   2   Next
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #1 

Cache DB Requests to Speed up Iron Speed

Bryan Patrick –Pseudo Consulting

December 27, 2011

 

Introduction

Here is a simple way to greatly speed up some parts of your Iron Speed application.

One of the greatest things about Iron Speed is how itgenerates code to populate and handle Foreign Keys.

The problem is that Iron Speed does not know if the data in your foreign key tables rarely change so it will generate code to read values even as you simply paginate through the results in a basic table, or populate a drop-down list every time you edit a record with that foreign key field.

For  example, I have an “Apartments” table with 2 Foreign Keys. One to a “Status” table, and one to an “AirportCodes” table (markets).   The AirportCodes table has 140 records, the Status table has 26 records.  These tables change rarely.  There is no reason to continually read these from the database each time a search page is displayed or an Edit Record page is used.

Here is the very simple page generated by the Application Wizard.  As you can see Iron Speed automatically generated the  2 filtersfor my 2 foreign keys (Status and Airport Code).

 

 

When we turn on “Tracing and Event Logging…” we can see that Iron Speed is hitting each FK table TWICE. Once to get the count of records, and once to read them.  These 2 steps are performed twice since I have 2 filters.

Here is the tracing for the 140 record AirportCodes table.  Notice that the read for the AirportCodes table is between the end of the read for “Apartments” and before the read for “ALNStatusCodes”.

 

We can cache the results and only re-read the database every 10 minutes or whenever the application is reset.

Luckily this change is very easy to accomplish.

 

Edit the “Controls” file for your page and add this line at the top:

 

using System.Web.Caching;       

 

Go to design mode and select the filter you want to cache.  Click on the code tab, then the “Populate()”function.

We only need to wrap the “GetRecords()” call to accomplish caching of the FK table.

 

We need to change this: 

itemValues = ALNAirportCodesTable.GetRecords(wc, orderBy, pageNum,maxItems);

into this:

string cacheKey = "AirportCodes";

itemValues=(ALNAirportCodesRecord[]) this.Page.Cache[cacheKey];

if (itemValues == null)

                   {

                       itemValues = ALNAirportCodesTable.GetRecords(wc, orderBy, pageNum,maxItems);

                       this.Page.Cache.Insert(cacheKey,itemValues,null,Cache.NoAbsoluteExpiration,TimeSpan.FromSeconds(600));

                   }

     

Notice that the actual read from the database is not changed.  We simply check to see if we’ve already read (and cached) from the database and if not, read the values from the database and store in the .NET cache.

The TimeSpan.FromSeconds(600) means expire this cached data in 10 minutes.   

Now after this change, loading the page and paginating does not incur another 2 database hits (and 140 records read/transferred)!

You can also use this technique to populate foreign key drop-downs where the data used to populate does not change (or change often). 

 

  

Here I’ve only shown the changes for one filter.  In my production application I’ve changed BOTH filters and the page loads quite a bit faster.

Considerations

 

1.      The caching system may remove your cached data if memory is low on the server.  This is not a problem since you application will work as if it was unchanged.

2.       If someone DOES add a record to your foreign key table while the data is still cached, it will not appear in your filter list (or drop-down list) until the timeout has expired.  Therefore this technique is most helpful for tables that don’t change (or don’t change often).

 

 

Conclusion

With a simple modification to the Populate() function, it is possible to utilize .NETcaching to improve your applications performance and database usage.  

 

About the Author

C. Bryan Patrick II has been a professional databasedeveloper since the age of 16.  He has aBachelor of Arts in Computer Science from The University of Texas atAustin.  At 26 Bryan received a US Patent(# 5,142,624) for work done during his college years.  He is a member of the Microsoft PartnerProgram and has been self-employed for the last 10 years as a small business ITconsultant and web application developer.

Attached Images
Click image for larger version - Name: Image1.png, Views: 976, Size: 57.52 KB  Click image for larger version - Name: Image2.png, Views: 974, Size: 167.38 KB  Click image for larger version - Name: Image3.png, Views: 972, Size: 67.56 KB  Click image for larger version - Name: Image4.png, Views: 962, Size: 83.80 KB  Click image for larger version - Name: Image5.png, Views: 961, Size: 72.23 KB 

 
Attached Files
doc DBCaching.doc (551.00 KB, 114 views)


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #2 

 

This technique should work with versions 7 & 8 with no problems too.

 

 


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,948
Reply with quote  #3 
Thanks Bryan! 
Since the Tool, we are using is Iron Speed and everybody is expecting a speedier execution on top of quick application generations, that must be a great opener to a reality of performance.

Cheers,
Jimi J

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

fjrodriguezcom

Registered:
Posts: 91
Reply with quote  #4 
Great job
__________________
https://es.linkedin.com/in/fjrodriguezcom
antsht

Registered:
Posts: 4
Reply with quote  #5 

Impressive!!!

It works for me in ISD6 using VB too.

in populate DDL i changed this:

----------------------------------------

Me.SpecialnostFilter.Items.Clear()
Dim itemValue As Sprav_specialnostRecord
  For Each itemValue In Sprav_specialnostTable.GetRecords(wc, orderBy, 0, maxItems)
   ....

----------------------------------------

into this:

----------------------------------------
Dim cacheKey As String = "specialnostFilter"
Dim itemValues As Sprav_specialnostRecord()
itemValues = CType(Me.Page.Cache(cacheKey), Sprav_specialnostRecord())

If (itemValues Is Nothing) Then
itemValues = Sprav_specialnostTable.GetRecords(wc, orderBy, 0, maxItems)
Me.Page.Cache.Insert(cacheKey, itemValues, Nothing, Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(600))
End If
   Me.SpecialnostFilter.Items.Clear()
Dim itemValue As Sprav_specialnostRecord
    For Each itemValue In itemValues

....

miles

Avatar / Picture

MVP Consultant
Registered:
Posts: 1,656
Reply with quote  #6 

It would be nice for Iron Speed to embrace this as a performance option for selected tables and views.  Bryan, how can we reset the cache programatically to reload on demand?


__________________
Miles Gibson, MScIS
Iron Speed MVP
Senior Consultant, Principal
Milestone Software Inc.

http://www.ironspeedmvp.com\localizer 
Email: miles@milestone.ca
Milestone Localizer: Full Localization for your Iron Speed applications!
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #7 

Miles,

 

That would be sweet.  I thought about changing the baseclasses to add a "CacheTimeout" property and caching at that level but any IronSpeed upgrade would remove those changes.  Maybe they'll like my solution and implement it in a future version.   In my testing it really does seem to help performance a lot.

 

 

I also thought about adding the where clause to the cache key so it could handle multiple difference queries.  This is just the simplest case of reading all the values to populate in the drop-down or filter.

 

You should be able to simply call this.Page.Cache.Remove("key") to remove the value from the cache and force a reload the next time.

 

 


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
porphi

Registered:
Posts: 647
Reply with quote  #8 

Bryan,

Another option for caching is to do it system wide in the generated data access layer for a system wide implementation.  You can find a post here where I answered a forum users question about caching back in version 6 that might be of interest.


__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
timt

Avatar / Picture

Iron Speed MVP
Registered:
Posts: 556
Reply with quote  #9 
Hi

We delved into the bowels of the datalayer and cached the results there so all calls to the db could be cached in 1 place. We then created a hit list of the type tables that benefit from this and wrote a small admin function for the user to add/remove tables from the hit list, clear the cache, etc. Worked well for us, works best with data that does not change much of course.
Given the huge amount of querying a simple page does (filters, keys, dropdowns etc) over and above getting the basic dataset this can speed up page loads considerably especially with a slow sql server.

HTH

__________________
Tim Titchmarsh Iron Speed Consultant MVP
London UK based
+44 (0)1621 835002
http://www.lightspeeditsolutions.co.uk/dotnetarchitect
timt@dotnetarchitect.co.uk
timt@lsits.co.uk
didier

Registered:
Posts: 42
Reply with quote  #10 

Your solution works very well for an alone dropdownlist (thank to share it).

 

But I try to use the same in case of dropdownlist depending of other dropdown list result (for example Country; State; City) the result isn't usable because if we change the Country from the country Dropdownlist the State Dropdownlist shows always the states from the previous Country.

 

The very good solution will be to cache the entire list and apply the "where condition" on the cache. Do you have any idea to make these feature?

 

Thank one more time for the good job.

 

Didier

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #11 

 

Sorry for the late reply. Was on vacation.

 

You could invalidate the state cache when the country cache changes and the city cache when the state cache changes.  See above on how to remove the current items from the cache.  That would apply the new filters and then those would remain until the "parent" drop down was changed again.

 


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
Juanro

Avatar / Picture

Registered:
Posts: 104
Reply with quote  #12 

Make the Country.SelectedValue part of the cache key, something like:

 

string cacheKey = "AirportCodes" + CountryControl.SelectedValue;

 

Hope this helps, regards

 

Juanro

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #13 

 

That's an even better idea!

 


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
cpet

Registered:
Posts: 84
Reply with quote  #14 
using ISD 9.1E
Where exactly does this "using" statement go? -

Edit the “Controls” file for your page and add this line at the top:

using System.Web.Caching;   

When I put it in my Controls file for the page I get a message saying it can't be used outside of a method.
I tried the very top, inside Imports region, inside Section 1.
Also - is the ";" part of the statement?

thanks so much for this 
Carla

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #15 
Carla,

Are you using C# or VB?

My code is for C# and you should see a bunch of other "using" statements at the top of the file.


__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Download Iron Speed Designer

Terms of Service Privacy Statement