Finne forrige post med SQL.

[cazber]

Bruker Delphi 2007 og ADO (TADOQuery) for å aksessere en database. I en tabell, så har jeg tiden lagret i 4 forskjellige felt (Dato, Time, Minutt og sekund). Jeg har funnet en record, men trenger så å finne forrige record i tid.

Har fått et SQL statement til å virke, men synes det er litt mye og rotete. Når jeg kommer i en slik situasjon, så pleier det å finnes enklere måter å gjøre ting på. Noen forslag ?

 

TIA

[___]

Bruker Delphi 2007 og ADO (TADOQuery) for å aksessere en database. I en tabell, så har jeg tiden lagret i 4 forskjellige felt (Dato, Time, Minutt og sekund). Jeg har funnet en record, men trenger så å finne forrige record i tid.

Har fått et SQL statement til å virke, men synes det er litt mye og rotete. Når jeg kommer i en slik situasjon, så pleier det å finnes enklere måter å gjøre ting på. Noen forslag ?

 

TIA

 

Det jeg ikke skjønner er hvorfor du er nødt til å splitte opp tiden i fire forskjellige felter, når det finnes diverse helt kurante datatyper for å lagre et tidspunkt som EN verdi.

 

Werner

[cazber]

Det jeg ikke skjønner er hvorfor du er nødt til å splitte opp tiden i fire forskjellige felter, når det finnes diverse helt kurante datatyper for å lagre et tidspunkt som EN verdi.

 

Werner

 

Dette er flere applikasjoner som skal portes fra en Pervasive.SQL database, og til en MSSQL database. Noen applikasjoner er programmert i Delphi, og skal nå bruke ADO, mens andre er programmert i Visual Dataflex. For Delphi applikasjonene hadde det vært helt ok å f.eks. bruke en Timestamp, men for Dataflex applikasjonene (ca 130 stk), så er ikke det aktuellt pga en driver som benyttes mot databasen (Dataflex er record orientert og bruker ikke SQL syntaks). Vi har også mer enn 700 tabeller i databasen, og et sted mellom 130 og 140 programmer, så det å endre på slike ting er ikke noe man gjør raskt:)

[JohndoeMAKT]

Er dette mulig i det hele tatt? Siden de kjente variablene ikke utgjør noe unikt vil du aldri kunne finne ut akkurat hvilken rad du har "valgt" og dermed ikke finne ut hvilken som er rett før.

 

Tenk deg at du har valgt deg ut en rad med datetime = '2008-01-01 00:00:00' og at tabellen din ser ut som dette:

 

id, date, time, minute, second

1, 2007-12-30, 23, 00, 00

2, 2008-01-01, 00, 00, 00

3, 2008-01-01, 00, 00, 00

4, 2008-01-01, 00, 00, 00

5, 2008-01-01, 00, 00, 00

6, 2008-01-01, 01, 00, 00

 

Hvilken er din valgte? Hva er den neste? Hvilken rad vil du ha utvalgt?

[j000rn]

SELECT TOP 1 * FROM blah WHERE time <= valgteTime AND minute <= valgteMinute AND second <= valgteSecond AND id <> valgteID

ORDER BY hour DESC, minute DESC, second DESC

 

Men eller er jeg enig i de andre her at databasedesignet ikke virker helt stødig...

[cazber]

SELECT TOP 1 * FROM blah WHERE time <= valgteTime AND minute <= valgteMinute AND second <= valgteSecond AND id <> valgteID

ORDER BY hour DESC, minute DESC, second DESC

 

Men eller er jeg enig i de andre her at databasedesignet ikke virker helt stødig...

 

He he. Databasedesignet er helt ok det, så lenge man holder på med record basert interface, og ikke SQL. Problemet med SQL er at man ikke kan angi hvilken index man jobber med, og at man skal ha forrige eller neste på en index. I databasen har vi en index på "Dato + Time + Minutt + Sekund + en unik id". Dvs at tabellen er sortert etter tiden. Det er da ikke noe problem å finne forrige og eller neste post. Dette designet har vi brukt i mange år, og i mange programmer.

 

Ved bruk av SQL, så kan man ikke gjøre slik du har gjort (gjorde det samme selv først), pga sekundet på forrige post, kan være større, mens minuttet kan være mindre. Da vil man ikke finne forrige post (pga sjekken du har på at sekundet<=Nå sekundet).

 

Den løsningen vi har brukt, er å sette sammen Dato+Time+Minutt+Sekund i SQL statementet til en timestamp, og bruke denne til sammenlikning. Dette virker men det blir et så rotete å bygge opp dette SQL statement:)

Antar at konklusjonen dog er at det er dette vi må gjøre dessverre.

[Manfred]

Dette designet har vi brukt i mange år, og i mange programmer.

"Dette har vi gjort i ALLE år, og ingen skal komme hit og si at det finnes bedre løsninger!". Jeg trodde det var slikt man hørte fra bedriftsledere i 50-åra jeg...

[kaffenils]

