Jag blir lite irriterad, har skapat en sql-sats som returnerar värden på under 1 sek om man tittar i SQL Server Management Studio när man testar satsen själv, samt som en subquery i andra sql-satser.

Nu vill jag implementera denna sql-sats i en user-defined scalar function, att skriva själva funktionen är inte problemet och få resultatet. 1 - 2 tester så var den "hyfsat snabb" under 3 sek när man använde sig av den i andra sql-satser sen.

Men sen efter de 1-2 testen, då blev den enormt seg. När jag hade den i sql-satserna hade den inte exekverat färdigt efter ens 1 minut, använde jag print, för bara funktionen då gick den lös på ca 20 sek.

Vad tror ni kan vara boven i dramat?

Vad är det egentligen för SQL frågor du har som tar så länge som 1 sekund och uppåt att exekvera? Vanligtvis brukar ju tiderna ligga kring någon millisekund. Är du säker på att du har index på rätt fält i databasen och att din SQL fråga är optimal?

Så här ser i alla fall själva functionen ut från modify-läge:
<code>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author: Fredrik Malmström
-- Create date: 20070613
-- Description: Beräknar antalet annonser under en viss period som underlag för fakturering
-- =============================================
ALTER FUNCTION [dbo].[ufn_CountAds]
(
-- Add the parameters for the function here
@CustomerID int, @StartDate datetime, @Stopdate datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Result int

-- SQL-sats för att returnera antalet annonser för import kunder som
-- skall vara underlag för fakturering

select @Result = (select sum(c.annonser) from (
select
(
select count(distinct custompropertyid) from properties where
imported between @StartDate and @StopDate and
customer=a.Customer
) As annonser
from properties a
inner join
customers b on
a.Customer=b.customerid
where a.customer=@CustomerID
-- end
UNION
-- SQL-sats beräkna antal annonser för ej importerad kund som skall
-- vara underlag för fakturering.
select
(
(
select count(*) from properties where customer=a.Customer
and deleted is null and imported is null
)
+
( select count(*) from properties where
deleted between @StartDate and @StopDate
and imported is null and
customer=a.Customer
)
) As annonser
from properties a
inner join
customers b on
a.Customer=b.customerid
where a.Customer=@CustomerID
) as c)

-- Returnera antalet annonser under den angivna perioden.
RETURN @Result

END</code>

Bara sql-satsen<code>
select sum(c.annonser) from (
select
(
select count(distinct custompropertyid) from properties where
imported between @StartDate and @StopDate and
customer=a.Customer
) As annonser
from properties a
inner join
customers b on
a.Customer=b.customerid
where a.customer=@CustomerID
-- end
UNION
-- SQL-sats beräkna antal annonser för ej importerad kund som skall
-- vara underlag för fakturering.
select
(
(
select count(*) from properties where customer=a.Customer
and deleted is null and imported is null
)
+
( select count(*) from properties where
deleted between @StartDate and @StopDate
and imported is null and
customer=a.Customer
)
) As annonser
from properties a
inner join
customers b on
a.Customer=b.customerid
where a.Customer=@CustomerID
) as c
</code>

Jag kan inte svara på vad som inte är riktigt optimalt, men den drar iväg mycket snabbare inom select-satser än som funktion

Oj vilka SQL satser du har. För det första brukar det i många fall ta mycket kraft ur servern att köra felaktiga SELECT satser inne i andra SELECT satser.
Om du beskriver hur de väsentligaste tabellerna ser ut och hur du vill ha ut data, gärna med små exempel, kan du nog få hjälp att optimera det hela till en enklare SQL sats som också är snabbare...

Jag är nu ingen expert på SQL server men SQL kan jag nog rätt bra...

Tar bar upp de väsentliga kolumnerna som berörs.

Tabellen:Properties
propertyid | | customer | custompropertyid | created | deleted | updated | imported

Tabellen:Customers
customerid | Name | deleted


Properties innehåller annonser som är direkt skapade av någon kund, men även innehåller den importerade annonser från någon kund.

Det jag vill ha fram är antalet annonser som funnits under en viss period för båda två, dock importeras annonser varje dygn från de kunder som är importkunder, om en importerad annons med ex. custompropertyid 100 och tillhör en kund med customer=45 så skapas en ny post med tillhörande information, samt sätts created till begynnelsepostens created med samma custompropertyid och customer, deleted sätts till null, den tidigare begynnelsepostens deleted sätts til den nya postens import datum.

Därav har varje importerad annons flera upplagor av sig själv, så länge som den finns kvar hos exportören så växer antalet lika annonser.

Där av den första sql-satsen i union satsen skall räkna antalet distincta annonser (deleted som ej deleted) under en viss period, den andra räknar annonserna för de ej importerade under samma period med samma krav (deleted som inte deleted).

För perioden kan ha kanske 10 aktiva annonser, men det har funnits x antal till annonser som deleteas och ett visst antal nya har kommit.

Där av den lilla sql-satsen och det stora problemet

Som jag förstår det av sin SQL sats kan du förkorta ner den till åtminstone det här:
<code>
SELECT COUNT(0) FROM
(
(
SELECT DISTINCT custompropertyid FROM properties
WHERE customer = @CustomerID AND imported BETWEEN @StartDate AND @StopDate
)
UNION ALL
(
SELECT custompropertyid FROM properties
WHERE customer = @CustomerID AND imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)
)
) AS c
</code>
Testa och se om det ger samma resultat och är snabbare...

