Many reads - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Many reads

Author

Topic

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 06:20:35

Hi!
I was assigned to solve performance problems for an application. I fired up Sql Server profiler and started a trace. Downloaded Sql Server Trace Analyzer. It's a trial version so it's very limited. What I found is that one stored procedure generates almost 400 000 reads everytime it's used and it's used everytime the user wants to see his orders. I've tried to translate the t-sql to english from swedish, it looks something like this:


select top 100
	o.orderid, 
	o.name, 
	o.latestdeldate, 
	os.name as OrderStatus,
	os.orderstatusID, 
	p.placeID, 
	p.name as place, 
	p.address, 
	p.city, 
	a.name as worktype,
	noOfActions=(select count(*) from actions a where a.order_orderid=o.orderid),
	noOfServiceObjects = (select count(*) from Serviceobject s, Actions a where s.Place_PlaceID = o.Place_PlaceID and a.order_orderid = o.orderid and a.Serviceobject_serviceobjectid = s.serviceobjectid),
...
...
...

It has 8 select count(*) in the select statement then in the where statement it has 2 more select count(*).

I know it's very difficult for you to come up with a solution but do you know a better way than to use select count(*) everywhere? The count is used for to show different status flags on the website.

/Magnus

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 06:54:48

Post full query before we can help.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 06:55:38

If the result from the subquery already is JOINed in outer query, you can take the count from there.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 07:14:46

Also, there is no need to translate the column names.
Most people here knows the difference of column names and table names.

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 07:45:14

Ok here is the whole stored proc, in Swedish. It's a really messy sproc and they use varbinary at the end.

@UID int,
@OrderByColumn varchar(50),
@sortdir varchar(50), 
@PlaceFilter varchar(50),
@OrderStatusFilter varchar(50),
@VisaFel int,
@VisaFelAvvikelse int
) AS
select top 100
	o.orderid, 
	o.namn, 
	o.senasteleveransdatum, 
	os.namn as OrderStatus,
	os.orderstatusID, 
	p.platsID, 
	p.namn as plats, 
	p.besoksadress, 
	p.postort, 
	a.namn as arbetstyp,
	numAtgarder=(select count(*) from atgard a where a.order_orderid=o.orderid),
	numSo = (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid),
 	antalSoRod = (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid and s.Felklass_FelklassID > 34),
 	antalSoGul = (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid  and s.Felklass_FelklassID > 24 and s.Felklass_FelklassID < 35),
 	antalSoGron = (select count(*)from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid  and s.Felklass_FelklassID > 14 and s.Felklass_FelklassID < 25),
 	antalAtgRod =(select count(*) from atgard a where a.order_orderid=o.orderid and a.SOFelklass_FelklassID > 34),
	antalAtgGul =(select count(*) from atgard a where a.order_orderid=o.orderid and a.SOFelklass_FelklassID > 24 and a.SOFelklass_FelklassID < 35),
  	antalAtgGron =(select count(*) from atgard a where a.order_orderid=o.orderid and a.SOFelklass_FelklassID > 14 and a.SOFelklass_FelklassID < 25)

from [order] o, dbo.fn_Split(@OrderStatusFilter, ','), orderstatus os, plats p, arbetstyp a, UWebPermission u
where o.orderstatus_orderstatusid = value
  and o.orderstatus_orderstatusid = os.orderstatusID
  and o.plats_platsid = p.platsid
  and o.orderkorg_orderkorgid = u.orderkorg_orderkorgid
  and u.uwebusers_uwebusersid = @uid
  and o.namn like @PlaceFilter
  and (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid and s.Felklass_FelklassID > 34) > @visaFel
  and ((select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid and s.Felklass_FelklassID > 34) + (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid  and s.Felklass_FelklassID > 24 and s.Felklass_FelklassID < 35)) > @visaFelAvvikelse
  and o.arbetstyp_arbetstypid = a.arbetstypid

