Matthias, please be kind with me if the following advice is boring for you, but you mentioned that you are migrating from Access.
I am active in teaching developers how to migrate from dBase DBFs, which in general have a lot of similarities with Access MDBs. The biggest mistake I have to correct out of the minds of people is the problem of overindexing and the missing sense for housekeeping. But a query like the one you posted should - under normal circumstances - deliver records in FAR less than 5-10 seconds, even on a slow machine and with 10.000s of records in the tables. - Are you familiar with the concept of Backup/Restore in Firebird - Did you read Helen's book especially on the "keep the Gap going" (between OIT and the actual transaction) - Have you checked that you do not have too many indexes, maybe with a lot of duplicate entries. In comparison to MDBs/DBFs where a lot of indexing was sometimes necessary to make acceptable orders, Firebird is sometimes indeed performing better withOUT an index. - Did you probably put different indexes on the same field? Such could make the plan-optimizer in Firebird just go crazy. -- Mit freundlichen Grüssen, André Knappstein, EDV und Controlling Verwaltungs- und Erlebniszentrum Marina Rünthe beta Eigenheim GmbH Hafenweg 4 59192 Bergkamen-Rünthe Durchwahl: +49 2389 9240 140 ~~~Ihre Nachricht~~~ MW> Hi, MW> Madhu Sasidhar, MD schrieb: >> Mathias, Please check your query syntax and Db (indexes and keys). >> Even the most complex JOINS with > 10000 records returned in my >> VB.NET does not take more than a few milliseconds. >> You may have to optimize your Db/SP or query. MS MW> well, unfortunately I can't seem to find a problem with my query or MW> indexes/keys. MW> Maybe you can see any sense in this and help: MW> Query: MW> ------ MW> SELECT visits.VISITED, visits.BROWSER, SiteUrls.URL, titles.TITLE MW> FROM SiteUrls INNER JOIN (titles INNER JOIN visits ON titles.IDTITLE = MW> visits.TITLEID) ON SiteUrls.idSiteUrl = visits.URLID MW> ORDER BY visits.VISITED DESC; MW> (Using RIGHT JOIN does not make a difference.) MW> On those tables, the timestamp column "visits.VISITED" has a descending MW> index, while titles.IDTITLE and SiteUrls.idSiteUrl are (bigint) primary MW> keys and visits.TitleID and visits.URLID are bigint columns with MW> ascending index sort order. MW> visits.BROWSER, SiteUrls.URL and titles.TITLE are not indexed because no MW> grouping or sorting takes place on this columns. MW> I also rebuild the indexes (deactivate/activate) and updated database MW> statistics at no avail. MW> Did I overlook something? MW> Regards, MW> Mathias Wuehrmann MW> Mit freundlichen Grüßen, MW> Mathias Wührmann MW> FLEXact Informationssysteme ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Firebird-net-provider mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
