Selecting data from recordset
Posted: 5/13/2004 3:10:24 PMBy: Comfortably Anonymous
Times Read: 2,392
Likes: 0 Dislikes: 0
Topic: Programming: .NET Framework
I've got a database table (Geo) with the following:
ID: int (Autoindex, primary key)
X: int
Y: int
Home: int
The table consists of an X/Y grid of data.
While I can do individual queries like "SELECT Home from Geo WHERE X=345 and Y=972" to get an individual value for Home, it's a pretty large grid (1000x1000, a million records), so I'm thinking it will probably quicker to do a query like:
SELECT X,Y,Home FROM Geo WHERE (X BETWEEN 50 and 80) and (Y BETWEEN 200 and 220)
Then somehow select from the recordset using the X & Y values. But I have no idea how to "query" the recordset based on values in the recordset. Hoping someone out there will know how!
Also, is there a good way to move the two dimensional data in the recordset into a two dimensional array?
Thanks in advance!
ID: int (Autoindex, primary key)
X: int
Y: int
Home: int
The table consists of an X/Y grid of data.
While I can do individual queries like "SELECT Home from Geo WHERE X=345 and Y=972" to get an individual value for Home, it's a pretty large grid (1000x1000, a million records), so I'm thinking it will probably quicker to do a query like:
SELECT X,Y,Home FROM Geo WHERE (X BETWEEN 50 and 80) and (Y BETWEEN 200 and 220)
Then somehow select from the recordset using the X & Y values. But I have no idea how to "query" the recordset based on values in the recordset. Hoping someone out there will know how!
Also, is there a good way to move the two dimensional data in the recordset into a two dimensional array?
Thanks in advance!
Rating: (You must be logged in to vote)
Discussion View:
By: Comfortably Anonymous
Times Read: 2,392
Likes: 0 Dislikes: 0
Topic: Programming: .NET Framework
By: Comfortably Anonymous
Times Read: 2,392
Likes: 0 Dislikes: 0
Topic: Programming: .NET Framework
Replies:
Selecting data from recordset
Posted: 5/13/2004 3:10:24 PMBy: Comfortably Anonymous
Times Read: 2,392
Likes: 0 Dislikes: 0
Topic: Programming: .NET Framework
??? No replies in a month? Man, I'd like to know the solution too! Someone please reply with the solution.
Rating: (You must be logged in to vote)
Selecting data from recordset
Posted: 5/13/2004 3:10:24 PMBy: Comfortably Anonymous
Times Read: 2,392
Likes: 0 Dislikes: 0
Topic: Programming: .NET Framework
Well, still haven't figured that one out yet, and I'm now doubting that an ADO recordset is "intelligent" enough to do so. But using the ADO.NET .ExecuteScalar method (Returns only one row) and setting X & Y both to Primary Keys, I am able to get sufficiently quick response to do what I was doing.
I created the following method:
public int getTerrainByXYW(int X, int Y, int World)
/// Returns the terrain for a single cell
{
string qTerrain = "SELECT Terrain FROM Geo WHERE X=" + X +
" AND Y=" + Y + " AND World=" + World;
SqlCommand cmdTerrain = new SqlCommand(qTerrain, dbCon);
int Terrain = (int)cmdTerrain.ExecuteScalar();
return Terrain;
}
I found I am able to populate a 25x25 grid in less than a second. Although it seems wasteful to use 625 separate queries (All using the same existing database query, so there's no "create a new connection" overhead), the performance is surprisingly good. The trick was to set multiple primary keys, before that it was taking a LONG time.
I created the following method:
public int getTerrainByXYW(int X, int Y, int World)
/// Returns the terrain for a single cell
{
string qTerrain = "SELECT Terrain FROM Geo WHERE X=" + X +
" AND Y=" + Y + " AND World=" + World;
SqlCommand cmdTerrain = new SqlCommand(qTerrain, dbCon);
int Terrain = (int)cmdTerrain.ExecuteScalar();
return Terrain;
}
I found I am able to populate a 25x25 grid in less than a second. Although it seems wasteful to use 625 separate queries (All using the same existing database query, so there's no "create a new connection" overhead), the performance is surprisingly good. The trick was to set multiple primary keys, before that it was taking a LONG time.
Rating: (You must be logged in to vote)