order by
case when @sortdir='asc' then Null
when @OrderByColumn = 'os.orderstatusid' then cast(os.orderstatusid AS varbinary(50)) 
when @OrderByColumn = 'os.namn' then cast(os.namn AS varbinary(50)) 
when @OrderByColumn = 'o.namn' then cast(o.namn AS varbinary(50)) 
when @OrderByColumn = 'p.besoksadress' then cast(p.besoksadress AS varbinary(50)) 
when @OrderByColumn = 'p.postort' then cast(p.postort AS varbinary(50)) 
when @OrderByColumn = 'o.senasteleveransdatum' then cast(o.senasteleveransdatum AS varbinary(50)) 
when @OrderByColumn = 'o.orderid' then cast(o.orderid AS varbinary(50)) else NULL end desc, 
case when @sortdir<>'asc' then Null
when @OrderByColumn = 'os.orderstatusid' then cast(os.orderstatusid AS varbinary(50)) 
when @OrderByColumn = 'os.namn' then cast(os.namn AS varbinary(50)) 
when @OrderByColumn = 'o.namn' then cast(o.namn AS varbinary(50)) 
when @OrderByColumn = 'p.besoksadress' then cast(p.besoksadress AS varbinary(50)) 
when @OrderByColumn = 'p.postort' then cast(p.postort AS varbinary(50)) 
when @OrderByColumn = 'o.senasteleveransdatum' then cast(o.senasteleveransdatum AS varbinary(50))
when @OrderByColumn = 'o.orderid' then cast(o.orderid AS varbinary(50)) else NULL end asc

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 08:33:30

Try this

ALTER PROCEDURE dbo.uspDemo
(
	@UID INT,
	@OrderByColumn VARCHAR(50),
	@sortdir VARCHAR(50), 
	@PlaceFilter VARCHAR(50),
	@OrderStatusFilter VARCHAR(50),
	@VisaFel INT,
	@VisaFelAvvikelse INT
)
AS

SET NOCOUNT ON

SELECT TOP 100	o.orderid, 
		o.namn, 
		o.senasteleveransdatum, 
		os.namn as OrderStatus,
		os.orderstatusID, 
		p.platsID, 
		p.namn as plats, 
		p.besoksadress, 
		p.postort, 
		at.namn as arbetstyp,
		numAtgarder = COUNT(*) OVER (PARTITION BY o.orderid),
		numSo = SUM(CASE WHEN so.Felklass_FelklassID IS NULL THEN 0 ELSE 1 END),
		antalSoRod = SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END),
		antalSoGul = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END),
		antalSoGron = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0),
		antalAtgRod = SUM(CASE WHEN y.SOFelklass_FelklassID > 34 THEN 1 ELSE 0 END),
		antalAtgGul = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END),
		antalAtgGron = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0 END)
FROM		[order] AS o
INNER JOIN	dbo.fn_Split(@OrderStatusFilter, ',') AS x ON x.value = o.orderstatus_orderstatusid
INNER JOIN	orderstatus AS os ON os.orderstatusID = o.orderstatus_orderstatusid
INNER JOIN	plats AS p ON p.platsid = o.plats_platsid
INNER JOIN	arbetstyp AS at ON at.arbetstypid = o.arbetstyp_arbetstypid
INNER JOIN	UWebPermission AS u ON u.orderkorg_orderkorgid = o.orderkorg_orderkorgid
LEFT JOIN	atgard AS y ON y.order_orderid = o.orderid
LEFT JOIN	Serviceobjekt AS so ON so.serviceobjektid = y.Serviceobjekt_serviceobjektid
			AND so.Plats_PlatsID = o.Plats_PlatsID
WHERE		o.namn like @PlaceFilter
		AND u.uwebusers_uwebusersid = @uid
		AND SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) > @VisaFel
		AND SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) + SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END) > @visaFelAvvikelse
ORDER BY	CASE
			WHEN @sortdir = 'asc' THEN NULL
			WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
			ELSE NULL
		END DESC, 
		CASE
			WHEN @sortdir <> 'asc' THEN NULL
			WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50)) 
			WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50))
			WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
			ELSE NULL
		END

And please report back if there is a speed difference.

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 08:44:02

Wow! Thanks, tried it out, got an error message:

Msg 102, Level 15, State 1, Procedure GetOrders, Line 48
Incorrect syntax near ')'.

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 08:49:14

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 08:56:39

I'm really thankful for you helping me and I know it's hard to code without the db. Got this now:
Msg 147, Level 15, State 1, Procedure GetOrders, Line 14
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 08:58:39

