Hejsan
Jag läste tråden nedanför angående sökning på tid och blev lite nyfiken på hastighetsskillnad.
Är det stor skillnad i hastighet på de alternativen nedan, och vad skulle ni rekomendera.
Jag jobbar med stora datamängder.
Alt 1
where Convert(varchar(10),datumkolumn,121) between '2005-01-01' and '2005-02-01'
Alt 2
where Convert(varchar(10),datumkolumn,121) >= '2005-01-01' and Convert(varchar(10),datumkolumn,121) < '2005-02-01'
Alt 3
where Convert(varchar(7),datumkolumn,121) = '2005-01'
Alt 4
where datepart(yy,datumkolumn)=2005 and datepart(mm,datumkolumn)=1

Bara lite nyfiken.

Alt 1 och alt 2 är exakt lika bra, SQL gör om Alt 1 till Alt 2 vid optimeringen.

Alt 3 är bara 1 jämförelse och "borde" vara lite effektivare.

Alt 4 är (troligen) ganska likvärdig med 1 och 2, det beror på vilken funktion som är snabbast: CONVERT eller DATEPART.

/micke

<b>Jag läste tråden nedanför angående sökning på tid </b>

Läaste du även Göran Anderssons svar om hur datetime lagras? Jag tror att du får bäst prestanda om du struntar i att konvertera datumet till strängar och istället använder DateDiff metoden i SQL server.


<code>
declare @mydate datetime
set @mydate = '2004-10-02'

select * from mintabell
where datediff(m, @mydate, orderdate) = 0

declare @mydate2 datetime
set @mydate2 = '2004-10-10'

select * from mintabell
where datediff(day, @mydate, orderdate) >= 0 and datediff(d, @mydate2, orderdate) <= 0
</code>

I fösta fallet så kommer alla som har ett orderdate i oktober 2004 at visas, i andra kommer enbart de som har orderdatum mellan 2 oktober 2004 och 10 oktober 2004 att visas. Du kan även jämföra på tider, tex visa alla mellan 2004-10-02 10:22 och 2005-03-04 06:45.

Jag tror att det prestandmässigt bästa är att använda datediff så att man slipper en massa castningar till strängar.

Bäst, och det enda som är korrekt, är något i stil med följande:

<code>
WHERE datumkolumn BETWEEN @startdate och @enddate
</code>

Använd lämpliga värden i @startdate och @enddate, t ex om man ska ha en viss dag sätter man @startdate till 'YYYY-MM-DD 00:00:00' och @enddate till 'YYYY-MM-DD 23:59:59' (där YYYY är det år det gäller, MM månaden och DD dagen).

Varför? Dels så är det som Marcus skriver viktigt att inte hårdkoda datum i en fråga som en sträng, beroende på localization så kan det bli fel resultat då. Med datumen som variabler som sätts enligt den localization som gäller slipper man det. Men den viktigaste anledningen är prestanda. På detta vis ger man SQL Server möjlighet att använda eventuella index på datumkolumnen. Jämför t ex exekveringsplanerna för följande två procedurer i QA:

<code>
use Northwind
go

create proc dbo.TestDate
@startdate datetime
, @enddate datetime
with recompile
as
begin
SELECT OrderID, OrderDate FROM dbo.Orders
WHERE OrderDate BETWEEN @startdate AND @enddate
end

create proc dbo.TestDate2
@startdate datetime
, @enddate datetime
with recompile
as
begin
SELECT OrderID, OrderDate FROM dbo.Orders
WHERE DATEDIFF(d, @startdate, OrderDate) >= 0 AND DATEDIFF(d, @enddate, OrderDate) <= 0
end

exec dbo.TestDate '1996-07-10 00:00:00', '1996-07-10 23:59:59'
exec dbo.TestDate2 '1996-07-10 00:00:00', '1996-07-10 23:59:59'
</code>

Den första exekveringen använder en index seek, den andra en index scan, för att returnera samma resultat. Anledningen är att i den andra frågan så är inte WHERE-villkoret ett korrekt formatterat SARG (kolumn operator expression), och därmed kan inte index användas.

Hej

