slow writing

[stanja]

Hai ,

i'm using asp and sql server 2000 on a win2003 for processing the data.
When writing data the system is very slow , it takes a while till the data is processed.
I've already reindexed the db and checked it, and found that it's ok.
The system is a HP Proliant ML370

Regards
Stanja

 

[timmoser]

Can you show us the SQL and just the asp that calls the database? Bad SQL can kill performance. Also table size and type of updates you are performing will impact the performance.

Thanks

 

[stanja]

here's an example from a sql statement that has very poor performance.The example is a select not an insert/update

Remark : when it was installed on a win2000 server it worked fine

The asp talks with sql by the next statements

Dim objConn As New ADODB.Connection
Dim ObjRS As New ADODB.Recordset
Dim eSQL
Set objConn = Server.CreateObject("ADODB.Connection&quot
Set ObjRS = Server.CreateObject("ADODB.Recordset&quot
objConn.Open Session("WorkOfficeDSN&quot
eSQL = "select K4.K1KEY,K11.NAAMKLANT,isnull((select sum(K41.K4bedragfa) from K4 K41
where K4.K1KEY=K41.K1KEY and K41.K4docdatum <20020201 ),0),isnull((select
sum(K42.K4bedragfa)-sum(K42.K4BTW)-sum(k42.k4bedragdiv) from K4 K42 where
K4.K1KEY=K42.K1KEY and (K42.K4ORDERSYMB in ('FA','KN')) and K42.K4docdatum
>=20020201 and K42.K4docdatum <=20031231),0),isnull((select sum(K43.K4btw)
from K4 K43 where K4.K1KEY=K43.K1KEY and (K43.K4ORDERSYMB in ('FA','KN'))
and K43.K4docdatum >=20020201 and K43.K4docdatum
<=20031231),0),isnull((select sum(K44.K4bedragfa) from K4 K44 where
K4.K1KEY=K44.K1KEY and K44.K4ORDERSYMB<>'FA' and K44.K4ORDERSYMB<>'KN' and
K44.K4docdatum >=20020201 and K44.K4docdatum <=20031231),0),
isnull((select sum(K45.K4btw) from K4 K45 where K4.K1KEY=K45.K1KEY and
K45.K4ORDERSYMB<>'FA' and K45.K4ORDERSYMB<>'KN' and K45.K4docdatum
>=20020201
and K45.K4docdatum <=20031231),0),'Jaar',K11.BTWNUMMER,isnull((select
sum(K46.K4bedragdiv) from K4 K46 where K4.K1KEY=K46.K1KEY and
K46.K4ORDERSYMB in ('FA','KN') and K46.K4docdatum >=20020201 and
K46.K4docdatum <=20031231),0),
k11.vertegenw,k11.aankooporg,'' from K4 join K1 K11 on (K4.K1KEY=K11.K1KEY)
where (K4.K1key >=1 and K4.K1KEY <=9999999999 ) and k11.vertegenw >= '0' and
k11.vertegenw <= '999999' and isnull(k11.aankooporg,'') >= '' and
isnull(k11.aankooporg,'') <= '99999999'and isnull(K4valutakode,'')>= '0' and
isnull(K4valutakode,'') <= '1' group by
K4.K1KEY,K11.naamklant,K11.BTWNUMMER,k11.vertegenw,k11.aankooporg order by K4.K1KEY&quot;
Set ObjRS = objConn.Execute(eSQL)
processing of data ...
objrs.close

 

[timmoser]

The SQL looks ok to me and the fact that it ran fine on win 2000 helps this case. It sounds like a configuration issue with win2003 and sql2000; unfortunately that's something I know little about.

Probably someone else on this forum can help.

 

[royjimenez]

hi!

U should drop all the functions in the where 'cause u won't use the indexes. ISNULL ( it's a bad idea for using frequent queries, 'cause is a function and the NULL fields should be avoided ).

It could help:

i think you could separate your query in several queries, 'cause if u execute many aggregate functions in the same query u will get a worse performance.

I hope it helps...

Regards

The life is too short to cry and long enough to try it... God bless us.