52 reputation
5
bio website
location Peterborough, United Kingdom
age 45
visits member for 2 years, 11 months
seen Apr 9 at 13:05

Sep
20
awarded  Commentator
Sep
20
comment SQL Server WHERE clause on CLR method (spatial) peformance
Unfortunately, the query was still very slow using TOP so I bit the bullet and re-factored. I selected all the data for the reportGUID into a table along with STGeometryType() stored as a string. Then I queried this table based on the string's value. I'm still interested to see if the performance can be improved directly in the query, though, so will keep the question open and investigate a bit further.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Thanks for the advice. The query runs pretty quickly without that final part of the WHERE clause. Unfortunately, the value - geom.STGeometryType() - is a call into the .NET CLR, which I've assumed has some overhead and is causing the poor performance when it is called over the whole locations table unnecessarily. However, I wasn't aware that TOP had this effect and that may just solve the problem. Thank you.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Yes. This table holds the output of reports generated by users of the application. The GUID identifies a single report (usually in the region of 5K - 25K records).The SQL above selects the records for the report and subsequent .NET code exports them as an ESRI shapefile. There are similar queries to export Lines and Polygons, since ESRI shapefiles only support single geometry types.
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Thanks. Wasn't aware of that article.
Sep
19
revised SQL Server WHERE clause on CLR method (spatial) peformance
added 106 characters in body
Sep
19
revised SQL Server WHERE clause on CLR method (spatial) peformance
added 121 characters in body
Sep
19
comment SQL Server WHERE clause on CLR method (spatial) peformance
Okay. How is it best to do that? As an image (it's quite large ...)?
Sep
19
asked SQL Server WHERE clause on CLR method (spatial) peformance
Nov
16
awarded  Editor
Nov
16
comment Selecting values that are not associated with one side of a many to many relationship
Thanks. Fixed it.
Nov
16
revised Selecting values that are not associated with one side of a many to many relationship
Fixed typo
Nov
16
accepted Selecting values that are not associated with one side of a many to many relationship
Nov
16
comment Selecting values that are not associated with one side of a many to many relationship
Fantastic. Thank you very much.
Nov
16
asked Selecting values that are not associated with one side of a many to many relationship
Jun
27
awarded  Supporter
Jun
27
accepted What does CREATE ASSEMBLY do with the registered dll?
Jun
27
comment What does CREATE ASSEMBLY do with the registered dll?
Thanks for the tip.
Jun
27
comment What does CREATE ASSEMBLY do with the registered dll?
Thanks very much. That makes a lot of sense. I scripted the assembly and did see a long binary value like this: CREATE ASSEMBLY [AlphaShapes] AUTHORIZATION [dbo] FROM 0x4D5A900003000000 ... WITH PERMISSION_SET = SAFE It was about 19000 characters long, which sounds roughly right for a 10K dll.
Jun
27
asked What does CREATE ASSEMBLY do with the registered dll?