At 01:08 PM 4/03/2008, you wrote: >Hi André, > >André Knappstein, Controlling schrieb: >> please be kind with me if the following advice is boring for you, but >> you mentioned that you are migrating from Access. > >there is nothing boring about your advice, even if I knew some of those >things before ;-) > >> 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. > >I still struggle a bit at this, because as far as I can see, I now >optimized as good as I can - without getting the desired results.
You should avoid thinking of "optimization" and serving up Access SQL statements as opposite ends of the spectrum! Access constructs weird, non-standard statements to work around its query engine, which is not SQL. Here's the statement you quoted: SELECT visits.VISITED, visits.BROWSER, SiteUrls.URL, titles.TITLE FROM SiteUrls INNER JOIN (titles INNER JOIN visits ON titles.IDTITLE = visits.TITLEID) ON SiteUrls.idSiteUrl = visits.URLID ORDER BY visits.VISITED DESC; Rewrite it like this: SELECT v.VISITED, v.BROWSER, su.URL, t.TITLE FROM SiteUrls su JOIN visits v ON su.idSiteUrl = v.URLID JOIN titles t ON t.IDTITLE = v.TITLEID ORDER BY v.VISITED DESC; >> - Are you familiar with the concept of Backup/Restore in Firebird - > >Yes. From an Access point of view, its comparable to the compact/repair >feature. Hmm, not really. You can't compare them. Access is a file-based database and compact removes dead deleted records from files. Firebird is a multi-versioning record management system that doesn't store data in files at all. Besides backups, the backup/restore utilities try to remove garbage - obsolete record versions. There are other mechanisms in the engine itself that also perform this task. >> Did you read Helen's book especially on the "keep the Gap going" > >(between OIT and the actual transaction) - > >Never heard of that. Where can I get it? http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1204701677:102291&page=ibp_firebird_book Also Amazon and many other online booksellers. You can get it in PDF format from the publisher's website: http://www.apress.com/book/view/1590592794 >> 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. > >Triple-checked that. I tried without indexes, with guerilla-indexing-all >tactic and carefully setting indexes only when the resulting statistic >value is good enough. It did not seem to have any valuable impact on the >performance. Indexes may be useful on the fields you are joining TO and in WHERE clauses (which you're not using). A suitable index will speed up ORDER BY and GROUP BY. Your particular ORDER BY here will use the DESC index on v.Visited. >I'm stuck here. I'm not yet convinced that query optimization (or lack thereof) is your problem. If queries tend to get slower over time, I'd want to be looking at the progressive figures you're getting back from gstat -h. This area of things isn't driver-specific, although the practice of running long-running read-write transactions and poor transaction control in general certainly are problems created in your client applications. How? is a question to be answered by others. I'm an ObjectPascal programmer (Delphi and Kylix, mostly). I do urge you to try and forget everything you thought you had learnt from your experiences with Access. The parser for the Jet engine interprets brackets and some other syntax elements in non-standard ways. Use of Access syntax - if the Firebird DSQL engine accepts it at all - will typically spoil performance and, in some cases, will spoil the logic and return wrong results. Helen ------------------------------------------------------------------------- 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
