Bug report #8525

MSSQL spatial layer bug with estimated metadata

Added by Mike Gates over 11 years ago. Updated over 10 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:17281

Description

It looks like there is a bug when connecting to MSSQL databases to add a spatial layer when using estimated table parameters.

Connecting one time works fine and all the spatial enabled tables are listed in a very short period of time.

However, after adding a spatial layer using this method, going back and adding a second spatial layer causes the application to submit a query to the database which doesn't take into account the "use estimated table parameters" setting...it ends up querying the whole table, which for a large table can take quite a bit of time.

Taking a glance at the source code, it appears to be a problem with the mUseEstimatedMetadata variable being set incorrectly after adding a spatial layer.

This is a problem in both Lisboa and the current weekly build.

Thanks,
Mike

geom.png (10.7 KB) Nathan Woodrow, 2013-09-01 01:35 AM

History

#1 Updated by Nathan Woodrow over 11 years ago

Do you have a geometry_columns table in your database?

#2 Updated by Mike Gates over 11 years ago

Nathan Woodrow wrote:

Do you have a geometry_columns table in your database?

No, I don't.

#3 Updated by Nathan Woodrow over 11 years ago

Adding one of those will help with the table listing times in the dialog. Once you have one it will list all the tables instantly. It has the same scheme as the postgis one and you just need to have a row for each layer/table to tell QGIS what the geometry type and srid is.

#4 Updated by Mike Gates over 11 years ago

Woodrow wrote:

Adding one of those will help with the table listing times in the dialog. Once you have one it will list all the tables instantly. It has the same scheme as the postgis one and you just need to have a row for each layer/table to tell QGIS what the geometry type and srid is.

I'm not familiar with postgis or this table. could you point me to a table schema I could replicate? The top 100 method works great for this purpose, when it does work.

#5 Updated by Nathan Woodrow over 11 years ago

Here is the create script for it:

code>
CREATE TABLE [dbo].[geometry_columns](
    [f_table_catalog] [varchar](128) NOT NULL,
    [f_table_schema] [varchar](128) NOT NULL,
    [f_table_name] [varchar](256) NOT NULL,
    [f_geometry_column] [varchar](256) NOT NULL,
    [coord_dimension] [int] NOT NULL,
    [srid] [int] NOT NULL,
    [geometry_type] [varchar](30) NOT NULL,
 CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED 
(
    [f_table_catalog] ASC,
    [f_table_schema] ASC,
    [f_table_name] ASC,
    [f_geometry_column] ASC
)

and this is example of some records:

#6 Updated by Mike Gates over 11 years ago

That's great. That will be useful until the bug gets fixed.

Thanks, Nathan.

#7 Updated by Nathan Woodrow over 11 years ago

I would use it even if the estimated bug is fixed. It is much faster then QGIS having to check the type each time you open the dialog. Unless you have a rapid changing database it's pretty easy to maintain geometry_columns.

#8 Updated by Mike Gates over 11 years ago

Nathan Woodrow wrote:

I would use it even if the estimated bug is fixed. It is much faster then QGIS having to check the type each time you open the dialog. Unless you have a rapid changing database it's pretty easy to maintain geometry_columns.

I hear what you're saying about the performance. The problem is that we do have multiple databases with changing schemas (new tables show up often). Putting a qgis geometry table in each of these databases would require a certain amount of maintenance and can appear to be clutter to the non-GIS users of the database. Not the end of the world, but having a dynamic system that doesn't require an extra table in every database would be a lot more transparent IMO.

#9 Updated by Nathan Woodrow over 10 years ago

  • Resolution set to fixed/implemented
  • Category set to Data Provider/MSSQL
  • Status changed from Open to Closed

I have tweaked the estimated table search to make it faster and more correct. Load times should be a lot quicker now for a database without geometry_columns. Give it a try and reopen if needed.

Also available in: Atom PDF