.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Large geo database (not spatial), index problems

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server



I am have trouble getting my database selects fast. The SQL force is not strong with me.

The data is provided from geonames.org and contains about 8 mio locations.

The main problem is my ORDER BY population DESC. When covering a large location, where there are lots of populated places (pp), its fast, but when covering the sea, 0 pp, its really really slow.


Here is the table:

CREATE TABLE [dbo].[GeoNames](
  [geonameid] [int] NOT NULL,
  [name] [nvarchar](200) NULL,
  [asciiname] [nvarchar](200) NULL,
  [alternatenames] [nvarchar](max) NULL,
  [latitude] [float] NULL,
  [longitude] [float] NULL,
  [feature_class] [char](2) NULL,
  [feature_code] [nvarchar](10) NULL,
  [country_code] [char](3) NULL,
  [cc2] [char](60) NULL,
  [admin1_code] [nvarchar](20) NULL,
  [admin2_code] [nvarchar](80) NULL,
  [admin3_code] [nvarchar](20) NULL,
  [admin4_code] [nvarchar](20) NULL,
  [population] [bigint] NULL,
  [elevation] [int] NULL,
  [gtopo30] [int] NULL,
  [timezone] [char](31) NULL,
  [modification_date] [datetime] NULL


My clustered index:

CREATE UNIQUE CLUSTERED INDEX [pk_lat_lng_id] ON [dbo].[GeoNames]
  [latitude] ASC,
  [longitude] ASC,
  [feature_class] ASC,
  [geonameid] ASC,
  [population] DESC

View Complete Post

More Related Resource Links

Persist large dataset across ASP.NET pages. No database


Can anyone give me suggestions on how to implement the following requirement?

1. A online wizard (step-by step operation) processes user requests across several ASP.NET pages.  The session data is stored in memory until the user submits the request.  Once the request is submitted, a XML file is created and the data are passed to XML.

# Case Study: The parent page has a "select" button.  Click "select" will pop up a child page.  The users can select 1000+ items from a datagrid in the child page.  Once the user clicks "confirm", the selected items in the child page will be populated to the selected item gridveiw in the parent page.  The user can keep selecting more in the child page and the selected items will be appended to the gridview in the parent page.


- What's the best way to extract strings from a text file and convert to a dataset in memory and then present it in a datagrid in UI?

- What's the best way to persist and manipulate a large dataset across pages?  Session? or ViewState? Can it be accomplished by LINQ?

The app doesn't interact with a DB.  Once the request is submitted, it will convert the in memory data to a XML file.  What technology can be used to accomplished this?&nb

Remote Data Access encountered problems when opening the database.

Hello,   I have some toubles with SQLCERemoteDataAccess   The exceptions are thrown on rda.Pull("Article", "Select * from Article", _ rdaOleDbConnectString, _ RdaTrackOption.TrackingOnWithIndexes, _ "ErrorTable")   with follow parameters : rdaOleDbConnectString = "Data Source=MyServeur;Initial Catalog=BddClient;User Id=admin;Password=admin;" InternetURLString = ""; LocalConnectString = "\\BACKUP\\AppliClient\\Bdd.sdf"     And the followinf error : Error Code: 80004005 Message : SQL Server Compact encountered problems when opening the database. [Internal Error Number =0, Internal Error String = ] Minor Err.:28559 Source :Microsoft SQL Server Compact   Error Code: 80004005 Message : Minor Err.:0 Source :Microsoft SQL Server OLE DB Provider My CE database file Bdd.sdf exist on pocket pc, and I access to the sqlcesa35.dll with IE. The server is a Windows Server 2003 SP2 using SQL Server 2005.   Thank you in advance

problems with using SQLEXPRESS database on a server

I made an application that reads a KML file every few minutes and puts the data in a SQLEXPRESS database. It works perfectly on my laptop, but when I move everything to the server where it has to run, the application doesn't find the database anymore. I tried lots of connectionstrings already, but none works. I have to admit that my knowledge of connectionstrings is not that big, so I think the answer will be very simple. In my program I point to the app.config file who looks like this atm (remember, I changed the connectionstring a couple of times already, and I have no idea anymore where I started): <?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="ships" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\ftproot\eu006202\www.ortelius.icso.be\AIS\ships.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration> I pasted the error I get on http://www.webbouwers.be/error.png Any idea what I need to do to get it work? Thank you in advance, L.

Normalizing Large Database

I need to normalize (if that's the right word) a very wide table with more than 100 million records, turning it into a pair of relational tables. For many variables in the table, there are 20 fields that could be filled in. For simplicity's sake, the table looks like this: JoinID varchar(10) Value1 varchar(8) Value2 varchar(8) Value3 varchar(8) (so on, up to Value20) The way the table is currently set up, if I want to find records where "Value" equals 'x', I need to search across 20 fields. I want to create a table that has one record for each of the 20 Value fields that are filled in, ignoring those that are null. The only way I know to do that is something like below, which can take 20 hours on my table. Is there a more efficient (faster) way to do this? Thanks in advance for any advice. Tom DECLARE @sql varchar(Max) WHILE (@step < 21) USE SANDBOX DECLARE @table varchar(500) SET @table ='Receptacle' DECLARE @step int set @step = 1 BEGIN SET @sql = ' USE SANDBOX INSERT INTO ' + @table +' ( JoinID, Step, Value ) SELECT JoinID, ' + CONVERT(varchar(2),@step) + ' as Step, Value' + CONVERT(varchar(2),@step) +' as HCFPCL FROM SANBOX.dbo.TABLE WHERE VALUE' + CONVERT(varchar(2),@step) +' is not null ' EXEC(@SQL) SET @step+=1 END

DataAdaptor/Dataset problems when no row present in database tables

Hi All, I'm trying to use a DataSet to maintain some rows for a table, and when I've finished my changes, send all changes to the database using a SqlDataAdapter.    I find if there are no rows in the table in the database then I am getting a 'Object reference not set to an instance of an object' when I try to access the table in the Dataset. Is there a way to work with a Dataset like this ie. I start off with an empty table and I wish to add rows, to access the structure of the table rows, build rows, then add them and do the update on the SQLDataAdapter. Thanks, Sinead Here is my code: protected SqlDataAdapter memberDA = new SqlDataAdapter(); protected DataSet memberDS { get { if (ViewState["memberDS"] != null) return (DataSet)ViewState["memberDS"]; else return new DataSet(); } set { ViewState["memberDS"] = value; } } protected SqlDataAdapter getDataAdapterForMembers() { SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["SiteDBConn"].ConnectionString; memberDA = new SqlDataAdapter("usp_GetMembers", conn); memberDA.SelectCommand.CommandType = CommandType.Stored

Problems with SSRS 2008 Install when configuring a back-end database on a SQL Server 2008 R2 environ

We are tyring to configure SQL Server Reporting Services (SSRS 2008) not the R2 version with a SQL Server 2008 R2 version of database. We are facing error when the configuration manager within SSRS tries to execute its scripts against the database. Do you think that SSRS 2008 may have trouble using a SQL Server 2008 R2 version as the back-end? Also, what kind of permissions does the ID installing SSRS needs to have on the SQL Server database ?  Not much is available in the documenation or online.

Problems with People-picker control in a large organisation.

We are experiencing very slow people searches, when trying to pick a user from a SP group containing 500 names. Some people this week have experienced errors after about 10 minutes of waiting. Background: I have a form where the users need to pick an Authoriser. The List of Authorisers is held in a Custom Sharepoint Group. Site column was then built to select people from that SP Group. The good news is the Lookup address book dialog shows the group members OK Unfortunately if the users do a search in this dialog, or from the main form, the server now takes (on average)  around 3 minutes 51s to find the user (from about 500 names), and sometimes fails completely - either with an error, or just returns control to the form with no result. Has Microsoft changed the algorythm for this search to search the entire AD? (I am working in a huge organisation with domains round the world, some are NT4). On a previous project (last year) a similar search only look 2 s! Any suggestions? Any recent Server packs that might impact this (possitively or negatively?) Thanks Ruth, UK

retrieve large file from database ?


I have a database table that works as a file repository.  Currently there are binaries stored in there and I want to pull the "large" ones out in chunks.  Some of these files are in excess of 500 MB.  I have business rules that dictate if the file is >5MB to transmit in chunks.  <5MB and I can load into memory and rip out.  I got the uploading in chunks to work, but how do I get it to pull it out of the DB in chunks?

Right now I'm getting hit with a 'System.OutOfMemory' exception.  But when I recreate the byte array of the SAME size (empty though) it doesn't break.

Download Chunks (DAL)

public byte[] getBytesByDataID(int chunkSize, string dataID)
            string query = "SELECT data.data " +
                " FROM data " +

Can i use Windows desktop search indexing service to index a full sql database ?



i wonder if i can use the windows indexing service to perform indexing on a database instead of file system ?

i.e we need to use the windows indexing service to create index table that contains information from all rows in the database



Problems Connecting Excel Services to Oracle Database


I am just beginning to experiment with using Excel Services with BI Dashboards in SharePoint 2010.  I created a Excel Workbook that has a data connection using connection type of OLE DB Query with a connection string of "Provider=MSDAORA.1;User ID=<username>;Data Source=<oracleEnvironment>".  I have the Excel Services Authentication Settings set up for a specific SSS ID <mySSSID>.


This Excel Workbook has been Saved to SharePoint with an entire sheet published.  The Excel Web App is displaying the sheet and the Slicers are functioning just like they do in the Excel client.  When I refresh the data on the client I get prompted for a password and it works fine.  When I refresh on the Web App I have received a variety of errors depending on what I try but nothing has helped me track down what I have missed.

Importing large dat file into sql server database

I have a dat file which consists of thousands of columns which i have to insert into a database. I have to insert the data into multiple datatables. I am looking for the best possible way to do that. I have looked into bulk insert also but is there a better way? something like normalizing the data in the data access layer and inserting into the tables or creating ssis packages? Any help is highly appreciated.

index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)


index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)

database grew from 40 GB to 500 GB. space is reserved no free space to reclaim after reindex completes.

pls guide

database query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     Marks

Math       First Terminal    CT              2010    18
Math       First Terminal    Hall Exam   2010    67.2

Science   First Terminal    CT              2010    20
Science   First Terminal    Hall Exam   2010    78

Now how can I get the result followningly

                                First Terminal

                        CT       Hall Exam      Total


Windows 7 x64, SQL Express 2008 - Problems attach Northwind database

I've attached Northwind many times before, but seem to be unable to do so in Windows 7. I tried attaching from Visual Studio, using osql, and finally (after many hours) installed SQL Express 2008 / SQL Management Express 2008. All methods failed. The last failure (using SME 2008 Attach Databases) is sometimes:

Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\northwnd.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". (.Net SqlClient Data Provider)

and other times:

Database 'Northwind' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery. (Microsoft SQL Server, Error: 3415)

I have, of course, verified countless times that the files are NOT read-only.

Could any please explain what could be happening here? All suggestions much appreciated.

many thanks,


Unable to repair index issue in database


checkdb  found issues with one of the databases

The issue has been going on longer then we have current backups, so restoring a backup will not fix the issue.

I have tried running the repair in both safe mode and data loss mode but neither process repaired anything.

I even tried scripting the removial of the foriegn keys and the primary key affected and adding them back to try and fix it but the issue is the same.

Does anyone know of anything else I can try or any tech docs to discuss how to fix this?

Here are the error messages from checkdb

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3855, State 1: Attribute (data_space_id=1) exists without a row (object_id=1053246807,index_id=24) in sys.indexes.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8951, Level 16, State 1, Line 1
Table error: table 'sys.sysidxstats' (ID 54). Data row does not have a matching index row in the index 'nc' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:7120:26) identified by (id = 1053246807 and indid = 23) with index values 'name = '_WA_Sys_00000016s3EC74557' and id = 1053246807'.
Msg 8952, Level 16, State 1, Line 1
Table error: table 'sys.sysidxstats' (ID 54). Index row

Error Creating Spatial Index - Could not find spatial tessellation sheme 'GEOMETRY_GRID'

I am trying to create a spatial index on a column of type geography in Server Management Studio. We are running SQL 2008 Workgroup. I setup the index and click ok, but getting the error:

Could not find spatial tessellation sheme 'GEOMETRY_GRID'. Specify a valid tellellation scheme name in your USING clause.

What in the world does that mean? We have created other spatial indexes in different tables successfully.

Search for Clustered Index in database on varchar, nvarchar column



I have very big database, i am running a Re-index job and it's keep failing throwing everyday error stating "Online index operation cannot be performed on clustered index as index contains column of data type text, nvarchar.

is there any way i can search and change all such datatype of culstered into non-clustered indexes or any better solution i have.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend