Can this SQL Select run faster?

[EzLogic]

VFP 9
SP2
DBFs are inside a DBC

Tables:
Inventory Master table
ICItem (Index: item, itmdesc, itemclass, itemsubclass, itemcategory, manufacturer,condition, itemcolor, barcode)

Inventory Location Table (could have more than 1 location per item/Warehouse ID bascially)
ICILoc (Index: item, locitem, Item+LocID, ItmDesc)

Inventory Item SKUS( each item could have multiple SKUs or aliases of SKUs)
ICItemSKU (Index: item, SKU)

I have a search form, where the user can search inventory using keyword searching, which finds the occurance of the string in the fields of inventory, based on my sql select below.

that works great..however I would like to see how i can speed up my search form. Especially, when the Item Location table (ICIloc has 100k+ records)

        lcStr = '%LG%'  && this is usually coming from the search box.. but, for here, i just put it like that.
        lcLocID = 'TEKNOSOFT'

	SELECT ;
		.f. as llSelect, ;
		icitem.ITEM,;
		icitem.ItmDesc,;
		icitem.ProdLine,;
		icitem.ItemClass,;
		icitem.ItemSubClass,;
		icitem.ItemCategory,;
		icitem.Manufacturer,;
		icitem.Condition,;
		icitem.ItemColor, ;
		NVL(iciloc.lOnhand,0000) AS lOnhand, ;
		NVL(iciloc.lOnhand-iciloc.lsoalloc,0000) AS AvailQty,;
		iciloc.Price,;
		iciloc.Price1,;
		iciloc.Price2,;
		icitem.MiscInfo, ;
		IIF(icitem.OEM,'Yes','No ') AS OEM,;
		iciloc.LocID ;
		FROM icitem ;
		LEFT OUTER JOIN iciloc ;
		ON icitem.ITEM = iciloc.ITEM ;
		WHERE iciloc.LocID = lcLocID ;
		AND (;
			icitem.item like lcStr OR ;
			icitem.itmdesc like lcStr OR ;
			prodline like lcStr OR ;
			itemclass like lcStr OR ;
			itemsubclass like lcStr OR ;
			ItemCategory like lcStr OR ;
			Manufacturer like lcStr OR ;
			Condition like lcStr OR ;
			ItemColor like lcStr OR ;
			MiscInfo like lcStr OR ;
			icitem.Barcode like lcstr OR ;
			icitem.item in (SELECT item FROM ICItemSKU WHERE SKU LIKE lcStr) ;
		 );
		ORDER BY icitem.ITEM,icitem.ProdLine,icitem.ItemClass

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[EzLogic]

After reading a bit and searching, I should start by:

index on Deleted() TAG DEL

on all my tables.

is this a good idea as well?

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[EzLogic]

What i was getting before i did anything (currently):

Rushmore optimization level for table icitem: none
Using index tag Locid to rushmore optimize table iciloc
Rushmore optimization level for table iciloc: partial
Rushmore optimization level for table icitemsku: none
Rushmore optimization level for table icitem: none
Rushmore optimization level for table icitemsku: none

I added index on deleted()
I added index on the MISCInfo field
I turned sys(3054) on and this is what i am getting now:

Using index tag Item to rushmore optimize table icitem
Using index tag Itmdesc to rushmore optimize table icitem
Using index tag Prodline to rushmore optimize table icitem
Using index tag Itemclass to rushmore optimize table icitem
Using index tag Itemsubcla to rushmore optimize table icitem
Using index tag Itemcatego to rushmore optimize table icitem
Using index tag Manufactur to rushmore optimize table icitem
Using index tag Condition to rushmore optimize table icitem
Using index tag Itemcolor to rushmore optimize table icitem
Using index tag Miscinfo to rushmore optimize table icitem
Using index tag Barcode to rushmore optimize table icitem
Using index tag Itemstatus to rushmore optimize table icitem
Using index tag Del to rushmore optimize table icitem
Rushmore optimization level for table icitem: partial
Using index tag Locid to rushmore optimize table iciloc
Using index tag Del to rushmore optimize table iciloc
Rushmore optimization level for table iciloc: full
Using index tag Del to rushmore optimize table icitemsku
Rushmore optimization level for table icitemsku: partial
Rushmore optimization level for table icitem: none

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Mike Lewis]

Hmm. Interesting question.

The one thing that hit me in the face was the lack of an index on iciloc.LocID.

The point is that you can't optimise any of the LIKE clauses. But you can optimise the iciloc.LocID = lcLocID clause, by creating an index on iciloc.LocID. At a first glance, that would make a big difference (especially if relatively few rows contain the specified LocID).

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Your SYS(3054) output seems to contradict your index specs (in your first post). Is there in fact an index on LocID?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[EzLogic]

Yes, sorry mike,.. i must have missed "typing" that I have DO indeed have an index on ICIloc.LocID

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

Aiming for rushmore optimization level: full is not necessarily the fastest. For example indexes on DELETED() only have a big impact on tables with a larger amount of deleted records. If you have SET DELETED ON there always is the filter on deletion mark, but that doesn't hurt, if there are no deleted records anyway.