[code]ALTER PROCEDURE dbo.uspDemo
(
@UID INT,
@OrderByColumn VARCHAR(50),
@sortdir VARCHAR(50),
@PlaceFilter VARCHAR(50),
@OrderStatusFilter VARCHAR(50),
@VisaFel INT,
@VisaFelAvvikelse INT
)
AS

SET NOCOUNT ON

SELECT TOP 100 o.orderid,
o.namn,
o.senasteleveransdatum,
os.namn as OrderStatus,
os.orderstatusID,
p.platsID,
p.namn as plats,
p.besoksadress,
p.postort,
at.namn as arbetstyp,
numAtgarder = COUNT(*) OVER (PARTITION BY o.orderid),
numSo = SUM(CASE WHEN so.Felklass_FelklassID IS NULL THEN 0 ELSE 1 END),
antalSoRod = SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END),
antalSoGul = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END),
antalSoGron = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0),
antalAtgRod = SUM(CASE WHEN y.SOFelklass_FelklassID > 34 THEN 1 ELSE 0 END),
antalAtgGul = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END),
antalAtgGron = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0 END)
FROM [order] AS o
INNER JOIN dbo.fn_Split(@OrderStatusFilter, ',') AS x ON x.value = o.orderstatus_orderstatusid
INNER JOIN orderstatus AS os ON os.orderstatusID = o.orderstatus_orderstatusid
INNER JOIN plats AS p ON p.platsid = o.plats_platsid
INNER JOIN arbetstyp AS at ON at.arbetstypid = o.arbetstyp_arbetstypid
INNER JOIN UWebPermission AS u ON u.orderkorg_orderkorgid = o.orderkorg_orderkorgid
LEFT JOIN atgard AS y ON y.order_orderid = o.orderid
LEFT JOIN Serviceobjekt AS so ON so.serviceobjektid = y.Serviceobjekt_serviceobjektid
AND so.Plats_PlatsID = o.Plats_PlatsID
WHERE o.namn like @PlaceFilter
AND u.uwebusers_uwebusersid = @uid
HAVING SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) > @VisaFel
AND SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) + SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END) > @visaFelAvvikelse
ORDER BY CASE
WHEN @sortdir = 'asc' THEN NULL
WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50))
WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50))
WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50))
WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50))
WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
ELSE NULL
END DESC,
CASE
WHEN @sortdir <> 'asc' THEN NULL
WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50))
WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50))
WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50))
WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50))
WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
ELSE NULL
END[/code]
To bad I can't test run them[code]ALTER PROCEDURE dbo.uspDemo
(
@UID INT,
@OrderByColumn VARCHAR(50),
@sortdir VARCHAR(50),
@PlaceFilter VARCHAR(50),
@OrderStatusFilter VARCHAR(50),
@VisaFel INT,
@VisaFelAvvikelse INT
)
AS

SET NOCOUNT ON

