Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bashka_abdyli
Starting Member
15 Posts |
 Posted - 2003-07-22 : 02:22:10
|
| SELECT d1.kontoja,d1.valuta,d1.partia, c.emri + ' ' + c.mbiemri as description,d1.debi,d1.kredi as kredi,d1.debi-d1.kredi as saldo from (SELECT t.kontoja, t.valuta, t.partia, SUM(t.debi) as debi,SUM(t.kredi) as kredi, ABS(SUM(t.debi-t.kredi)) as saldo FROM transaksionet t GROUP BY t.kontoja, t.valuta, t.partia) as d1,[plan] p, clients c WHERE d1.kontoja = p.account2 and d1.partia = c.id order by d1.kontoja,d1.valuta,d1.partiathis statement costs me 6 secondsand at last I need to know sums of debi and kredi to put in a status barSELECT SUM(t.debi) as debi,SUM(t.kredi) as kredi, SUM(t.debi-t.kredi) as saldo FROM transaksionet t, clients c, [plan] p WHERE t.kontoja = p.account2 and t.partia = c.id this takes me 20 seconds on table contains 2,000,000 rowsany help to speed the second sum statement |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-22 : 03:23:58
|
| 20 seconds is a lot of time for a straight forward query like this. Can you try this? (make sure you get the same results!)SELECT debi, kredi, (debi-kredi) as saldo FROM(SELECT SUM(t.debi) as debi, SUM(t.kredi) as kredi FROM transaksionet tINNER JOIN clients c ON t.partia = c.idINNER JOIN [plan] p ON t.kontoja = p.account2) SubQuery1You see here it has to perform the debi-kredi only once for the entire query as opposed to maybe 2 million times for the query. Also I have re-written it using the ANSI SQL92 syntax, you should really try to use this syntax, its less ambiguous and the previous syntax might not be supported by future versions of SQL Server. Also, do you have indexes on the join fields: partia, kontoja, etc.? Adding indexes to those could dramatically speed up the query.Owais |
 |
|
|
bashka_abdyli
Starting Member
15 Posts |
Posted - 2003-07-22 : 04:27:53
|
| same thing 20 sec or more, another thing the first statement is executed is it possible to get results from that statement of sum(debi) and sum(kredi) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-22 : 09:30:41
|
quote:
same thing 20 sec or more, another thing the first statement is executed is it possible to get results from that statement of sum(debi) and sum(kredi)
Well, yes you could by adding a subquery, but that is only going to slow the query down unacceptably. I am wondering if the joins are really required in the second query, because I dont see any WHERE clause filters...does this return the same results as the earlier one?SELECT SUM(t.debi) as debi,SUM(t.kredi) as kredi, SUM(t.debi-t.kredi) as saldo FROM transaksionet t Owais |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 11:04:38
|
| Didn't we do this already?Brett8-) |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2003-07-23 : 08:12:07
|
quote:
Also I have re-written it using the ANSI SQL92 syntax, you should really try to use this syntax, its less ambiguous and the previous syntax might not be supported by future versions of SQL Server.
The query written by bashka_abdyli is standard compliant. It is the use of *= and =* (for outer joins) that is marked as obsolete and may not be supported fortwith.ANSI SQL 92 is obsolete. The current standard is ANSI SQL 99, which will be surpassed by ANSI SQL 2003 which is planned to be approved at the end of this year.Edited by - LarsG on 07/23/2003 08:13:33 |
|
|
|
|
|
|
|
|
Help with sum