I am using classic ASP.
Records are grouped together by a GroupUnique number. Some groups are small with about 10 records, othere are larger at about 160.
For each record, I have about 50 columns of data that I need to display on a webpage. Because the 50 columns don't easily fit on the one page, I create two tables, each displaying 26 columns, the first columnn being an ID column. Due to the size of groups, sometimes the tables are very large - and when they get too big it overloads the server.
I think the main problem is the two tables. I use two recordsets (one of them is shown below - although instead of a SELECT * I do in fact name the columns needed for each table). I have to use two because the Recordsets don't like me using the ID column again - once it is used it is gone.
Is there a better way to store all of this information so that I can just use the one recordset? Possibly in an array? Is there a more efficient way of getting the data?
Recordset4__MMColParam1 = "1"
If (Scramble.Fields.Item("GU").Value <> "") Then
Recordset4__MMColParam1 = Scramble.Fields.Item("GU").Value
SQL Server 2000 and back don't have array function. However, I think SQL Server 2005 implemented some sort of array function, but don't quote me on that. Anyway about your two recordsets....
You can create a stored procedure which is just a sql file with your queries in it. The cool thing about the stored procs is you can write queries and store the results in variables and reuse the variable throughout the store proc. Also, you can execute multiple queries like your doing and print them out into one recordset which eliminates manaually inserting SQL into your website, and opening up double connections, which maybe your bandwidth issue.. (Its good for security purposes too.)
I'm not a pro at this, actually just graduated college and started my entry level software engineer position. So I may be missing some things. Your best bet is to go onto google and search SQL stored procedures.
Hmm - I converted the SQL queries to stored procedures today. It works on groups when small amounts of data is retrieved and displayed as before, but not when over 150 records are displayed.
If I just show the one table from one recordset, then it does work on the groups with 150 records, but of course I'm only showing half the data. I need to show the rest of it.
I therefore think that I am exceeding my memory. Is there anyway I can monitor how much stress I am placing on the server? Or any fixes?
Should I change the order at which I open and close connections? Currently I open all recordsets at the top of the page and close them at the bottom. Should I be opening one recordset, displaying that table and then closing it BEFORE I open another recordset up?
You can take help of PROFILER in this case to see what are running on SQL Server side and also use SYSMON (PERFMON) to cpature CPU, Physical disk & memory related counters. Refer to http://www.sql-server-performance.com for help on counters & performance related issues.