Jag är med på att din between kan vara snabbare och effektivare, men för att den skall var fullständig så har du utleämnat vissa saker. Tex så har du hårdkodat @startdate och @enddate till 00:00:00 respektive 23:59:59 för att kunna jämföra hela dagar, det kan man ju iofs lösa i applikationskoden med risken att göra den mer komplex. Det slipper man göra med datediff utan kan där direkt använda DateDiff(d,datetime,datetime) på det datum som man har valt. Så just det exemplet är ju lite "tillrättalagt" för att stödja between, försök att skicka in tex "TestDate '1996-07-10 08:00:00', '1996-07-10 18:00:00'" och se vilken som fungerar för att visa ordrar för de dagar som ligger i intervallet för @startdate & @enddate... ;-)


Ta t ex exemplet med att jämföra månad, jag vill få ut alla ordrar från en månad(tex januari i år) som jag skickar som en inparameter till en SP. den inparametern är en datetime med godtyckligt datum och tid i månaden (tex 2005-01-23 12:36:42) .


<code>

create proc dbo.GetOrderFroMonth
@Mydate datetime // innehåller den månad vi vill få information infrån, kan var vilken dag som helst i månaden
with recompile
as
begin

declare @startdate datetime
set @startdate = @Mydate - antalet dagar som har har gått sedan den 1'e i @startdate - antalate timmar, minuter & sekunder som har gått sedan midnatt i @startdate

declare @enddate datetime
set @endate = sista datumet i den månad som @startdate innehåller och hur sjutton skall vi få fram dem?//
Vi skulle kunna labba en hel del med dateadd osv men det blir ju tokjobbigt. //
Logiken blir tvärtom @Mydate + antalet dagar som är kvar i måndan i @startdate
+ antalet timmar, minuter & sekunder till midnatt i @startdate

SELECT OrderID, OrderDate FROM dbo.Orders
WHERE OrderDate BETWEEN @startdate AND @enddate
end


</code>

Eller

<code>

create proc dbo.GetOrderFroMonth
@mydate datetime // innehåller den månad
with recompile
as
begin

SELECT OrderID, OrderDate FROM dbo.Orders
where datediff(m, @mydate, OrderDate) = 0

end

</code>

Att få fram sista datumet i en månad vet jag faktiskt inte hur jag skulle göra i en SP, där finns ju en del att ta hänsyn till tex skottår med 29 dagar i februari (30 dagar en gång i sveriges tidshistoria). Att lösa det i applikationskod kan jag göra direkt, det andra skulle nog ta någon timme att klura ut. Men en poäng med det är ju att man vill helst inte att applikationskoden skall bli för anpassad efter databas (eller något annat heller för den delen) utan det jag har är ett datum och utifrån det datumet vill jag sedan få alla ordrar och finns det då ett lättförståeligt .


Skillnaden i appen skulle bli att applikationskoden skulle få räkna ut det plus att SP'n får ta emot två parametrar @Startdate och @Enddate (vilka egentligen inte är datum utan datum & klockslag det datumet).

Så här skulle koden se ut för att räkna ut start och slutdatum (& tid eftersom det är datetime) på en månad kunna se ut. (C#)
<code>

TimeSpan ts = new TimeSpan(MyDateToCompare.Day-1, MyDateToCompare.Hour, MyDateToCompare.Minute, MyDateToCompare.Second);

DateTime Startdate = MyDateToCompare.Add(-ts);

ts = new TimeSpan(MyDateToCompare.AddMonths(1).Day, 23-MyDateToCompare.AddMonths(1).Hour, 59-MyDateToCompare.AddMonths(1).Minute, 59-MyDateToCompare.AddMonths(1).Second);

DateTime EndDate = MyDateToCompare.Add(ts);

// nu är EndDate 2005-03:01 00:00:00 & EndDate 2005-03-31 00:00:00
</code>


Vilket sätt man använder sig av för att komma fram till rätt datum är en smaksak, men det kan bli otroligt komplicerat och oöverskådeligt om man inte tänker till innan hur man vill ha sin arkitektur på db OCH applikation.

Du missar min poäng. För att lösa WHERE DATEDIFF(whatever, datumkolumn, @datumvariabel) > 0 (eller liknande) kan SQL Server _aldrig_ använda index. Då är frågan om CONVERT, DATEDIFF eller vad som helst är snabbast att använda ganska ointressant.

Att få fram sista datumet i en månad är inte speciellt svårt. DATEADD(s, -1, @datevariable), där @datevariable är första dagen kl 00:00:00 i nästa månad. För att beräkna månad så beräknar man antingen datum i förväg (och skickar in två parametrar med första och sista datum i månaden) eller så gör man en procedur som tar ett datum och själv beräknar första och sista i aktuell månad.

Bara en parentes )

