sql query time

[Luis G]

how much time do you think that executing around 70 queries that are similar to this one should take to complete?

consider that the sum will become of about 250,000 records for each querie.

select sum(br1_kwh),sum(br1_impener)+sum(br1_impmant)+sum(br1_imp_iva)+sum(br1_imp_dap)+sum(br1_imp_sub)+sum(br1_imp_car)+sum(br1_imp_cre)+sum(br1_imp_dep)+sum(br1_dif_mas)-sum(br1_dif_menos) from regs1 where br1_tipo='0' and br1_area='"&areasub&"' and br1_tarifa='"&tarifasub&"' and br1_fecha_cob>='"&fechinic1sub&"' and br1_fecha_cob<='"&fechfin2sub&"';

Right now it takes like 4 minutes to do
the task, and i'm really concerned about the performance, unfortunately either i do it with sum or i make the addition manually record by record (already tried) and it will take even longer.

Is there anything to improve the performance of SQL Server 2k, SP3 ??

 

[fury]

It looks like that's structured about as well as it can be. I doubt doing 70 queries each with about 250,000 results can be completed in less time than that outside of a massive hardware upgrade. Is the SQL server optimized for your particular hardware? I'm not sure whether it applies to SQL server too, but MySQL can crawl even on top of the line hardware if it's not configured right. yay, 5000th post!

 

[Luis G]

I think hardware optimization doesn't apply to sql server, it just runs over windows.

I found a way to improve performance, but that will mean to create additional tables and to make the server calculate all the numbers on a daily basis (between 1am and 2am) for a period of one day (yesterday), that way when somebody asks for the results of a week the server would only sum 7 records instead of thousands.

Any other idea?

 

[fury]

That's the way the stats page in phpBB worked. It did all the calculations once hourly and for each access of the stats page it only called 1 row instead of making calculations through hundreds of rows for member stats, thousands of rows for thread stats and hundreds of thousands of rows for post stats.

I was really talking more about software configuration more than hardware configuration - you can have the server perform better with a weaker machine than on a beefier machine if the weaker machine has the SQL server optimized right for the amount of usage it's gonna get...

 

[Luis G]

that's what i meant

Been browsing enterprise manager, among other tools for something to keep the memory usage down and improve query times, didn't find a thing