How to boost ISelect and IUpdate command performance

[Ray]

I need to use ISelect to do query for hundreds of thousand times. Every time the filter string is the same but the binding variable changes. See sample code below. I wonder how the performance can be maximized?
I know in Oracle I can use dynamic sql and binding variable to boost the performance. Is there similar thing we can do to boost performance here in FDO?

Dim fdoCmd As ISelect = fdoConn.CreateCommand(CommandType.CommandType_Select)
Dim fdoReader As OSGeo.FDO.Commands.Feature.IFeatureReader = Nothing
Dim filterString As String = String.Format("( UFID = {0} )", ufid)
fdoCmd.SetFilter(filterString)
fdoCmd.Prepare()
fdoReader = swicSelCmd.Execute()

How about IUpdate command?

[Romica Dascalescu]

Try using parameter values...

     FdoPtr<FdoISelect> selectCmd = (FdoISelect*)conn->CreateCommand(FdoCommandType_Select);
     selectCmd->SetFeatureClassName(L"MyClass");
     FdoPtr<FdoFilter> filter = FdoFilter::Parse(L"ID < :parm1");
     selectCmd->SetFilter(filter);
     FdoPtr<FdoParameterValueCollection>parms = selectCmd->GetParameterValues();
     FdoPtr<FdoInt32Value> intval = FdoInt32Value::Create(30);
     FdoPtr<FdoParameterValue>parm = FdoParameterValue::Create(L"parm1",intval);
     parms->Add(parm);

{// first execute using 30
     FdoPtr<FdoIFeatureReader>reader = selectCmd->Execute();
}
intval->SetInt32(44);
{// secon execute using 44
     FdoPtr<FdoIFeatureReader>reader = selectCmd->Execute();
}

Regards,
Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Ray [[hidden email]]
Sent: Tuesday, November 30, 2010 9:59 AM
To: [hidden email]
Subject: [fdo-users] How to boost ISelect and IUpdate command performance

I need to use ISelect to do query for hundreds of thousand times. Every time
the filter string is the same but the binding variable changes. See sample
code below. I wonder how the performance can be maximized?
I know in Oracle I can use dynamic sql and binding variable to boost the
performance. Is there similar thing we can do to boost performance here in
FDO?

Dim fdoCmd As ISelect =
fdoConn.CreateCommand(CommandType.CommandType_Select)
Dim fdoReader As OSGeo.FDO.Commands.Feature.IFeatureReader = Nothing
Dim filterString As String = String.Format("( UFID = {0} )", ufid)
fdoCmd.SetFilter(filterString)
fdoCmd.Prepare()
fdoReader = swicSelCmd.Execute()

How about IUpdate command?
--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-performance-tp5788379p5788379.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[djonio]

Maybe I don't understand fully the need to do a one-at-a-time query vs.
a set-at-a-time. Does your provider support "IN"? (UFID IN
(XXXX,XXXX,XXXX)) or even (UFID = {0} OR UFID = {1}).

It would seem to me that iterating a reader, even multiple times, would
be significantly faster than hundreds of thousands of queries.
r,
dennis
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Traian Stanev]

In cases when you want to access the features in a certain order.

The solution I've used in the past, and is slightly simpler (but equivalent) than the one Romy posted:

FdoPtr<FdoIdentifier> idid = FdoIdentifier::Create(idname);
FdoPtr<FdoInt32Value> idval = FdoInt32Value::Create(0);
FdoPtr<FdoFilter> fidfilter = FdoComparisonCondition::Create(idid, FdoComparisonOperations_EqualTo, idval);
select->SetFilter(fidfilter);

for (...)
{
   idval->SetInt32(some_other_id); //update the filter with a new ID value
   FdoPtr<FdoIFeatureReader> rdr = select->Execute(); //execute the select again
   rdr->ReadNext(); //read the result
   ....
}

It skips the use of a parameter value. However, I've only tried this to work with SQLite, SDF and SHP. I don't know if there are other providers which require the use of a parameter value in between the comparison condition and the integer value. For the wider context of this example check SQLiteConverter.cpp in the SQLite provider sources.