Originalfrågan ville söka efter data såhär:

2005-03-01 8:00:00 till 13:00:00
2005-03-02 8:00:00 till 13:00:00
2005-03-03 8:00:00 till 13:00:00
2005-03-04 8:00:00 till 13:00:00

Alltså datum mellan 2 dagar, men också tider, alltså ett antal icke-sammanhängande tidsintervall.
Det är ENBART därför något annat än Christoffers första förslag kommit upp i diskussionen. Naturligtvis är det viktigaste (prestandamässigt) att ett index kan nyttjas. Så den korrekta (och mycket krångliga) frågan i VARJE fall är:

(Förutsätter att man bara vill ha 4 intervall
och att man fyllt på 8 variabler med sina intervall enligt följande lista)
@DateStart = 2005-03-01 8:00:00
@DateEnd = 2005-03-01 13:00:00
@DateStart2 = 2005-03-01 8:00:00
@DateEnd2 = 2005-03-02 13:00:00
@DateStart3 = 2005-03-01 8:00:00
@DateEnd3 = 2005-03-03 13:00:00
@DateStart4 = 2005-03-01 8:00:00
@DateEnd4 = 2005-03-04 13:00:00

Då ser den "bra" wheresatsen ut såhär:

WHERE DatumKolumn BETWEEN @DateStart and @DateEnd
OR
DatumKolumn BETWEEN @DateStart2 and @DateEnd2
OR
DatumKolumn BETWEEN @DateStart3 and @DateEnd3
OR
DatumKolumn BETWEEN @DateStart4 and @DateEnd4

Svårigheten är att generera korrekta intervall för alla sina dagar och sedan få in dem som parametrar till SQL Server

/micke

Hej,

Det är sant att om man lagrar i en kolumn som är av typen datetime och skall ha ut delar av dagar på ett antal dagar så blir databasfrågan lite krånglig.

Jag skulle nog påstå att en effektivare tabelldesign skulle lösa frågeställningen mycket enklare. Helt klart så skall ju datumet behandlas separat från tiden i frågan och varför då inte strunta i datetime kolumnen och istället skapa två nya kolumner, en för datum och en för klockslag.

På så sätt kan man få en fråga som använder färre where klausuler samtidigt som det går att indexera på ett effektivt sätt..

// Johan

Johan, du har helt rätt i att det kanske lämpligaste här är att fundera över schemat. Temporal data i databaser och sökningar i denna är ett svårt ämne som kan bli komplicerat om man har 'annorlunda' sökningar. Dock är det kanske inte alltid lämpligt att lagra datum och tid för sig i två kolumner eftersom man då tappar hela datumhanteringen i SQL Server. Och ska man ändå göra det så bör man lagra dem i något 'oberoende' format, t ex antal dagar sedan en viss startdag i en kolumn (för datum) samt sekunder in på dygnet (för tid), alternativt år, månad, dag, timme, minut och sekund i varsin kolumn. Men det komplicerar i sin tur andra frågeställningar.

Det aktuella problemet hade jag antagligen löst genom att lagra en datetime-kolumn och indexera denna. Sedan hade jag ställt en fråga för varje tidsintervall i taget om man har flera intervall som skiljer sig, alternativt något likt nedanstående om det gäller en viss tid men olika dagar (i ett intervall):

<code>
SELECT ...
FROM ...
WHERE datecol BETWEEN @startdate AND @enddate
AND HOUR(datecol) BETWEEN @starthour AND @endhour
</code>

Med ett index på datecol kan SQL Server snabbt hitta kandidatrader till frågan och sedan kolla exakt vilken timme dessa ligger på.