SELECT TOP 100 o.orderid,
o.namn,
o.senasteleveransdatum,
os.namn as OrderStatus,
os.orderstatusID,
p.platsID,
p.namn as plats,
p.besoksadress,
p.postort,
at.namn as arbetstyp,
numAtgarder = COUNT(*) OVER (PARTITION BY o.orderid),
numSo = SUM(CASE WHEN so.Felklass_FelklassID IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY o.orderid),
antalSoRod = SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid),
antalSoGul = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid),
antalSoGron = SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid),
antalAtgRod = SUM(CASE WHEN y.SOFelklass_FelklassID > 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid),
antalAtgGul = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid),
antalAtgGron = SUM(CASE WHEN y.SOFelklass_FelklassID BETWEEN 15 AND 24 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid)
FROM [order] AS o
INNER JOIN dbo.fn_Split(@OrderStatusFilter, ',') AS x ON x.value = o.orderstatus_orderstatusid
INNER JOIN orderstatus AS os ON os.orderstatusID = o.orderstatus_orderstatusid
INNER JOIN plats AS p ON p.platsid = o.plats_platsid
INNER JOIN arbetstyp AS at ON at.arbetstypid = o.arbetstyp_arbetstypid
INNER JOIN UWebPermission AS u ON u.orderkorg_orderkorgid = o.orderkorg_orderkorgid
LEFT JOIN atgard AS y ON y.order_orderid = o.orderid
LEFT JOIN Serviceobjekt AS so ON so.serviceobjektid = y.Serviceobjekt_serviceobjektid
AND so.Plats_PlatsID = o.Plats_PlatsID
WHERE o.namn like @PlaceFilter
AND u.uwebusers_uwebusersid = @uid
AND SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid) > @VisaFel
AND SUM(CASE WHEN so.Felklass_FelklassID > 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid) + SUM(CASE WHEN so.Felklass_FelklassID BETWEEN 25 AND 34 THEN 1 ELSE 0 END) OVER (PARTITION BY o.orderid) > @visaFelAvvikelse
ORDER BY CASE
WHEN @sortdir = 'asc' THEN NULL
WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50))
WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50))
WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50))
WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50))
WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
ELSE NULL
END DESC,
CASE
WHEN @sortdir <> 'asc' THEN NULL
WHEN @OrderByColumn = 'os.orderstatusid' THEN CAST(os.orderstatusid AS VARBINARY(50))
WHEN @OrderByColumn = 'os.namn' THEN CAST(os.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'o.namn' THEN CAST(o.namn AS VARBINARY(50))
WHEN @OrderByColumn = 'p.besoksadress' THEN CAST(p.besoksadress AS VARBINARY(50))
WHEN @OrderByColumn = 'p.postort' THEN CAST(p.postort AS VARBINARY(50))
WHEN @OrderByColumn = 'o.senasteleveransdatum' THEN CAST(o.senasteleveransdatum AS VARBINARY(50))
WHEN @OrderByColumn = 'o.orderid' THEN CAST(o.orderid AS VARBINARY(50))
ELSE NULL
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 09:19:48

THANKS IT WORKS! It now generates about 19 000 reads instead of 400 000 :-) I had to add a GROUP BY to the last sql. Thanks again.

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 09:22:22

So the query now runs about 20 times faster?

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 09:31:07

Yes thank you. I still think they approach of this query is bad or what do you think?

I'm now into next problem but it's not a t-sql problem but maybe you have a clue? I've noticed that when they create orders for their servicepeople I can see that INSERT INTO statements are run but..they don't generate writes. After 10-15 minutes a cunk of writes are done by .net sqlclient data provider. I'm the only one running against this db. Seems like problem with transaction/connection handling in the .net code?

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 09:55:31

Could be.
Hard to tell without knowing more about business rules, code and other vital parts of information.

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 10:05:42

I found one thing not correct in they query, the original line of code:

numSo = (select count(*) from Serviceobjekt s, Atgard a where s.Plats_PlatsID = o.Plats_PlatsID and a.order_orderid = o.orderid and a.Serviceobjekt_serviceobjektid = s.serviceobjektid),

Was changed to this, which is completely different.


numSo = SUM(CASE WHEN so.Felklass_FelklassID IS NULL THEN 0 ELSE 1 END),

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-25 : 10:21:28

Are antalSoRod, antalSoGul and antalSoGron correct?
NumSo should be the sum of these three?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-25 : 12:03:12

One thing I would suggest is that put the results of your SPLIT function into a temp table or table variable with a primary key. SQL doesn't seem to do well when joining to table-valued functions. This is probably trivial in this case, but it's a good habit to get into.

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-25 : 17:17:09

Peso - Yes you're right, NumSo is the sum of those three. I found out that the count of NumSo is used for setting the height of a div layer on the webpage, incredible :-)

Lamprey - I understand what you mean with moving the split to a temp table. It would probably give better perfomance.

Jesus saves. But Gretzky slaps in the rebound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-26 : 02:18:12

numSo = SUM(CASE WHEN so.Felklass_FelklassID >= 15 THEN 1 ELSE 0 END)

E 12°55'05.25"
N 56°04'39.16"

Rauken
Posting Yak Master

108 Posts

Posted - 2008-03-26 : 10:38:50

New info. After research I've found out that antalSoGul, antalSoRod and antalSoGul is very rarely shown on the webpage which means I can take them out from the query. I've also removed the orderby statements and I let a new grid from devxpress handle all the sorting, grouping etc. And I've written two new stored procs from scratch. No more perfomance problems :-) Thanks for your help.

Jesus saves. But Gretzky slaps in the rebound.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources