# Search speed issue

**URL:** <https://mapwindow.discourse.group/t/search-speed-issue/143>\
**Category:** MapWinGIS\
**Created:** [April 18, 2019, 5:49pm UTC](https://mapwindow.discourse.group/t/search-speed-issue/143 "2019-04-18T17:49:53Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jonwinchester](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jonwinchester](https://mapwindow.discourse.group/u/jonwinchester)\
**Post date:** [April 18, 2019, 5:49pm UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/1 "2019-04-18T17:49:54Z")

</div>

I stumbled onto this situation. I am searching a layer for address like this:

```
    srch = "[NAME] = """ & strt & """"
    srch &= " and ("
    srch &= " ([FROMLEFT]<=" & addrnum & " and [TOLEFT]>=" & addrnum & ")"
    srch &= " or ([FROMRIGHT]<=" & addrnum & " and [TORIGHT]>=" & addrnum & ")"
    srch &= ")"
    srch &= " and ("
    srch &= "[ZIP_LEFT] = """ & zipin & """"
    srch &= " or "
    srch &= "[ZIP_RIGHT] = """ & zipin & """"
    srch &= ")"

    sf.Table.Query(srch, res, errstr)

```

At the top of the class, I tried two ways to get an instance of the layer:

```
    'This way, exporting to make a copy results in very fast search - 17 seconds to look up 137 addresses
    Dim sf_temp As MapWinGIS.Shapefile
    sf_temp = Form1.Map1.get_GetObject(Form1.Handle_RoadsLayer) ' this gets layer for searching
    sf_temp.SelectAll()
    sf = sf_temp.ExportSelection

    ' this is the alternative to the above creation of temp layer 
    ' runs much to geocode 137 points - 2min 13 sec
    sf = Form1.Map1.get_GetObject(Form1.Handle_RoadsLayer) ' this gets layer for searching

```

It is dramatically slower searching to do the second way. Any thoughts?

---

<div class="post-metadata">

**Author:** ![pmeems](https://yyz2.discourse-cdn.com/free1/user_avatar/mapwindow.discourse.group/pmeems/32/9_2.png) [@pmeems](https://mapwindow.discourse.group/u/pmeems)\
**Post date:** [April 25, 2019, 12:05pm UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/2 "2019-04-25T12:05:27Z")

</div>

This is interesting and an unexpected result.  
Can you share your shapefile? So we can try to debug.

---

<div class="post-metadata">

**Author:** ![jonwinchester](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jonwinchester](https://mapwindow.discourse.group/u/jonwinchester)\
**Post date:** [April 25, 2019, 3:32pm UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/3 "2019-04-25T15:32:25Z")

</div>

Here is the shapefile.

[RD12115.zip](https://global.discourse-cdn.com/free1/uploads/mapwindow1/original/1X/f5bc61a39fe0ad27900ab6b113e2b63ade6ee614.zip) (3.5 MB)

---

<div class="post-metadata">

**Author:** ![pmeems](https://yyz2.discourse-cdn.com/free1/user_avatar/mapwindow.discourse.group/pmeems/32/9_2.png) [@pmeems](https://mapwindow.discourse.group/u/pmeems)\
**Post date:** [May 3, 2019, 9:49am UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/4 "2019-05-03T09:49:19Z")

</div>

I made a unit test to quickly test some variations and I also get different timing.  
I need to debug this more.

Here’s my unit test:

```
var sf = Helper.OpenShapefile("RD12115.shp", true, this);

const string query = @"[NAME] =""la France Ave"" and (([FROMLEFT]<=4201 and [TOLEFT]>=4499) or ([FROMRIGHT]<=4200 and [TORIGHT]>=4498)) and ([ZIP_LEFT] = ""34286"" or [ZIP_RIGHT] = ""34286"")";
Debug.WriteLine(query);
object result = null;
string errorString = null;

var stopWatch = new Stopwatch();
stopWatch.Start();
Assert.IsTrue(sf.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query filebased shapefile: " + stopWatch.Elapsed);

Assert.IsNotNull(result);
var indexes = result as int[];
Assert.IsNotNull(indexes);
Assert.IsTrue(indexes.Length > 0, "No results found");

// Make memory copy:
sf.SelectAll();
var sfTemp = sf.ExportSelection();
sf.SelectNone();

stopWatch.Restart();
Assert.IsTrue(sfTemp.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query memory shapefile: " + stopWatch.Elapsed);
var fileLocation = Path.Combine(Path.GetTempPath(), "speed.shp");
Helper.SaveAsShapefile(sfTemp, fileLocation);

// Again after saving
stopWatch.Restart();
Assert.IsTrue(sfTemp.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query memory shapefile after saving: " + stopWatch.Elapsed);
sfTemp.Close();

// Use QTree
sf.UseQTree = true;
stopWatch.Restart();
Assert.IsTrue(sf.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query shapefile with QTree index: " + stopWatch.Elapsed);

// Use spatial index:
Assert.IsTrue(sf.CreateSpatialIndex(sf.Filename), "Cannot create spatial index");
stopWatch.Restart();
Assert.IsTrue(sf.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query shapefile with spatial index: " + stopWatch.Elapsed);

// Open temp shapefile:
var sfTemp2 = Helper.OpenShapefile(fileLocation);
sfTemp2.UseQTree = true;
stopWatch.Restart();
Assert.IsTrue(sfTemp2.Table.Query(query, ref result, ref errorString), "Table.Query: " + errorString);
stopWatch.Stop();
Debug.WriteLine("Time it took to query temp shapefile with QTree index after loading from disk: " + stopWatch.Elapsed);

```

The Helper class is in [Github](https://raw.githubusercontent.com/MapWindow/MapWinGIS/master/unittests/MapWinGISTests/Helper.cs).

The results of my test:

```
Time it took to query filebased shapefile: 00:00:01.2651178
Time it took to query memory shapefile: 00:00:00.2310401
Time it took to query memory shapefile after saving: 00:00:00.6871266
Time it took to query shapefile with QTree index: 00:00:00.7006017
Time it took to query shapefile with spatial index: 00:00:00.7213261
Time it took to query temp shapefile with QTree index after loading from disk: 00:00:01.2230481
```

---

<div class="post-metadata">

**Author:** ![pmeems](https://yyz2.discourse-cdn.com/free1/user_avatar/mapwindow.discourse.group/pmeems/32/9_2.png) [@pmeems](https://mapwindow.discourse.group/u/pmeems)\
**Post date:** [May 9, 2019, 3:15pm UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/5 "2019-05-09T15:15:48Z")

</div>

I updated the test a bit and noticed that querying the first time a file-based shapefile is always slower than the second time. So some caching must be involved.  
The in-memory shapefile stays much faster, but has no difference in the first time or the second time, assuming it is already fully optimized:

```
Time it took to query filebased shapefile: 00:00:01.1245868
Time it took to query filebased shapefile second time: 00:00:00.6824004

Time it took to ExportSelection: 00:00:01.2607499

Time it took to query memory shapefile: 00:00:00.2366985
Time it took to query memory shapefile second time: 00:00:00.2387093

Time it took to query memory shapefile after saving: 00:00:00.6258544
Time it took to query memory shapefile after saving second time: 00:00:00.2375523

Time it took to query shapefile with QTree index: 00:00:00.6741876
Time it took to query shapefile with QTree index second time: 00:00:00.6730707

Time it took to query shapefile with spatial index: 00:00:00.6897191
Time it took to query shapefile with spatial index second time: 00:00:00.6699897

Time it took to open shapefile: 00:00:00.0165911
Time it took to query temp shapefile with QTree index after loading from disk: 00:00:01.1004431
Time it took to query temp shapefile with QTree index after loading from disk second time: 00:00:00.6669803

```

A file-based spatial index or a QTree index has no effect, which makes sense because they both are spatial-based and are not indexing attributes.

I tried debugging MapWinGIS to see what is the difference between the file-based shapefile and the in-memory version but couldn’t find anything. Most likely because I’m not a good C++ debugger 😉

@jerryfaust When you have some time, could you have a look at this?  
I also created [https://mapwindow.atlassian.net/browse/MWGIS-166](https://mapwindow.atlassian.net/browse/MWGIS-166) for this.

---

<div class="post-metadata">

**Author:** ![pmeems](https://yyz2.discourse-cdn.com/free1/user_avatar/mapwindow.discourse.group/pmeems/32/9_2.png) [@pmeems](https://mapwindow.discourse.group/u/pmeems)\
**Post date:** [July 3, 2019, 7:30am UTC](https://mapwindow.discourse.group/t/search-speed-issue/143/6 "2019-07-03T07:30:38Z")

</div>