For the update command, you can reuse a single IUpdate object and pre-populate the PropertyValueCollection of the IUpdate and on each iteration update the property values only. However, the speed gain of this with updates will not be huge.

Traian

> -----Original Message-----
> From: [hidden email] [mailto:fdo-users-
> [hidden email]] On Behalf Of Jonio, Dennis (Aviation)
> Sent: Tuesday, November 30, 2010 10:55 AM
> To: FDO Users Mail List
> Subject: RE: [fdo-users] How to boost ISelect and IUpdate command
> performance
>
> Maybe I don't understand fully the need to do a one-at-a-time query vs.
> a set-at-a-time. Does your provider support "IN"? (UFID IN
> (XXXX,XXXX,XXXX)) or even (UFID = {0} OR UFID = {1}).
>
> It would seem to me that iterating a reader, even multiple times, would
> be significantly faster than hundreds of thousands of queries.
> r,
> dennis
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Ray]

I tried both Romy and Traian's solution.

Romy's solution doesn't work. The SelectCmd.ParameterValues returns me nothing and it is read only. I guess autodesk fdo for Oracle doesn't support this.

Taian's solution works. However, it doesn't have any performance gain. It has exactly the same performace as the filter string is bound to selectCmd in every call.

[Martin Morrison]

This is the basics, but you have created an index on the field where the parameter is haven't you?

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Ray
Sent: Tuesday, November 30, 2010 3:15 PM
To: [hidden email]
Subject: [fdo-users] RE: How to boost ISelect and IUpdate command performance


I tried both Romy and Traian's solution.

Romy's solution doesn't work. The SelectCmd.ParameterValues returns me
nothing and it is read only. I guess autodesk fdo for Oracle doesn't support
this.

Taian's solution works. However, it doesn't have any performance gain. It
has exactly the same performace as the filter string is bound to selectCmd
in every call.




--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-performance-tp5788379p5789506.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Traian Stanev]

If there is no performance gain from this and your performance is still slow, it means that you have a bigger bottleneck somewhere in the pipeline. It would help if you gave more information about what type of data source you are using and what your performance expectation is -- e.g. it would be unrealistic to expect 10 million queries per second with, say, WFS data.

Traian

> -----Original Message-----
> From: [hidden email] [mailto:fdo-users-
> [hidden email]] On Behalf Of Ray
> Sent: Tuesday, November 30, 2010 3:15 PM
> To: [hidden email]
> Subject: [fdo-users] RE: How to boost ISelect and IUpdate command
> performance
>
>
> I tried both Romy and Traian's solution.
>
> Romy's solution doesn't work. The SelectCmd.ParameterValues returns me
> nothing and it is read only. I guess autodesk fdo for Oracle doesn't
> support
> this.
>
> Taian's solution works. However, it doesn't have any performance gain.
> It
> has exactly the same performace as the filter string is bound to
> selectCmd
> in every call.
>
>
>
>
> --
> View this message in context: http://osgeo-
> org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-
> performance-tp5788379p5789506.html
> Sent from the FDO Users mailing list archive at Nabble.com.
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Ray]

Sure. There is index created. That is basics.

[Martin Morrison]

Just checking...

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Ray
Sent: Tuesday, November 30, 2010 3:50 PM
To: [hidden email]
Subject: [fdo-users] RE: How to boost ISelect and IUpdate command performance


Sure. There is index created. That is basics.
--
View this message in context: http://osgeo-org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-performance-tp5788379p5789609.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Ray]

The data source is Oracle.

My intention was to avoid sql statement re-parsing by using appropriate FDO api. I wonder if both the following approaches (#1 and #2) would achieve the same?

#1: (bind the string filter but supply different ufid for each query)
Dim filterString As String = String.Format("( UFID = {0} )", ufid)
selCmd.SetFilter(filterString)

#2: (use the same CamparisonCondition but set different ufid)
Dim idname As Identifier = New Identifier("UFID")
Dim val As Int32Value = New Int32Value(ufid)
Dim cc As ComparisonCondition = New ComparisonCondition(idname, ComparisonOperations.ComparisonOperations_EqualTo, val)
selCmd.Filter = cc

[Traian Stanev]

I don't know enough about how the Oracle FDO provider handles queries internally, but I imagine that the way I proposed will not result in much optimization with Oracle as it would still have to translate the filter to some sort of Oracle query every time anyway, regardless of the input FdoFilter being cached.

So you may have to use some other kind of parameter binding, hopefully someone else on the list can help with that.

Traian

> -----Original Message-----
> From: [hidden email] [mailto:fdo-users-
> [hidden email]] On Behalf Of Ray
> Sent: Tuesday, November 30, 2010 4:09 PM
> To: [hidden email]
> Subject: [fdo-users] RE: How to boost ISelect and IUpdate command
> performance
>
>
> The data source is Oracle.
>
> My intention was to avoid sql statement re-parsing by using appropriate
> FDO
> api. I wonder if both the following approaches (#1 and #2) would
> achieve the
> same?
>
> #1: (bind the string filter but supply different ufid for each query)
> Dim filterString As String = String.Format("( UFID = {0} )", ufid)
> selCmd.SetFilter(filterString)
>
> #2: (use the same CamparisonCondition but set different ufid)
> Dim idname As Identifier = New Identifier("UFID")
> Dim val As Int32Value = New Int32Value(ufid)
> Dim cc As ComparisonCondition = New ComparisonCondition(idname,
> ComparisonOperations.ComparisonOperations_EqualTo, val)
> selCmd.Filter = cc
>
> --
> View this message in context: http://osgeo-
> org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-
> performance-tp5788379p5789649.html
> Sent from the FDO Users mailing list archive at Nabble.com.
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users

[Dan Stoica]

I think is fair to ask what kind of performance is expected? And compared to what exactly? e.g. when I worked on improving "Select *" performance I compared the results to an OCI standalone application.

Dan.

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Traian Stanev
Sent: Tuesday, November 30, 2010 5:42 PM
To: '[hidden email]'
Subject: RE: [fdo-users] RE: How to boost ISelect and IUpdate command performance


I don't know enough about how the Oracle FDO provider handles queries internally, but I imagine that the way I proposed will not result in much optimization with Oracle as it would still have to translate the filter to some sort of Oracle query every time anyway, regardless of the input FdoFilter being cached.

So you may have to use some other kind of parameter binding, hopefully someone else on the list can help with that.

Traian

> -----Original Message-----
> From: [hidden email] [mailto:fdo-users-
> [hidden email]] On Behalf Of Ray
> Sent: Tuesday, November 30, 2010 4:09 PM
> To: [hidden email]
> Subject: [fdo-users] RE: How to boost ISelect and IUpdate command
> performance
>
>
> The data source is Oracle.
>
> My intention was to avoid sql statement re-parsing by using appropriate
> FDO
> api. I wonder if both the following approaches (#1 and #2) would
> achieve the
> same?
>
> #1: (bind the string filter but supply different ufid for each query)
> Dim filterString As String = String.Format("( UFID = {0} )", ufid)
> selCmd.SetFilter(filterString)
>
> #2: (use the same CamparisonCondition but set different ufid)
> Dim idname As Identifier = New Identifier("UFID")
> Dim val As Int32Value = New Int32Value(ufid)
> Dim cc As ComparisonCondition = New ComparisonCondition(idname,
> ComparisonOperations.ComparisonOperations_EqualTo, val)
> selCmd.Filter = cc
>
> --
> View this message in context: http://osgeo-
> org.1803224.n2.nabble.com/How-to-boost-ISelect-and-IUpdate-command-
> performance-tp5788379p5789649.html
> Sent from the FDO Users mailing list archive at Nabble.com.
> _______________________________________________
> fdo-users mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/fdo-users

_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users