Are there any drawbacks to using application variables? I want to minimize the SQL connections on my website to improve performance. I think applicaton variables might be my answer. Is there a problem with creating too many application variables? Right now I have all our product brands in application variables and am now considering saving all product model names in application variables. Could this cause problems? Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Application Variables
- Thread starter Jeremy21b
- Start date
Well, if you put your SQL connection in the Application variable then you may have to consider the following things:
1) The SQL Connection will be open from the moment IIS starts to the moment it ends...not sure how you feel about that, but I'm not crazy about the idea
2) Every single visitor to the page wil be sharing 1 ADO connection. That means if 10 people show up at the same time 10 independant requests will get shoved down the line...not sure if concurrency would be an issue there and also not sure how user 10 would feel if they had to wait not only for the other 9 executiong but also the communication times.
Also (if i remember correctly) Application variables are stored on disk rather then in memory (RAM) making access time to those variables longer. Not sure if this is an issue that will effect you or not...
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
1) The SQL Connection will be open from the moment IIS starts to the moment it ends...not sure how you feel about that, but I'm not crazy about the idea
2) Every single visitor to the page wil be sharing 1 ADO connection. That means if 10 people show up at the same time 10 independant requests will get shoved down the line...not sure if concurrency would be an issue there and also not sure how user 10 would feel if they had to wait not only for the other 9 executiong but also the communication times.
Also (if i remember correctly) Application variables are stored on disk rather then in memory (RAM) making access time to those variables longer. Not sure if this is an issue that will effect you or not...
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
- Thread starter
- #3
I'm not sure what you meant by 'if you put your SQL connection in the application variable'. Our website uses SQL a lot and as a result we've had SQL server slow downs lately. To cut down on the SQL connections, I wanted to store some SQL query results in application variables. So instead of SQL getting the product model list, it just gets it from an application variable array. I don't think it would be a problem that application variables are stored on disk, as it would be preventing SQL queries which are even slower, right? I don't know what you why you were going on about ADO connections as I wanted to use application variables instead of some SQL queries. This would be eliminating ADO connections, as the information required by some SQL queries would be obtained from application variables.
Ok, I misunderstood, I thought you wanted to store the connection object in an Application variable, didn't make the connection in my mind with the latter portion of your post.
In this case it might be ok to keep the results of some queries in there to keep the number of requests to the db down, but you lose the dynamic value of the database unless you find a way to periodically update that applciation variable in case additions or changes were made to the db.
Is the problem with the db the fact that you have to many web requests coming in (high number of connections) or that you have more data in your db now (causing each individual connection to take longer)?
There may be ways to speed things up without resorting to more major changes, things like more efficient ways to process recordsets, etc.
Back to your question: If the informaiton your storing in the application variables is not frequently changing then, no I don't think it would be a problem. I can't answer on memory contraints, unfortunatly, but the IIS forum should be able to answer that one since it's less ASP and more IIS.
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
In this case it might be ok to keep the results of some queries in there to keep the number of requests to the db down, but you lose the dynamic value of the database unless you find a way to periodically update that applciation variable in case additions or changes were made to the db.
Is the problem with the db the fact that you have to many web requests coming in (high number of connections) or that you have more data in your db now (causing each individual connection to take longer)?
There may be ways to speed things up without resorting to more major changes, things like more efficient ways to process recordsets, etc.
Back to your question: If the informaiton your storing in the application variables is not frequently changing then, no I don't think it would be a problem. I can't answer on memory contraints, unfortunatly, but the IIS forum should be able to answer that one since it's less ASP and more IIS.
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
- Thread starter
- #5
Losing the dynamic aspect isn't a problem as the information rarely changes. It would be easy enough to update these application variables on the pages that would change this data. We currently have too many SQL requests and we need to eliminate some. Some recordsets might not be processed in the most efficient manner. What makes a recordset not efficient? Efficiency isn't always my strong suit. I often do things the way I could most easily get it to work. All I know about making recordsets efficient is that they should request the minimum data and it should be closed asap. Is there more? I guess I'll find an IIS forum to ask the application variable question on. I just wanted to know how efficient application variables are compared to SQL queries.
You probably will get some speed out of storing in Application variables, I just am not qualified to answer for maximum size or hard numbers for speed.
As for recordsets:
There are a couple things that will give you a little extra speed:
1) In your SQL statements specify field names in the SELECT, this has two advantages
a) Your onl;y getting back fields your going to use and not wasting communications time with data you won't use
b) If you use * then the database does a pre-query for all the filednames from that table, rebuilds the SQL statement, then executes your (altered) sql statement. Specifying the fields means less work for the db for a one time cost of about a minute of your time
2) Instead of looping through recordsets use the GetRows method to pull that data back into an array. This will give you extremely noticeable results for pages that are just listing out lots of records
3) Instead of using the RecordSet.Open method, use the Connection.Execute method to execute into a recordset. Cuts out a little overhead on forcing the recordset to make the query for you.
4) In multi-table SQL statements use JOIN statements instead of comma delimited lists of tables:
With SELECT statements the db makes a temporary table based on the FROM clause then filters the data according to the WHERE and GROUP BY and then re-orders by the ORDER BY
If you use commas in the WHERE the db first creates a temp table of every combination of records in those tables, then applies the where
If you use JOINs (JOIN, INNER JOIN, etc.) then it makes that first temporary table based on the Join ON conditions
Example:
You have 3 tables
Owner - 10 records
CarType - 5 records
Manufacturer - 2 records
pretend one-to-many between owner-carType and carType-manufacturer. Pretend there is a manufacturer called Ford, there are 2 records in carType that are Fords, and there are 5 people that own those 2 types
SELECT * FROM Owner, CarType, Manufacturer WHERE Manufacturer.Name = 'Ford'
This makes a temporary table of 10 * 5 * 2 records, then loops through the 100 records filtering for Manufacturer.name=ford to get the 5 records that should result
SELECT * FROM ((Owner JOIN CarType ON Owner.carName = CarType.Name) Join Manufacturer ON Manufacturer.Name = CarType.manName) WHERE Manufacturer.Name = "Ford"
This one creates a temp table of only meaningful data, so it will have 20 records. It then loops through those 20 looking for the Ford's
Anyways, hadn't planned on going so long. Think there are probably some threads still hanging around on db efficiency if you search from the search tab above, these were just the major things that came to mind.
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
As for recordsets:
There are a couple things that will give you a little extra speed:
1) In your SQL statements specify field names in the SELECT, this has two advantages
a) Your onl;y getting back fields your going to use and not wasting communications time with data you won't use
b) If you use * then the database does a pre-query for all the filednames from that table, rebuilds the SQL statement, then executes your (altered) sql statement. Specifying the fields means less work for the db for a one time cost of about a minute of your time
2) Instead of looping through recordsets use the GetRows method to pull that data back into an array. This will give you extremely noticeable results for pages that are just listing out lots of records
3) Instead of using the RecordSet.Open method, use the Connection.Execute method to execute into a recordset. Cuts out a little overhead on forcing the recordset to make the query for you.
4) In multi-table SQL statements use JOIN statements instead of comma delimited lists of tables:
With SELECT statements the db makes a temporary table based on the FROM clause then filters the data according to the WHERE and GROUP BY and then re-orders by the ORDER BY
If you use commas in the WHERE the db first creates a temp table of every combination of records in those tables, then applies the where
If you use JOINs (JOIN, INNER JOIN, etc.) then it makes that first temporary table based on the Join ON conditions
Example:
You have 3 tables
Owner - 10 records
CarType - 5 records
Manufacturer - 2 records
pretend one-to-many between owner-carType and carType-manufacturer. Pretend there is a manufacturer called Ford, there are 2 records in carType that are Fords, and there are 5 people that own those 2 types
SELECT * FROM Owner, CarType, Manufacturer WHERE Manufacturer.Name = 'Ford'
This makes a temporary table of 10 * 5 * 2 records, then loops through the 100 records filtering for Manufacturer.name=ford to get the 5 records that should result
SELECT * FROM ((Owner JOIN CarType ON Owner.carName = CarType.Name) Join Manufacturer ON Manufacturer.Name = CarType.manName) WHERE Manufacturer.Name = "Ford"
This one creates a temp table of only meaningful data, so it will have 20 records. It then loops through those 20 looking for the Ford's
Anyways, hadn't planned on going so long. Think there are probably some threads still hanging around on db efficiency if you search from the search tab above, these were just the major things that came to mind.
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
- Thread starter
- #7
Ok I don't feel as incompetent now haha. I knew most of those optimization methods. I guess they could be implemented a bit more on our website. I didn't know about the inefficiency of objRS.open though. Is the getrows method really worth using all the time? I use it if I'm just getting one field from the database. Say you're just populating a drop down menu, is it worth using getrows and putting it in an array? Or is it best to just use getrows when there's more stuff going on inside the recordset loop? By the way Tarwn, thanks for all the help so far.
With a small recordset GetRows is actually a little less efficient because of the overhead, but then again with a small recordset efficiency (or lack of) isn't as noticeable. The larger the recordset gets, the better GetRows will work (by comparison). I posted a resource on my site at some point about this: but I can't seem to find the benchmarks I did on a GetRows vs Loop...I lost most of my web folder about 8 months ago including a large number of benchmark pages and such so I don't have numbers handy.
I would have to say that as a rule of thumb any loop of more then 20 or 30 records would probably be better of for having GetRows, plus it frees up that extra memory because once you use GetRows you can get rid of the Recordset and DB connection...
I'll be back in a bit, I have an 8Mb acces db hanging around somewhere, I'll try some quick benchmarks and let you know where the line is bewteen looping and GetRows,
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
I would have to say that as a rule of thumb any loop of more then 20 or 30 records would probably be better of for having GetRows, plus it frees up that extra memory because once you use GetRows you can get rid of the Recordset and DB connection...
I'll be back in a bit, I have an 8Mb acces db hanging around somewhere, I'll try some quick benchmarks and let you know where the line is bewteen looping and GetRows,
-T
01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question