locked
Performance problems with View using Geometry column and geometry.STSrid and geometry.STAsText functions RRS feed

  • Question

  • Hi all,

     

    I have a simple View with a JOIN like:

     

     

    create view [dbo].[VTRAFO_GEO_PERDAS] as
    
    	select BARRA.geometry,trafo.MES_REF
    
    		from trafo
    
             	join  BARRA on (BARRA.ID_BARRA=trafo.id_barra and trafo.mes_ref = BARRA.MES_REF)
    

     

     

    the pk of table BARRA is ID_BARRA and MES_REF and has a index in trafo table with (mes_ref and id_barra) columns

    When i execute this:

     

     

    SELECT *
    
    FROM "VTRAFO_GEO_PERDAS" 
    
    WHERE "geometry".Filter(geometry::STGeomFromText('POLYGON ((-44.02333842086841 -19.812345745086414, -44.02333842086841 -19.799454547882583, -44.010447223664585 -19.799454547882583, -44.010447223664585 -19.812345745086414, -44.02333842086841 -19.812345745086414))', 4326)) = 1
    

     

     

    Query spends less the one second but the query below:

     

     

    SELECT CAST("geometry".STSrid as VARCHAR) + ':' + "geometry".STAsText() as "geometry" 
    
    FROM "VTRAFO_GEO_PERDAS" 
    
    WHERE "geometry".Filter(geometry::STGeomFromText('POLYGON ((-44.02333842086841 -19.812345745086414, -44.02333842086841 -19.799454547882583, -44.010447223664585 -19.799454547882583, -44.010447223664585 -19.812345745086414, -44.02333842086841 -19.812345745086414))', 4326)) = 1
    

     

     

    spends more then 15 seconds to run, why ? has problems with this functions use in Views ?

    I try remove the cast using only geometry.STSrid in query and the time to run is the same.

    In my tests I see only problems when I use this JOIN, the same query direct on table BARRA is fine and runs fast but in view when use functions of geometry column STSrid and STAsText query runs very slowly.

    Can be a problem of SQLServer2008 ?

    I use is: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 6.0 <X86> (Build 6002: Service Pack 2) (VM) 

    Its necessary in my case the view to get geometry by JOIN to use in my GIS client, if i copy data from table barra to table trafo the performance is fine but this is not possible in my case.

    Thanks a lot for any help.

    ------------------------------------------------------------------------------------------------------------------------------------------------------

     

    Investigating I found a problem with execution plan of SQLServer using JOIN and Geometry column.

    trying explain.

    The simple query:

     

    select x.geometry.STAsText() from barra x
    where 
    x."geometry".Filter(geometry::STGeomFromText('POLYGON ((-44.036031291961415 -19.825038616179416, -44.036031291961415 -19.812147418975584, -44.023140094757586 -19.812147418975584, -44.023140094757586 -19.825038616179416, -44.036031291961415 -19.825038616179416))', 4326)) = 1

     

    This query runs fine and very fast, run´s in mileseconds.

    The next query:

     

    select barra."geometry"
    	from trafo
    	join BARRA on (barra.ID_BARRA = trafo.ID_BARRA and trafo.mes_ref = BARRA.MES_REF)
    		WHERE 
    		barra."geometry".Filter(geometry::STGeomFromText('POLYGON ((-44.036031291961415 -19.825038616179416, -44.036031291961415 -19.812147418975584, -44.023140094757586 -19.812147418975584, -44.023140094757586 -19.825038616179416, -44.036031291961415 -19.825038616179416))', 4326)) = 1

    This query runs fine and very fast, run´s in mileseconds too.

    The next query:

     

     

    select barra."geometry".STAsText()
    	from trafo
    	join BARRA on (barra.ID_BARRA = trafo.ID_BARRA and trafo.mes_ref = BARRA.MES_REF)
    		WHERE 
    		barra."geometry".Filter(geometry::STGeomFromText('POLYGON ((-44.036031291961415 -19.825038616179416, -44.036031291961415 -19.812147418975584, -44.023140094757586 -19.812147418975584, -44.023140094757586 -19.825038616179416, -44.036031291961415 -19.825038616179416))', 4326)) = 1
    Booom.... 
    This query is very very slow and runs in 25 seconds, the call to STAsText() makes the query very slow.
    I don´t undestand because if the first query is very fast, the second query with join is very fast but the third query using STAsText is very slow.
    All indexes is OK the problem is in STAsText and STSrid of geometry column.
    Any help is very apreciated.
    Carlos

     

     

     

     

     

     

     



    Friday, May 6, 2011 12:19 AM

