Zugg Software :: View topic - DB Seek --- slow

Newbie Joined: 11 Nov 2002 Posts: 5 Location: Canada

I've managed to write the triggers and such to populate an EQ db. It all works and I'm happy with how it functions. The problem at this point is simply speed. The DB contains about 3000 records and scanning them to do a possible insert is starting to drag Zmud down while I'm out there trying to kill some big greeblie.

I need some suggestions on how to speed the code below up quite a bit. I started off using a query but wasn't able to get it to do comparisons on the text fields properly. Is there a way to make this faster? If it's using a query then an example with multiple comparisons (including on text fields) would be appreciated.

Thanks
#if {@inIdent = 1} {#IF {!@Scanning} {
#DBLOAD Equipment
#VAR Scanning 1
#VAR new 1
#VIEW ALL
#IF { %isMember( @NewItem.ItemType, @DBViews)} {
#View @NewItem.ItemType
#SHOW {Using View @NewItem.ItemType}
}
#DBFIRST
#LOOP %numrec( ) {
#IF { &ItemName = @NewItem.ItemName AND &Weight = @NewItem.Weight AND &Level = @NewItem.Level AND &Rent = @NewItem.Rent AND &Value = @NewItem.Value} {
#VAR New 0
#Show Bail. Item @NewItem.ItemName Not Added
#VAR Scanning 0
#VAR InIdent 0
#Abort 1
}
#DBNEXT
}
#VAR Scanning 0
#IF {@New} {
#SHOW Item @NewItem.ItemName Added
#New All @NewItem
#DBSAVE
}
#IF {!@New} {#SHOW Duplicate: @NewItem.ItemName Not Added}
#VAR InIdent 0
}}

SubAdmin Joined: 18 Nov 2001 Posts: 5182

First off your using #LOOP and stepping through all items. It isn't very effiecient. Use %query to narrow the database records to a list of those matching the @NewItem.ItemName. Then #FORALL that list with #DBGET instead of #LOOP and #DBNEXT. That eliminates the item name comparison from the #IF. The next speed trick is to expand you #IF manually. I will show you what I mean:
#IF ((a=a) and (b=b) and (@c=@d)) {
and this result in the same, but the second is faster in most cases:
#IF (a=a) {#IF (b=b) {#IF (@c=@d) {
The reason the second is general faster is because a failure in the first argument will skip evaluation of all other arguments. In my contrived example we would switch the order around so the @c=@d test would be first since that is the one most likely to fail. Similar optimization can be done in your real world code. Also make sure to use the proper syntax and add those extra (). They cost little to type and when used with the & instead of typing AND are faster and more legible.

Those changes should be enough to put your code up to speed, I am guessing item level would be the most commonly changed stat and hence should be tested first in the new format.