March 11, 2003 at 4:36 am
hi,
Can anyone tell me what the best solution to this problem is - in terms of performance.
I have an SQL table which has 8 numeric columns holding values which make up rent for each a/c. I have three nested for loops using getrows to create arrays for these recordsets (Performance is important here). Anyway, the 2nd loop pulls the values from the Rent table to create an invoice amount for rent. I need therefore, to SUM all 8 columns from rent for each a/c. This means I need to SUM multiple columns for 1 row rather than multiple rows for 1 column.
Can I simply 'SUM(column1, column2, column3, column4 etc.....) AS RentTotal' or would it be a better idea to 'SELECT (column1+column2+column3+column4 etc.....) AS RentTotal'.
Thanks in advance,
Sam.
March 11, 2003 at 4:40 am
I would imagine Select SUM(Col1 + Col2 + etc) will be the fastest.
Much less work than nested loops.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 11, 2003 at 4:48 am
Sorry, the nested loops are nothing to do with the SUM function. I need the 3 loops to 1)get the account, 2)generate rent, 3)add invoice.
It's just that when executing the query at loop 2(the SUM) I wasn't sure what was faster.
Sam.
March 11, 2003 at 4:51 am
Well, doing it on a table (5 columns) and around 100000 rows is next to instant.
CP
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 11, 2003 at 4:55 am
Do you mean using SUM(col1+col2+col3) is almost instant?
March 11, 2003 at 4:58 am
Yes.
Even Col1 + col2 + etc is fast.
Which one do you want?
SUM(COl1 + Col2 + etc) will return the sum off all columns + all rows.
This could be a large figure.
Col1 + Col2 will give you n results of the summed columns.
CP
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 11, 2003 at 5:02 am
I think its the (col1 + col2 + col3) that I need.
Thanks for your help.
Sam.
March 11, 2003 at 5:10 am
I've just had a thought.
Can I add up all the columns and select them as 1 column, such as 'SELECT (col1 + col2 + col3....) AS RentTotal FROM Rent WHERE AccNo = '
Is this possible?
Or would it be better to select them individually and sum them in my VBScript code?
Thanks again.
Sam.
March 11, 2003 at 5:16 am
yup, Col1 + Col2 would give you one column with the summed values.
SUM() added the values of rows, not columns.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 11, 2003 at 5:22 am
cheers.
March 11, 2003 at 7:52 pm
Be careful using the Col1 + Col2, if one of the values is null then the resulting value will be null, which may not be what you want. ISNULL(Col1, 0) + ISNULL(Col2, 0) will generate what you want if the fields allow null.
Tim C.
//Will write code for food
Tim C //Will code for food
March 11, 2003 at 10:35 pm
ahh yes. Should have mentioned that. It bit me once....
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 13, 2003 at 10:28 am
As food for thought, I'd venture your design may not be a flexible as it could be. You may want to consider adding more tables for the elements that make up the rent. If, for example, there is a table called "Tenants" you could add another two tables called "Tenants_Pricing" and "Tenants_Pricing_Types." The Tenants_Pricing_Type table could have columns Id, Description, and Default_Value. The Tenants_Pricing table could have columns Id, Tenant_Id, Tenants_Pricing_Type_Id, and Value. Sample data for Tenants_Pricing_Type would be "1,Rent,$800"; "2,Utilities,$120"; "3,Maintenance,$50" and for Tenants_Pricing "1,1,1,$799"; "2,1,2,$119"; etc. To get the sum of the rent, "select sum(value) from Tenants_Pricing where Tenant_Id = @passed-in-id group by Tenant_Id" This removes the repeating group columns and enables flexibility. A potential tenant may want to negotiate a variance because of who know what, a bad view out the window, for example. New rows can be added to the Tenants_Pricing_Type table ("4,Bad View, -$50) and for that Tenants_Pricing table (999,999,4,-$49). The coding for the application doesn't have to change when the user wants to add more criteria to make up the rent. Of course, the trade-off is more development time in the short term (adding the Pricing_Type drop list, adding a maintenance screen for Pricing_Types) but potentially less in the long term. Some of the decision depends on how sure you are the 8 columns are the only ones the user will ever need. Cheers.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply