Hi, I am developing a database driven website.
I need to fire long SQL statement from ASP code, as database does not support store procedure:
ex.

sql="SELECT *"
sql=sql&" FROM tblaward RIGHT OUTER JOIN tblartist ON (tblaward.ArtistID = tblartist.ArtistID)"
sql=sql&" INNER JOIN tblperson ON (tblartist.PersonID = tblperson.PersonID)"
sql=sql&" LEFT OUTER JOIN tblnationality_lk ON (tblartist.NationalityID = tblnationality_lk.NationalityID)"
sql=sql&" LEFT OUTER JOIN tblcountry_lk ON (tblartist.BirthCountryID = tblcountry_lk.CountryID)"
Conn.execute(sql)


As sql variable is in fact a global variable shared by different asp pages, I though I could save sql into Application object, within the gloal.asa ( sub Application_onStart).

After trying it (website is working as normal) I can not figure out:

is this solution advisable, I mean, is it wise to store large variable into application object in terms of performance?

Generally speaking
could storing long variable in the application object slow down the server?




Imagine

This is infact a good idea, where as Sessions isn't the place for this, Application variabels are. You can put objects in app vars too to bost perf, but remember to do a smart clean up - ol' ASP does not have a garbage collection.
In some cases you might want to instantiate your app vars when the first user arrives eg Session_OnStart and clean up after the last visitor (user) has left. Remember you can set application variabels everywhere.

 - mega

... however, I noticed that the website was much slower using the application object with large variable, that is, it seems that there is more job for the server to do. In fact each asp page took more seconds to be displayed.
The code is much nither, but performance is worst. I took variables out from the application object and noticed that the same pages are now faster to download.
.....

more opinions?





Imagine

What is the database that you use?

_________________________
- Vijay G
Strive for Perfection

... I am using MySQL server v.4



Imagine

How often do you use the application data? If you place a SQL query or even the output of one (not the recordset, but an array of some sort) that you use on every page, I don't understand why your page execution is slow. If you on the other hand use it on one page, every page on your site will lose.

Can I see you code for instantiating the app variables?

 - mega
Moving to C# .NET

... let's consider 2 example:
1)
sql="SELECT concat(tblperson.fName,' ',tblperson.lName) as aname, "
sql=sql&" tblartwork.ArtWorkID, tblartwork.Show, tblartwork.ThemeID, tblartwork.Title, tblartwork.PriceOn, tblartwork.Price, tblartwork.Sold,"
sql=sql&" tblartwork.PriceRangeID, tblartwork.Width, tblartwork.isWhat, tblartwork.CategoryID,tblartwork.Height,"
sql=sql&" tblartist.ArtistID, tblartist.HasPainting, tblartist.HasArtPhoto, tblartist.HasPrint, tblartist.HasSculpt, tblartist.Artworks "
sql=sql&" from tblperson "
sql=sql&" inner join tblartist on (tblperson.PersonID=tblartist.PersonID) "
sql=sql&" inner join tblartwork on (tblartist.ArtistID=tblartwork.ArtistID)"

set rs=Conn.execute(sql)

2)
I store the same sql variable in Application("sql")=sql..
into the global.asa

then, in any asp page:
set rs=Conn.execute(Application("sql"))

The result is exactly the same, in option2 the code is much nither.
However, I am wandering if there is a different overload for the server, for the fact that, in the second case, the application object is involved itself.
The whole question rises as "ASP,ADO and XML complete" by Sybex, says ,in my undertsanding, not to store vars in application object if you do not have to. However, it does not say why. I got 3 books on ASP and they just scratch the surfice.







Imagine

About Application variables: http://www.15seconds.com/issue/010511.htm
About performance: http://www.15seconds.com/issue/000106.htm

I hope this can guide you.

 - mega
Moving to C# .NET

Imagine,

How about using stored proc than using any of those two options that were mentioned here?

Cheers!

_________________________
- Vijay G
Strive for Perfection

does MySQL version4 support store procedure?
I know that version 5 (beta is out now) does, hoever my host provider support v4.

Also MS SQL server is a much more expensive solution expensive solution.



Imagine