sql - should i create a vertical partition for geospatial data on a large table -

sql - should i create a vertical partition for geospatial data on a large table -

i have big table ( > 700,000 rows , growing) of contact info includes:

a polygon spatial index 1000 character text field full-text index about 25 other fields, of used searching (name, reference, id etc)

this table sees lots of queries on polygon field , lots of separate queries on free text field. (never both polygon , full-text in same query).

we have been suffering poor performance queries on table , considering creating 'vertical partition' set spatial info in new table , utilize view combine 2 application sees same info before.

will partitioning table increment performance of queries on full-text , spatial indexes if results require 2 tables joined?

short reply - yes.

after splitting table in two, 1 geometry seems geometry queries overall faster. performance of total text queries same.

i suspect because new table geometry info much smaller , queries on have fewer indexes take resulting in fewer execution plans.

but - querying view has geometry column in 1 of base of operations tables works - combinations of query area , spatial info density spatial index not selected , performance slow. cant pass index hint spatial index query on view ended querying base of operations tables of view straight in end forcefulness utilize of spatial index.

sql indexing sql-server-2012 geospatial partitioning