index CDX on the fly
in [Visual Objects]
|
Prev: Time flies....
Next: Serial comm in a VOService
From: Ginny Caughey on 6 Jan 2010 08:30 Forgot to answer the actual question - SQL can do stuff like "select col1, col2 from someTable where {any condition you can dream up} order by {whatever order you want}" and it will outperform DBF. But the SQL query analyzer can help you see where perhaps having certain columns in an index could speed this up even more. Even today I am sometimes surprised by the query analyzer's suggestions, but it is pretty much always right. You don't add the indexes as you need them for one report - you just add them to the database and SQL maintains statistics about the data so it know what query plan to generate for queries including which indexes it wants to use. -- Ginny Caughey www.wasteworks.com "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:00c05cbc$0$17009$c3e8da3(a)news.astraweb.com... > To answer your question about SQL - > > It's more than just that the work is done on the server machine, although > only sending the data you actually need across the wire is a benefit. The > biggest advantage is the SQL query processor - software that computer > scientists who spend their whole lives specializing in data access > constantly tweak to perfection. Combined with the right indexes and decent > server hardware, you can do all manner of complex queries with great > performance. (And where you don't have great performance, the best SQL > databases come with query analyzers that will suggest where a new index > might help a lot.) Even though I know the way my DBF data is used and have > the indexes I need to maximize performance, my SQL reports run faster, and > sometimes orders of magnitude faster, than the DBF versions. Of course > I've rewritten my reports to specifically take advantage of SQL - if I > just ran the old DBF code against a SQL database, I wouldn't see such a > dramatic improvement. > > A second advantage of SQL that is important to my largest customers is > security. Their administrators can control who gets to do what with the > data at a very fine level. All I have to do is use authentication based on > the user and the SQL engine does the rest. Of course if a customer chooses > to leave security wide open on the database, that works too without any > changes in my software. > > And finally, my customers are switching to the SQL versions of my apps > because their databases have just grown too large for DBF files. They may > have a decade or more of data and don't want to keep it off line or in > different files. "Huge" dbf files when move to SQL make databases that are > considered tiny by SQL standards. > > Not a specific advantage for customers, but for me now that I've worked in > SQL for several years, I find designing and coding apps to run against a > SQL database is quicker and easier and especially since it also allows me > to use more modern languages like C# for new work. This does become an > advantage to a customer if I can do the work in fewer hours (therefore > costing them less), so it's not unusual for my customers these days who > need custom work to choose to move to the SQL versions of my apps for that > reason. > > -- > > Ginny Caughey > www.wasteworks.com > > > > > "Perfect Solution Software" <infoNOSPAM1DELETE(a)magicorg.com> wrote in > message news:4b447ebe$0$6819$5fc30a8(a)news.tiscali.it... >> Yes, I need data mostly for browser situations. >> I came up with a custom built temporary CDX index file. >> I set an upper and bottom scope, than go through the rest >> of the DBF file evaluating the filter condition and adding to the custom >> index only >> records that meet the filter using EVAL() >> Does SQL do something similar or with SQL this kind of things (I mean >> extracting particular >> data and sorting) is much faster than in DBF in this particular situation >> where you do not have indexed data. >> In SQL you alway first "extract" data from your file befor showing them >> in browser? >> How does SQL work compared to DBF?? >> The only thing I know about SQL is that the work is done on the server >> machine therefore the speed >> should be much better for SQL, but what else is the difference?? >> >> cbFilter := &( '{||' + self:cfilterstring + '}' ) >> >> >> >> >> self:oDBDocumenti:gotop() >> >> >> >> do while !self:oDBDocumenti:eof >> >> MyInfoMessage("elaborazione.....") >> >> IF self:oDBDocumenti:Eval( cbFilter,,, 1 ) >> >> >> self:oDBDocumenti:OrderKeyAdd('TMPORD', cTmpIndex) >> >> ENDIF >> >> >> self:oDBDocumenti:Skip(1) >> >> >> enddo >> >> and then set the order to TMPORD >> >> >> regards >> Paolo Kalc >> >> "Geoff Schaller" <geoffx(a)softxwareobjectives.com.au> ha scritto nel >> messaggio news:mUY0n.67200$ze1.38347(a)news-server.bigpond.net.au... >>> Dirk, >>> >>> This really only works in a 'report' situation but is still tragic if >>> there are a lot of rows. I think this guy is talking generic browsers >>> where he is expecting a lot of data. This isn't unlike SQL where data >>> volume is an important performance factor. >>> >>> Geoff >>> >>> >>> >>> "Dirk (Belgium)" <dirk.dot.herijgers(a)pbprojects.dot.be> wrote in message >>> news:P9Y0n.2068$0a2.609(a)newsfe21.ams2: >>> >>>> Perfect Solution Software wrote: >>>> >>>> >> >>
From: Amilcar A. Camargo on 6 Jan 2010 12:20 Hi Paolo, On Tue, 5 Jan 2010 16:39:32 +0100, "Perfect Solution Software" <infoNOSPAM1DELETE(a)magicorg.com> wrote: >I use DBF files with CDX indexes with a lot of tags. >But sometimes you need to display data in different ways not included in any >Tag index. >The idea is create a temporary index on the fly, display the DBF data and >then >when the user closes the browser displaying the data delete the index. >Does anyone use this approach?? I do this for browsers so the user can order data clicking on the column header. My approach is to build temporary indexes (normally the file resides in windows temp directory) as necessary. The catch: build your indexes based on the 'scope' already applied to your dbf file so it will perform fast. Otherwise, it could take too much time and upset your users. To deal with final clean up, extend your base server Close() method and, after calling SUPER, delete your temp file. It should prove no problem. Have you considered migrating to SQL? This could be far easier in that environment. HTH, Amilcar A. Camargo F. Guatemala, C. A.
From: Perfect Solution Software on 6 Jan 2010 12:35 Thank you for your suggestion, really faster Paolo "Alessandro Antonangeli" <alcati.r3m0v3th1s(a)alcati.com> ha scritto nel messaggio news:hi21dd$369$1(a)speranza.aioe.org... > Hi Paolo > > Look also at the CreateOrder Help: may be that a > > oDbServer:SetOrderCondition(,cbFilter) > oDbServer:CreateOrder('TMPORD', cTmpIndex) > oDbServer:SetOrderCondition() > > could be faster > > > -- > Ciao > Alessandro
From: Perfect Solution Software on 6 Jan 2010 12:51 Ginny But when you show a window with a SQL browser in it, at first the browser is empty, then the user selects what he would like to query, presses a button to start the query and then you show the result data in the browser or as it is common in the DBF world and bBrowser, the data are displayed as soon as the window is shown and then the user can change the date interval, select a customer to show only that data etc.. To be more clear I mean that in DBF you do not have to care about the data you have to display in the browser as it is bBrowser that stops reading data as soon as the "buffer" is full even there is 1GB DBF, in SQL is the same?? Or you have to take care about the data queried and returned to the browser?? For example if someone queries a period of 10 years you have to wait for the SQL engine to return all the data to the browser or as it is in DBF when the bBrowser "buffer" is full it stops. Hope this question is not too stupid. I do not know very much about SQL. Paolo > > SQL can do stuff like "select col1, col2 from someTable where {any > condition you can dream up} order by {whatever order you want}" and it > will outperform DBF. But the SQL query analyzer can help you see where > perhaps having certain columns in an index could speed this up even more. > Even today I am sometimes surprised by the query analyzer's suggestions, > but it is pretty much always right. You don't add the indexes as you need > them for one report - you just add them to the database and SQL maintains > statistics about the data so it know what query plan to generate for > queries including which indexes it wants to use. >
From: Ginny Caughey on 6 Jan 2010 13:21
Paolo, In general you're right, but I do have some browsers that start by showing the top 1000 rows. I really don't see any downside at all to using SQL, and I would never allow anybody to query 10 years of data in a browser. I just give them the top 1000 (or whatever) rows and indicate to them that this is a partial list. Who really wants to scroll through 3.5 million rows (I have some customers with this much data!) in a browser anyway? -- Ginny Caughey www.wasteworks.com "Perfect Solution Software" <infoNOSPAM1DELETE(a)magicorg.com> wrote in message news:4b44cd87$0$709$5fc30a8(a)news.tiscali.it... > Ginny > > But when you show a window with a SQL browser in it, at first the browser > is empty, then the > user selects what he would like to query, presses a button to start the > query and then you show > the result data in the browser or as it is common in the DBF world and > bBrowser, > the data are displayed as soon as the window is shown and then the user > can > change the date interval, select a customer to show only that data etc.. > To be more clear I mean that in DBF you do not have to care about the data > you have > to display in the browser as it is bBrowser that stops reading data as > soon as the "buffer" is full even > there is 1GB DBF, in SQL is the same?? Or you have to take care about the > data > queried and returned to the browser?? > For example if someone queries a period of 10 years you have to wait for > the SQL engine > to return all the data to the browser or as it is in DBF when the bBrowser > "buffer" is full it stops. > Hope this question is not too stupid. I do not know very much about SQL. > > Paolo > > > >> >> SQL can do stuff like "select col1, col2 from someTable where {any >> condition you can dream up} order by {whatever order you want}" and it >> will outperform DBF. But the SQL query analyzer can help you see where >> perhaps having certain columns in an index could speed this up even more. >> Even today I am sometimes surprised by the query analyzer's suggestions, >> but it is pretty much always right. You don't add the indexes as you need >> them for one report - you just add them to the database and SQL maintains >> statistics about the data so it know what query plan to generate for >> queries including which indexes it wants to use. >> > > |