Hi,
I'm looking into serious performance issues that might happen in the sql
server
store when the database is deployed on a machine with a large amount of CPU
cores (in
this case, 128).
Basically, in those condition, sql server always chooses sequential scans
instead of
spatial index searches, and while it's possible to configure it to use only
1 core per query (which solves
the problem) it sort of defeats the purpose of having such a large
multicore machine.
Now, there are ways to force the index usage, with query hints, like this:
http://blog.sqlauthority.com/2009/02/08/sql-server-introduction-to-force-index-query-hints-index-hint-part2/
Something similar can be done for Oracle (just taking it into consideration
for the sake of generality):
http://support.esri.com/zh-cn/knowledgebase/techarticles/detail/32601
In one case the hint would have to be placed right after SELECT, in the
other either at the very end, or
right after the FROM <table>. Not sure what other databases might need, but
wondering if adding
the following extra method to the dialects would be suitable:
void handleSearchHints(StringBuffer sql, boolean isSqlView, Query query,
Connection cx)
and then let the dialect do some sql parsing of its own, and adapt the
request, in a similar fashion to what
is done for limit/offset.
Sql server wise, I'd go for adding a store parameter to enable the forcing
of spatial index usage, although having
a query hint would be interesting too, but more difficult to manage (having
to deal with a per query setting).
The above method uses Query to make it possible to add the per query hint
later down the road... or else
we could pass Filter and Hints (the filter is there to know whether there
is a spatial filter in the query...
one could also parse the sql, but I guess that would be harder to manage)
Cheers
Andrea
--
== Our support, Your Success! Visit http://opensdi.geo-solutions.it for
more information ==
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
-------------------------------------------------------
------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
GeoTools-Devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
