Bit of ASP Advice

suarve · 16 Mar 2009 at 16:45

Hi,

Am just making an admin type system for my shop, written in ASP. I have the store setting (E.g. show various areas of the site, min order value, meta tags etc etc) stored in my SQL Server database (the table is a simple two column table - Setting and SettingValue).

At the top of every page I obviously want to get the current value of each setting. As this action happens on every page I need to make sure I'm not doing anything silly and that I'm getting these settings in the most effecient way possible. I'm even more confused as my mate disagrees with whats the quickest way.

I'm currently using the following function to get the setting (the stored procedure is just the sql: select settingvalue from settings where settingname=somesettingname):

Code:

Sub getSettingKey(theKey)
    set ConnZ= server.createobject("ADODB.Connection")
    ConnZ.Open strConn
    set rsZ = ConnZ.Execute("EXECUTE spGetGLOBAL_SETTINGS " & theKey)
    varSetting1 = rsZ("SettingValue1")
    varSetting2 = rsZ("SettingValue3")
    rsZ.Close
    Set rsZ =Nothing
    ConnZ.Close
    Set ConnZ = Nothing
End Sub

Personally, I think think this is the fastest method as I open a single recordset, get all the setting values (there will ultimately be around 30 or 40 setting values), store them in a variable and close the recordset.

Now my mate reckons that converting my above procedure to a function to return the setting (E.g. getSettingKey("RequiredSettingName") is quicker). This way, I'd have to call the function multiple times (opening and closing multiple recordsets) in if statements, I imagine would be slower. I think using one of my varaibles I got above is quicker.

So my code would be something like:

Code:

'Sub to get all settings
If varSetting1 = 1 Then 
    'some html here
End If

If varSetting2 = 0 Then 
    'some html here
End If

and his would be:

Code:

If getSettingKey("settingname1") = 1 Then 
    'some html here
End If

If getSettingKey("settingname2") = 0 Then 
     'some html here
 End If

Any advice appreciated.

Thanks

SimonCHere · 16 Mar 2009 at 16:55

There will be a point somewhere when the one hit of yours out weighs the multi hits of his.

if you only need 1,2,3 etc settings for the page then his way will be quicker.
if you always need the 20,30 100 settings on every page then yours will be quicker.

suarve · 16 Mar 2009 at 17:39

Well, I'll have roughly 30-40 settings in total, not all used on page.

cjmUK · 17 Mar 2009 at 15:51

It's not clear to me what the settings are and how they are to be used, but given what I understand, I'd use your approach populate are series of Application variables.

There would be a small hit the first time you application is run (i'm guessing it will pretty much remain running), but after that calling the Application variable will have minimal impact. (Put your code in Application_OnStart)

TBH, it probably wouldn't be that bad if you followed you mate's approach. You are not exactly flogging the DB server.

Clearly, the worst solution would be to return all settings on every page, especially if you only wanted a few of them.

If you are strongly averse to using the Application variables, then I'd create a dynamic SProc that accepts a list of setting names as a single parameter, and returns a recordset containing all the settings you requested.