All replies

  • Hi Carlos.Lacerda, 

    Thank you for your question.  

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

    Thank you for your understanding and support. 

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Monday, May 9, 2011 10:07 AM
  • I am not exactly sure I understand the situation as described, but here is an idea - does the STAsText get somehow invoked on ALL records prior to the joins/where clause narrowing the result set?

    Perhaps try a trick using the primary key of the desired records would indicate if this is the case?  There could be several variations of this technique.

    PSEUDO CODE - but you will get the idea,  inner query is your fast one and identifies the "keeper" records, only records with those ids are converted to WKT in outer.

    SELECT g.<GET_WKT_OF_GEOM>               
    FROM <SOME_TABLE> as g INNER JOIN
    (SELECT <PK_COLUMN_OF_DESIRED_RECORDS>
     FROM <SOME_TABLES_WITH_SOME_JOINS>
     WHERE <SOME_WHERE_CLAUSE>) as FastQuery
     ON g.<PK> = FastQuery.<PK>

    or possibly

    SELECT g.<GET_WKT_OF_GEOM>               
    FROM <SOME_TABLE> as g
    WHERE g.<PK_COLUMN> IN
    (SELECT <PK_COLUMN_OF_DESIRED_RECORDS>
     FROM <SOME_TABLES_WITH_SOME_JOINS>
     WHERE <SOME_WHERE_CLAUSE>)

     

    Maybe a more fundamental question is if you are required to have WKT would it make sense to create an additional column that contains the WKT version of your geometry?  Populate the column at insert/update time and then just read its contents as needed?  May not be a "best practice", but could be an option in some cases...

     

     

    Monday, May 9, 2011 1:57 PM
  • Exactly wimpy, this is my conclusion also, STAsText is called on all records before JOIN.

    I already try use subquery to reduce/filter results but without success, query remain very slow.

    The second solution can solve the problem, but at this time I have another app using this functions STAsText and STSrid and changes cannot be made at this time because is internaly in driver of application to access SQLServer.

    Instead of create aditional column to save values of WKT and SRID, I create a column on table of JOIN and copy the geometry column as a workaround temporarly.

     

    Thanks a lot for the tips.

     

     

     


    Monday, May 9, 2011 2:29 PM
  • Hello ,

    Can you try following.
     1. Apply SP2 for SQL Server 2008.
     2. Update the stats with fullscan on all bases tables.
     3. Test the query again, if the issue still exist can you provide scripts to repro the issue.

    Thank You

    Jayaprakash JO - MSFT

    • Proposed as answer by WeiLin Qiao Monday, May 16, 2011 1:26 AM
    Thursday, May 12, 2011 2:15 PM
    Answerer
  • Thanks for reply, i try this today and return with results.

    Thursday, May 12, 2011 2:23 PM
  • I apply SP2 in database and execute tests again but performance still bad. 

    I´m loading data again now for tests of my app, after complete I will update stats in all tables and try again.

    I will return with results.

    Monday, May 16, 2011 4:10 PM
  • Hi Jayaprakash ,

    I run the queries after apply SP2 and update stats with fullscan but the performance problem persist, if don´t call functions STAsText and STSrid the query return with less 1 second, if use this functions the time is 7-8 seconds and processor of machine is in 100% while running the query.

    As a workarond at this time, I copy geometries from table of JOIN to solve my problem.

     

    I will send a script to repro this issue comming soon.

     

    Thanks a lot.

     

    Monday, May 16, 2011 6:49 PM