En till lösning, som kanske är ännu snabbare, kan vara
<code>
SELECT SUM(CASE WHEN a.imported IS NULL THEN a.antal ELSE 1 END) FROM
(
SELECT COUNT(0) AS antal, imported FROM properties
WHERE customer = @CustomerID AND (imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)) OR (imported BETWEEN @StartDate AND @StopDate)
GROUP BY custompropertyid
) AS a
</code>

får testa och se om de blir snabbare, och om det returnerar rätt :)

Kom just på att det saknas nog lite i den senaste SQL satsen jag skrev.
En lite modifierad variant kan vara:
<code>
SELECT SUM(CASE WHEN a.imported IS NULL THEN a.antal ELSE 1 END) FROM
(
SELECT COUNT(0) AS antal, imported FROM properties
WHERE customer = @CustomerID AND (imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)) OR (imported BETWEEN @StartDate AND @StopDate)
GROUP BY custompropertyid, imported IS NULL
) AS a
</code>

Jag kan inte göra en sql-sats med en group by där den har värdet imported is null, jag får ett felmeddelande på IS.

Ok, men ta bort IS NULL då bara, det borde bli samma resultat ändå. Ville bara försöka spara in på raderna som returneras ;)

Kanske du kan använda funktionen ISNULL(imported) istället

mja.. det blir ca 1000 rader för många på senaste varianten :P

mja.. det blir ca 1000 rader för många på senaste varianten :P

nope isnull(imported) funkade inget vidare.

Men jag prövade den där andra (den första), den gjorde funktionen mycket snabbare de gånger jag nu har kört den... den nästan bara smällde till :)

skall utvärdera den ett tag och se om vi måste optimera ännu mer då. :)

Akta så att datorn inte exploderar när det smäller ;)
Angående andra varianten ska jag, när jag får tid, ta och kontrollera den igen =)

Har nu funderat lite och kommit fram till detta. Är inte säker på vad GROUP BY tycker om denna, men det borde fungera. Dessutom fanns det en parentes för lite så därför returnerade den tidigare versionen fel resultat.
<code>
SELECT SUM(CASE WHEN a.isnotimported THEN a.antal ELSE 1 END) FROM
(
SELECT COUNT(0) AS antal, (imported IS NULL) AS isnotimported FROM properties
WHERE customer = @CustomerID AND ((imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)) OR (imported BETWEEN @StartDate AND @StopDate))
GROUP BY custompropertyid, isnotimported
) AS a
</code>

Vad blev den bästa lösningen nu då?

<code>select @Result = (select count(0) from
(
(
select distinct custompropertyid from properties
where customer=@CustomerID and imported between @StartDate and @StopDate
)
union all
(
select custompropertyid from properties
where customer=@CustomerID and imported is null and (deleted is null or deleted between @StartDate and @StopDate)
)
) as c)</code>
Denna blev det...

Snabbt och behändigt gick det!

Ok, bra =)

Testade du den andra varianten mer? Fungerade mina ändringar eller ger den ännu fel svar?
Bara lite nyfiken ;)

<code>SELECT SUM(CASE WHEN a.isnotimported THEN a.antal ELSE 1 END) FROM
(
SELECT COUNT(0) AS antal, (imported IS NULL) AS isnotimported FROM properties
WHERE customer = @CustomerID AND ((imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)) OR (imported BETWEEN @StartDate AND @StopDate))
GROUP BY custompropertyid, isnotimported
) AS a</code>


Denna ger felet:

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IS'.

Ok, så den klagade på sånt =)
Nå, här är en modifierad variant som, enligt mitt tycke, borde fungera ;)
<code>
SELECT SUM(CASE WHEN a.imported IS NULL THEN a.antal ELSE 1 END) FROM
(
SELECT COUNT(0) AS antal, imported FROM properties
WHERE customer = @CustomerID AND ((imported IS NULL AND (deleted IS NULL OR deleted BETWEEN @StartDate AND @StopDate)) OR (imported BETWEEN @StartDate AND @StopDate))
GROUP BY custompropertyid, imported
) AS a
</code>

den funkade i alla fall! :)

Kul att veta! =)
Sen är frågan, är den snabbare, söligare eller lika snabb som den med union ;)

ja det återstår att se vid ett senare skede :)