LIKE expressions are not optimizable. That's the main point.
Besides that a subquery like icitem.item in (SELECT item FROM ICItemSKU WHERE SKU LIKE lcStr) is very bad for the performance.

There's nothing you can do about that. If you want faster fulltext search you better go MS SQL Server or MySQL. In VFP there was phDBase, but it's a deprecated product.

Also see here: thread184-1553613

The main idea also inclued in FoxWeb Fulltext search is extracting all single words (or word parts) of data into a new table and doing a regular index on that field. That of course is a prestep you need to do once with full data and the continue updating that index data every time you update the single tables, or your word/syllable index get's out of synch.

Bye, Olaf.

 

[EzLogic]

Olaf,

I think I got the speed to the best i can using optimization.

the subquery you said was bad:

like icitem.item in (select item form icitemsku where sku like lcstr)

what's a better way of putting that?



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Mike Lewis]

To avoid the subquery, you could inner join on the SKU table, adding SKU LIKE lcStr to the join condition.

But that wouldn't necessarily be any faster. It might, or it might not, depending on various conditions. The only way to know is to try it and see.

But the real problem is the multiple LIKE clauses. They will inevitably be inefficient, and the more you have, the worst the accumulated effect.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Like Mike says a Join would be better, but its not what you can do in this case, as an inner join is like an AND condition. You then would only have results, that have a SKU like lcStr in the icitemsku table.

I think it would give a better overall performance to split the query and work on accumulating partial results.

Bye, Olaf.

 

[Auguy]

Not sure if this would make any difference, but is there any way you can limit the number of "LIKE" expressions to reduce or eliminate the "OR" conditions. An example would be searching for a manufacturer name, I would doubt if the name would be in the item color field and you could limit the search to the Manufacturer field. Can you display a list of fields to check and let the user select the ones to search and build the select statement dynamically. Mike and Olaf can probably tell you if this would help with the speed.

Auguy
Sylvania/Toledo Ohio

 

[EzLogic]

Thanks Olaf and Mike,

I am going to try and see the results after I added the index on deleted(), which i didn't have, nor i had on the miscinfo.

then,

i am going to try to work with partial results as suggested.

Auguy,
I also, started on working on making the user select which "field(s)" he/she would like to search in..



Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[Olaf Doschke]

You can try DELETED, but I doubt it will speed up things. I know it only helps with data having a large amount of deleted records, I tested this a lot. It's a busted myth to me.

A more specific search will help the most. Especially if you remove the fields and conditions the user doesn't search.

Bye, Olaf.

 

[Mike Lewis]

I agree that an index on DELETED() probably won't make a big difference. It's worth trying, but it doesn't attack the fundamental problem, which is the multiplicity of LIKE clauses.

Having the user select the specific fields to search would certainly help. But you've got to weigh that against user convenience. It's nice to be able to have the user simply enter the partial search string (LG in this example), without having to worry about making any other choices.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Convenience over performance.

You can work this way. Google does and has perhaps developed the most inteligent textbox of the world.
Yet, if you only have a few fields to search in and a user knows he searches "LG" as part of a SKU number and even would laugh at a result having LG in any other field like vendor. That can easily happen of course. My experience with users is, they know more precisely what they want. It's not unconvenient to look for the SKU field of a specific search mask and enter LG there. And if you mean LG, the hardware vendor, you'd enter that in the vendor field of the search mask.

In fact the necessity of a single textbox search interface for a web search engine is quite simple: You don't know what a user wants to find, all you know is, it should be a relevant web page. And I guess no one here can imagine what intelligence is figuring out what is a good result, if you rather want to find code containing a certain command word or a text or some company/vendor or support for a certain router name etc. etc. etc., but Google is a large corporation having a tidbit more than a single personal computer behind that single textbox with the search button...

Bye, Olaf.

 

[brigmar]

I want to say to order the conditions by their probability of hit to make use of logic short-circuiting, but I can't say that the speed increase would be that noticeable.

 

[Olaf Doschke]

Brigmar,

That is perhaps the best idea. It could be noticable. OR conditions are optimized in not beeing computed, if a first condition already is met.

So searching the itemdesc first would perhaps find of of the searches in there and would skip all other LIKE conditions. That can speed up the query natably. But there are pros and cons for searching a long text/memo field first. If you don't find the search term in there you processed a lengthy text. Abit of logic would help, eg if you know a certain input can rather be found as in technical field, put that first. You could decide via numeric input (search in barcode first), uppercase (search in SKU first, if mainly just SKUs contain upper case letters) or normal text (search in description first), for example, which fields have a higher probability to find the search term, and hen rearrange the filter clause correspondingly.

I think that is a very good idea. Overall dynamically putting together the where clause would help anyway.

You could also introduce checkboxes for the user to specify in which fields to search his search term and introduce saving preferences as these.

Bye, Olaf.

 

[EzLogic]

Im going to do check boxes.
build query based on where user wishes to seach.

default is item and itmdesc.

great input.



Ali Koumaiha
TeknoSoft Inc.
Michigan