08. juli 2005 - 15:08Der er
21 kommentarer og 1 løsning
Søge om dato eksisterer
I en af mine databaser er der en tabel som er linket fra Navision. Når der skal hentes data fra tabellen tager det ca. 8-10 minutter. Brugeren vælger en dato på en ActiveX kalender og programmet søger så efter datoen. Jeg kunne godt tænke mig at programmet hurtigst muligt fandt ud af om der var bare EN eneste forekomst af den valgte dato i databasen, men hvordan gør man det hurtigst? Jeg er ikke sikker på at feltet dato er indekseret, men det kan jeg vel finde ud af. Hvordan skal jeg søge om datoen findes, i VBA?
Denne side indeholder artikler med forskellige perspektiver på Identity & Access Management i private og offentlige organisationer. Artiklerne behandler aktuelle IAM-emner og leveres af producenter, rådgivere og implementeringspartnere.
Well, den kører nu...(tager de der 8-10 minutter) Nej, det er godt nok ikke en "rigtig" løkke, men den bliver da ved indtil den har talt alle forekomster af den valgte dato, jeg vil bare søge indtil jeg har fået bekræftet at EN (1) forekomst eksisterer. Ikke flere/mere.
Ja. Ok, here goes. Dit forslag vil søge ALLE 2.000.000 poster igennem, UANSET om den finder datoen i post 1, post 142.232, sidste post eller OVERHOVEDET ikke! Mit forslag (teoretisk) vil søge efter FØRSTE forekomst af datoen, så hvis den er i post 1, skal der IKKE søges videre, men meldes tilbage at datoen eksisterer.
In Access when you query a table then ALL records are read into memory whereafter Access starts looking through the each record to find waht you want.
I dont know much about Navision, but if it is a true database server then you can use a PassThrough query which send the query to the server and only returns the result set. You can also use this method if you are using SQL Server and link tables. Using PassThrough queries is MUCH faster than querying your LINKED SQL Server tables!
I have created a pass-through query, with this SQL SELECT * FROM Lønpost which is fine, it returns all the posts, but I would like to get a certain date, like this: SELECT * FROM Lønpost WHERE Dato=#09-01-2005# But this returns an error: ODBC: Kaldet lykkedes ikke. [Navision Software a/s][Navision Financials ODBC Driver]Unexpected extra token: # (#1017)
Must have something to do with the formatting of the date...
You MUST use the same SQL Syntax as you would if you made the SQL in Navision, so I very much doubt that # is used with date fields. Try replacing with '
I have NO IDEA as to how such a query would look like in Navision, but I found an example on Google, so now it works, but it's NOT faster than a query against the linked table, so no performance gain there. :(
No, it doesn't, not even close. The table I query contains aprox. 2 million records and it doesn't matter whether I ask for SELECT * FROM Table WHERE Dato=09-01-2005 or SELECT Date FROM Table WHERE Dato=09-01-2005
As I said, we have 2 million records, scattered over a 9 year period, with records falling on everyother sunday
Navision will still have to go through ALL records if the date field is not indexed, but I would have thought that it would be MUCH faster than sending all records over the network and then finding the records needed. Must be a slow server that Navision is on :o)
:) I don't think it is, got a 18GB database on Xeon 3.06Ghz, 1.5 GB RAM
I just asked the DBA and she said that the field is NOT indexed, that is, it does not have a "Key" (Navision lingo) But I found out that another table ALSO holds the dates and it only contains about 400 records, so I will just query that instead. Problem solved! :) Thanks again, terry
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