Ved bruk av SQL, så kan man ikke gjøre slik du har gjort (gjorde det samme selv først), pga sekundet på forrige post, kan være større, mens minuttet kan være mindre. Da vil man ikke finne forrige post (pga sjekken du har på at sekundet<=Nå sekundet).

 

Den løsningen vi har brukt, er å sette sammen Dato+Time+Minutt+Sekund i SQL statementet til en timestamp, og bruke denne til sammenlikning. Dette virker men det blir et så rotete å bygge opp dette SQL statement:)

Antar at konklusjonen dog er at det er dette vi må gjøre dessverre.

 

Og du står fortsatt på at dere har valgt et optimalt design når du faktisk støter på slike problemer.

 

Indexen dere har på Dato+Time+Minutt+Sekund blir mest sannsynlig aldri brukt av queryoptimizeren, nettopp på grunn av at dere ikke sammelinker mot kolonnene direkte, men mot en timstamp-funksjon som tar verdiene som parameter og kalkulerer en ny verdi som dere sammenlikner mot.

 

Kan du gi meg ett godt argument for hvorfor dere har splittet opp dato+tid på denne måten?

[cazber]

Dette designet har vi brukt i mange år, og i mange programmer.

"Dette har vi gjort i ALLE år, og ingen skal komme hit og si at det finnes bedre løsninger!". Jeg trodde det var slikt man hørte fra bedriftsledere i 50-åra jeg...

Ja. Dette har vi gjort i alle år pga vi ikke har brukt SQL til å aksessere databasen. Er helt enig at det er et idiotisk design i SQL sammenheng. Problemet er at nå er databasen slik, og det er for mye jobb å lete gjennom 780 tabeller + 130 til 140 programmer for å modifisere dem. Dessuten aksesserer de fleste av disse programmene MSSQL databasen via en driver, som ikke preseterer en timestamp som datatype i programmeringsspråket. Vi har da hatt valget mellom å lage et numerisk felt, som er antall sekunder fra en gitt dato, eller bruke dato+time+minutt+sekund. Siden det siste valget er lett leslig med de verktøy som vi bruker til å se på databasen med (utnom programmene), så har vi valgt dette.

[cazber]

Kan du gi meg ett godt argument for hvorfor dere har splittet opp dato+tid på denne måten?

 

Ja.

1. Når databasen ble designet, brukte vi ikke SQL. Driveren mot databasen har ikke en timestamp, så alternativet til Dato+Time+Minutt+Sekund, var et numerisk felt som var summen av dem. I og med at vi ganske ofte bruker andre verktøy enn programmene til å "se" i databasen, så har det vært greit med Dato+Time+Minutt+Sekund feltene.

 

2. De fleste av programmene våre (ca 130 stk) kommer fortsatt ikke til å ha tilgang til en timestamp, men MÅ ha feltene hver for seg, eller summert i et "uleslig" numeric felt.

 

Det er bare noen få av våre programmer vi trenger å skrive om til å bruke SQL statements, og det er her vi har problemet. De resterende (ca 130 programmer), vil bare bytte ut databasedriveren, og så kjøre på MSSQL server, i stedet for Pervasive.SQL.

De programmene vi må skrive om, er Delphi programmer, som før brukte TITAN database driver mot Pervasive.SQL databasen. Vi benytter nå Delphi dbGo (ADO) til dette. I delphi (ado), hadde vi så klart brukt en timestamp, men denne kan som sagt ikke leses i de andre 130 programmene. Vi velge da å endre noen få, i stedet for mange programmer. Det er også slike dato+time+minutt+sekund felter utrolig mange steder i databasen, så det vil ta allt for lang tid å endre på dette (databasen har tross allt mellom 700 og 800 tabeller).

Endret 14. mars 2008 av cazber

[kaffenils]

Dessuten aksesserer de fleste av disse programmene MSSQL databasen via en driver, som ikke preseterer en timestamp som datatype i programmeringsspråket.

 

Er dette en driver (er det et dataaksesslag du mener) dere har laget selv? Hvis ja, og alle de 130-140 programmene bruker samme driver/DAL så burde det vel ikke være alt for mye arbeid å skrive denne om til å bruke DateTime i stedet for dato+time+minutt+sekund. Hvis dere ikke er villige til å ofre litt arbeid med å gå over til å tenke i set-basert og ikke cursor/record-basert så vil dere ganske sannsynlig får store problemer med ytelse hvis dere ikke holder tungen rett i munnen når dere kjører spørringer med filter mot dato- og tidsverdiene i databasen.

[JohndoeMAKT]

Lett leselig men dårlig fungerende. UNIX-time ( "antall sekunder siden en gitt dato" ) hadde nok absolutt vært å anbefale over systemet dere valgte og hadde også fungert mot SQL. Hva skjer f.eks ved bytte til sommertid? Opererer dere ut fra GMT eller CET?

 

Men nok klaging, gjort er gjort og legacy er legacy.

 

Løsningen ved å bruke en intern datoparsefunksjon i databasesystemet mot de forskjellige feltene er nok den beste du vil få til på det designet.

[kaffenils]

Skjønner godt at det er for mye arbeid å endre så mange applikasjoner.

 

Jeg klarer dessverre ikke å se andre måter å gjøre det på enn slik du allerede så vidt har forklart ar du gjør det.

 

Hvis du poster en av spørringen her så kan jeg godt ta en titt for å se om det er noe som kan gjøres bedre/mer effektivt.

[blackbrrd]

Kan du ikke bare lage en funksjon som gjør om de fire feltene til en timestamp. Funksjonen tar inn de fire feltene som parameter? I postgres kunne du isåfall også laget en index på resultatet av denne funksjonen...

 

SQL-en hadde da blitt seende litt ut som dette:

SELECT * FROM table WHERE time(Dato, Time, Minutt, sekund) < time(01032008,23,13,60) ORDER BY Dato DESC, hour DESC, minute DESC, second DESC LIMIT 1

 

time er da en funksjon som gjør om de fire parametrene til et timestamp.

 

SQL-en blir ikke spesiellt stygg heller.

Endret 14. mars 2008 av blackbrrd

[kaffenils]

Faktisk en veldig god ide blackbrrd. Hvorfor tenkte ikke jeg på det

 

Til trådstarter: les mer om hvordan en computer column opprettes i SQL Server og hvilke krav som stilles for å opprette en index på denne.

 

CREATE TABLE og finn der det står om computed_column_definition

Creating indexes on computed columns

[cazber]

Er dette en driver (er det et dataaksesslag du mener) dere har laget selv? Hvis ja, og alle de 130-140 programmene bruker samme driver/DAL så burde det vel ikke være alt for mye arbeid å skrive denne om til å bruke DateTime i stedet for dato+time+minutt+sekund. Hvis dere ikke er villige til å ofre litt arbeid med å gå over til å tenke i set-basert og ikke cursor/record-basert så vil dere ganske sannsynlig får store problemer med ytelse hvis dere ikke holder tungen rett i munnen når dere kjører spørringer med filter mot dato- og tidsverdiene i databasen.

Nei, det er ikke en driver vi har laget selv, og vi har ikke tilgang til kildekoden. Visual Dataflex, som vi har programmert disse programmene i, har drivere mot Pervasive.SQL, MSSQL, Oracle, ODBC, og en eller annen database som jeg ikke husker navnet på i farten.

Vi er smertelig klar over performance problemet, men det har vi tenkt å ta etterhvert. Første step i konverteringen er å få allt til å gå, slik programmene nå er (Visual Dataflex Programmene), så vil vi kjøre tester, og endre til set basert tankegang de stedene der performance blir dårlig. Da kan det være vi får en endring på denne dato+time+minutt+sekund konstruksjonen, men akkurat nå, så har vi ikke tid til dette.

[cazber]

Kan du ikke bare lage en funksjon som gjør om de fire feltene til en timestamp. Funksjonen tar inn de fire feltene som parameter? I postgres kunne du isåfall også laget en index på resultatet av denne funksjonen...

 

SQL-en hadde da blitt seende litt ut som dette:

SELECT * FROM table WHERE time(Dato, Time, Minutt, sekund) < time(01032008,23,13,60) ORDER BY Dato DESC, hour DESC, minute DESC, second DESC LIMIT 1

 

time er da en funksjon som gjør om de fire parametrene til et timestamp.

 

SQL-en blir ikke spesiellt stygg heller.

 

Ikke noe dum ide nei. Det er i prinsippet det vi har gjort, men med å bruke "convert" direkte i sql statementet. Da blir statementet stort og stygt, men med din løsning, så ser det jo ikke så aller værst ut:)

[cazber]

Faktisk en veldig god ide blackbrrd. Hvorfor tenkte ikke jeg på det

 

Til trådstarter: les mer om hvordan en computer column opprettes i SQL Server og hvilke krav som stilles for å opprette en index på denne.

 

CREATE TABLE og finn der det står om computed_column_definition

Creating indexes on computed columns

Dette er dessverre ikke mulig for oss pga hvordan driveren fungerer. Vi er nødt til å gjøre oppgraderinger av databasen via driveren pga noen filer som genereres av driveren for å aksessere tabellene. Gjør vi noe med tabellen direkte i sql, så matcher ikke dette lenger, og vi får ikke aksessert tabellene. En annen mulig hindring for dette, er om Pervasive.SQL hat samme støtten for dette. Vi er foreløpig nødt til å kjøre samme kode på begge databasene (Pervasive.SQL og MSSQL), men takker for alle forslag.

[blackbrrd]

CREATE FUNCTION finnes da i PervasiveSQL også?

[cazber]

CREATE FUNCTION finnes da i PervasiveSQL også?

Pervasive har CREATE FUNCTION ja, men det jeg mente var å opprette en index på en computed column. Vi kan foreløpig ikke basere oss på å endre strukturen til noen tabeller, uten å gå via driveren til Visual Dataflex. Vi får da problemer ved oppgradering av databasen pga driveren trenger å lagre informasjon om hver enkelt tabell i noen tekst filer som den leser. Disse filene brukes til å åpne tabellene, og presentere tabellene riktig for Visual Dataflex programmene (alltid noe bøll med å skulle bruke opp til 23 år gammel kode).