Zugg Software :: View topic - %mapfilter vs. %mapquery vs. direct COM approach

Magician Joined: 28 Jan 2007 Posts: 430

Now, bear with me, heh.

Have been doing speed comparisons towards 3 separate routes of data look-ups over last couple of days.

I know that once Map database goes completely into SQL and all the bugs will be ironed out, all this (least the COM aspect) will be moot, but...

Until now I've used %mapfilter(%concat collaboration to #loop %numrooms into a DB list from where I choose one I want to run to (within one Zone).

SubAdmin Joined: 18 Nov 2001 Posts: 5182

Step 1. Get my Toolbox from the package library. It has some predefined queries for various things, autmatically links to your map, and will give you quite a few examples of how to do things.

Step 2. Start writing you own queries. For example the correct way to do the query above is with a JOIN; while the way it is works the JOIN will produce results faster.

Magician Joined: 28 Jan 2007 Posts: 430

Well, I'm still pretty much in the process of getting myself familiar on a more deeper level with the program until
I choose to eventually buy it (which I most likely will but not a jump the gun sort of type and hence still having few weeks left).
I'm sure though that once I've bought it, package library will become my next best friend.

Can you point me to some help material about queries such like these perhaps? It took me quite a while to understand the mechanics of
queries while using %mapfilter(%concat but finally I understood it, most of it anyway. Doing more exact and complex queries into
Map DB itself is something pretty new to me.

Thanks,

Prog

Wizard Joined: 14 Aug 2004 Posts: 1269

So you basically want
AND ","ZoneID"," = ",%zonenum
added to the COM query?

Magician Joined: 28 Jan 2007 Posts: 430

Basically, thats true.

Spent few hours last night, reading through SQL Database Tutorial I found on internet and I must say that most likely I'd be able to do
queries I need based on what I learned, but what I've never completely understood is the usage of %concat in the COM script above.

Can't I do something like

SELECT z.name FROM ObjectTBL WHERE o.name LIKE '%", %-1, "%' AND ","ZoneID"," = ",%zonenum

instead?

Sorry for coming across dumb, but I haven't put too much serious thought into direct queries such like these before.
All I've used so far is the easy setup I had with %mapfilter.

Prog

Wizard Joined: 22 Mar 2007 Posts: 2320

%concat() just takes several things and puts them together into one long string. Often, you can assume that Cmud will automatically put pieces of string together into a single large string, but Cmud does not guarantee that. Using %concat() simply ensures that it happens. I think that %mapquery() may be one of the functions that does not always automatically concatenate strings. In any case, it never hurts to use %concat() to put strings together, and is much more robust. When in doubt, use %concat() to be sure.

Wizard Joined: 14 Aug 2004 Posts: 1269

So it probably becomes something like this:

Magician Joined: 28 Jan 2007 Posts: 430

Sweet! Yeah, when I read through some web tutorial for SQL Queries few days ago the impression I got was that JOIN is used when there's
more than one table needed.

The thing is, I think I understood the tutorial pretty well. Strangely, where I have always had problems is forming a correct %concat like you have above. Though, the basic principle you use above seems to be same as I've used in my original %mapfilter query on first glance, hmm... Maybe I'm just panicking too much Confused

Meanwhile I'll try it out and post speed results in case anyone's interested.

Thanks Seb.

EDIT: Started incorporating all this into code and one thing got me thinking. The original script takes the mapfilter, gets matching data, and as you've already set the current zone by it, you can do #loop %numrooms and parse the data. Can't something like this be done with this setup as well, for example, couldn't I use ORDER BY ObjID to get roomvnums and then somehow parsing them into a db?
I'm just bursting out ideas here, heh.

EDIT #2: Basically if I'd be able to get the data by roomvnums, I should be able to make a new string list and use that one for the actual walking. Something like, walk_list=@rs.GetString. Wouldn't it work?

Prog

Wizard Joined: 14 Aug 2004 Posts: 1269

Magician Joined: 28 Jan 2007 Posts: 430

Well, I haven't figured out how to parse the data got by COM into a db nor have I really had time to.

However just now I made some speed tests. Results are astonishing.

Wizard Joined: 14 Aug 2004 Posts: 1269

When doing database queries like this, e.g. the SQL one since that is an actual query, you should generally take the *first* result as your time (so 16ms). If you repeat the query, the data is cached, so it's hardly a valid timing test. If you want to repeat the timing test, you'll probably need to close CMUD and reopen it, or do whatever to make sure your query you are just about to test isn't cached. Also, it's worth reversing the order of your tests. So if you tested %mapfilter first, it might be slower than if you tested it afterward the other methods. So when comparing database queries for speed I often run them first one way round and then the other way round (and then one may be able to average two results).

Bear in mind also that the first code block is also doing a %replace. That won't have any noticeable affect on the times, but you should also do this in the second code block or any single quotes in your %-1 parameter will not be escaped properly and the query will not work the way you want (or at all). Also, I haven't seen any timings with %mapquery... That should be more of a half-way house between these two, in terms of what it does (I would guess).

Posted: Tue Aug 12, 2008 7:37 pm

The %mapfilter is creating a "filter" on the existing map database. In Delphi we have things called "DataSets" that you can apply a filter to. So the Dataset is filled with "SELECT * FROM ObjectTbl" and then %mapfilter applies a filter to the result. This is always going to be slower than a direct SQL query. The %mapfilter is not intended for full speed...it is intended for convenience because then you can use %numrooms etc to loop through the rooms in the remaining filter set.

Keep in mind that the database format is changing in v3.0. So if you write scripts that use ADO COM objects, then all of your scripts will break in v3.0+. On the other hand, a filter of an SQLite database is much faster than a filter of an ADO database in Delphi.

So unless the speed difference is critical to your script, you should never be accessing the map database with low-level COM objects unless you want to spend all of the time rewriting them for the new CMUD versions. And since you cannot create COM objects for SQLite, you'll need to use Lua to perform the low-level access. This means that rewriting your scripts will not be trivial.

Wizard Joined: 14 Aug 2004 Posts: 1269

That's what I thought Zugg, but I hoped you'd confirm my suspicions about filtering a DataSet... Smile

%mapquery seems like it should be faster because it's not doing that filtering, and it shouldn't break with the upgrade to v3.0+ (hence why I suggested he show me the timings).

Magician Joined: 28 Jan 2007 Posts: 430

With all due respect, even though my speed tests were unorthodox, for me it seems reasonable to assume, based on the numbers I saw, that at this very moment, direct COM object query is a lot more faster than using %mapfilter.

Deeper explanation is really appreciated though. While I'm a really lazy person I had a doubt anyway for going through with re-writing this particular script using COM (plus I still haven't come up with a way to parse the data into DB, heh).

It just seemed interesting to me, that they have such speed difference in comparison. Sad that I discovered it way too late, though (because as you said, Zugg, %mapfilter will be faster regardless in the future anyway).

Anyway, thanks for clearing this up.

EDIT: Heh. Seb, I suppose I could re-write my stuff using %mapquery then. Though when I did some general kind of speed tests between %mapfilter and %mapquery, the latter seemed to have pretty much same speed if not slower. But I guess using that I might at least do something right in time for future Confused

Prog