| Author |
Topic |
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
 Posted - 2006-09-07 : 02:04:52
|
Hi
I want to use Table name as variable like this
declare @tablename varchar(10)
select * from @tablename
how it will done
Ranjeet Kumar Singh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-07 : 02:22:58
|
use Dynamic SQL
declare @sql varchar(8000)
select @sql = 'select * from ' + @tablename
exec (@sql)
KH
|
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2006-09-07 : 04:23:05
|
Hi
We are working on a project VEHICLE monitoring in which we find a VEHICLE position according to VEHICLE moveing.we find place of a VEHICLE according to Latitude and Longitude.Our problem is if nomber of VEHICLE increase then master table(which have place according to Latitude and Longitude of VEHICLE)data increase more .and to find place for a particular Latitude and Longitude of a VEHICLE we match this Latitude and Longitude from master table which take more time because master table have records of total vechile .
so we thought that we create different table for a different VEHICLE
and when we need to find a VEHICLE position then we will search a particular table of this VEHICLE and by latitude and longitude we will find Place.but by concept maintanance of table will tough because we have one table for one VEHICLE
suggest me our concept is how much right.
Ranjeet Kumar Singh |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-07 : 04:29:41
|
quote:
Originally posted by ranjeetsingh_6
Hi
We are working on a project VEHICLE monitoring in which we find a VEHICLE position according to VEHICLE moveing.we find place of a VEHICLE according to Latitude and Longitude.Our problem is if nomber of VEHICLE increase then master table(which have place according to Latitude and Longitude of VEHICLE)data increase more .and to find place for a particular Latitude and Longitude of a VEHICLE we match this Latitude and Longitude from master table which take more time because master table have records of total vechile .
so we thought that we create different table for a different VEHICLE
and when we need to find a VEHICLE position then we will search a particular table of this VEHICLE and by latitude and longitude we will find Place.but by concept maintanance of table will tough because we have one table for one VEHICLE
suggest me our concept is how much right.
Ranjeet Kumar Singh
Instead of maintaining one table per Vehicle, why don't you partition your existing master table into multiple smaller tables and use partitioned view ?
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 04:31:06
|
How many records are there in Master Table?
If there are less than 100 million rows, there is no need to "partition" the records.
Just make a column named VehicleID and you're set!
Peter Larsson
Helsingborg, Sweden |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-07 : 04:31:40
|
what query you are using for retriving the values from the master table?
using seperate for each vehicle will be Ad-hoc when the number of the vehicle increase... its better to keep that out of option..
Chirag |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 04:55:07
|
You can also make a "history" table.
Keep one Master table, with all data for last month or so. When data gets older, move the data to a history table.
Or only keep a week, or one day only.
Peter Larsson
Helsingborg, Sweden
EDIT: Last sentence |
|
|
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2006-09-07 : 04:55:34
|
Hi
suppose we have 100 VEHICLE and its data into master table(its ID,latitude ,longitude,place) and suppose one VEHICLE(its id is V100) among 100 VEHICLE moveing in London at latitude=23.9876654327 and longitude=72.6543783211 and then i want to find the nearest place
of this VEHICLE according to its latitude and longitude to searching
master table which kept place for a latitude and longitude.
if i partition master table then its become like concept to make different table for a diff VEHICLE.
there are not fix how much will record in master table.its depends upon VEHICLE moveing records.
Ranjeet Kumar Singh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 05:00:18
|
No, you will only have TWO tables. And they can be UNIONed in a view very easy with no maintenance, because these two tables are all you got.
How many records do you have today? How many records do you expect per VEHICLE and DAY? 50 readings per vehicle and day? That's 5000 records per day. You will at least have a go for 20000 days, which is approx 54 years, keeping the record count under 100 million.
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 05:04:50
|
If I interpreted you correctly, you lend some vehicles and want to keep track of them by using GPS information. Keep only the current lend for each vehicle in "master" table, other lendings in history table.
1) Is there a need to search previous readings in the same table?
2) What is the use for "nearest place"? Assistance of some kind?
Peter Larsson
Helsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-07 : 05:10:29
|
Also after partitioning you don't have to bother about whether to search in current table or history table. You just have to write view which will be single BIG virtual table, and let SQL Server decide from which table to bring data based on your WHERE condition !!
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 05:26:48
|
Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.
Peter Larsson
Helsingborg, Sweden |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-07 : 05:48:57
|
quote:
Originally posted by Peso
Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.
Peter Larsson
Helsingborg, Sweden
I hope this Boss doesnt see this post else he may start receiving deduction for next 2 month.. 
Chirag |
|
|
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2006-09-07 : 05:57:43
|
Hi
yes i lend some vehicles and want to keep track of them by using GPS information. Keep only the current lend for each vehicle in "master" table, other lendings in history table.
1) yes we are searching previous 60 days records in the same table?
2)"nearest place" means we are find most nearest place of a latitude and longitude.Because it is not possible that we kept all places for every latitide and longitude.
i am sending table structure and again focous on problem
VEHICLETRACK Table Contain current position of Vehicle (By Tables's Latitude and longitude we will Find place)
CREATE TABLE [VEHICLETRACK] (
[Jrid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Deviceid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lat] [float] NULL ,
[Lon] [float] NULL ,
[Timerecorded] [datetime] NULL ,
[id] [decimal](10, 0) NOT NULL ,
[status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[other1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[other2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Speed] [float] NULL ,
[Distance] [float] NULL ,
[DirLat] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DirLon] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[userid] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
------------------------------------------------------------
Map_Places_India Table Contain main places and its latitude and longitude.
CREATE TABLE [Map_Places_India] (
[ID] [decimal](18, 0) NULL ,
[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lat] [float] NULL ,
[Lon] [float] NULL
) ON [PRIMARY]
GO
-------------------------------
when any latitude and longitude will insert into VEHICLETRACK Table then we find nearest place of this latitude and longitude (I mentain nearest place it means we have not stored all places in Map_Places_India Table for each inserted latitude and longitude because it is not possible) .
My goal is to find place for inserted latitude and longitude and
there are 100 VEHICLE are using this Software.
Suppose two place have distance 1000 meter and one VEHICLE change its movement 10 times between these two places and its will return
10 latitude and langitude,each among ten latitude match 2000 records(from Map_Places_India Table )to find its nearest place.
This is example of only one VEHICLE which move between 1000 meter and stop 10 times suppose its cover 500 Km. and stop 300 times
then each 300 latitude search its place.Similarly it may for multiple VEHICLE.
Our main moto is performance and maintance
Ranjeet Kumar Singh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-07 : 06:08:30
|
I have a suggestion.
1) Store the last 60 days of information in a "Master" table (or as long as the current lend period is), for each vehicle.
1.5) When lending period is over for any vehicle, move the data to a history table.
2) Create a view that returns LAST (in time) position of every vehicle.
3) Use pythagoras theorem to get "nearest" place for very vehicle in the view.
Does it sound simple enough?
Peter Larsson
Helsingborg, Sweden |
|
|
|
ranjeetsingh_6
Posting Yak Master
125 Posts |
Posted - 2006-09-07 : 06:52:26
|
Hi
Thanks I think it will better implementation.I am working on it.
Ranjeet Kumar Singh |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-07 : 07:16:52
|
Add indexes and a PK to your table and be sure that your sql is optimized before doing any other things to attempt to increase performance .
- Jeff |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-07 : 07:21:31
|
quote:
Originally posted by chiragkhabaria
quote:
Originally posted by Peso
Harsh, I think we made his day and destroyed his last two month's work...
And that in less than an hour.
Peter Larsson
Helsingborg, Sweden
I hope this Boss doesnt see this post else he may start receiving deduction for next 2 month..
Chirag
Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-09-07 : 07:34:04
|
quote:
Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!
Are you working for SQL Team.. 
Chirag |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-09-07 : 08:06:32
|
quote:
Originally posted by chiragkhabaria
quote:
Actually I received warning that if i don't reach 1K before end of this year, I may not receive appraisal bonus!!
Are you working for SQL Team..
Chirag
If that were the case, I would not be posting here !
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
Next Page
|
useTable name as a variable