January 8, 2008 at 1:54 pm
I have an array table that is currently using the real data type in SQL Server 2005.
Analyzing the data in this table it has been determined that a substantial amount of data that is stored is more accurately defined as tinyint or smallint. Due to memory limitations and a desire to more optimally utilize existing memory we are creating three array tables based upon data types... for the purpose of this request I will name them ad_tinyint, ad_smallint, and ad_real (which will include both numeric and int datatypes.
The table I currently have is ad and contains all the data under the real datatype at the current time.
I wish to query out the tiny int records and move them to the ad_tinyint table, the smallint records to the ad_smallint table, and leave the remaining data; renaming the table to ad_real.
Does anyone have a clue regarding accomplishing this feat?
January 8, 2008 at 2:05 pm
Do your reals have decimal places?
If not, just query based on the range that the smaller data types can handle
Select * from Array_table where value between 0 and 127 -- tinyint
Select * from Array_table where value between -32768 and 32767 -- tinyint
...
If you do have decimal places, then you'd need to add another filter to check that the value and the floor of the value are the same.
Could you explain a bit more about the array table, what it stores and for what reasons? I'm curious about why you have this situation and, if maybe, there's another way of doing things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2008 at 2:27 pm
Currently the table has 441 data columns and two identifying columns (that comprise the PK), the data type of which is defined as real. However, the data is composed of multiple data types that are consistent by row; I am simply trying to store the data with the correct data type and facilitate better memory utilization when that data is later queried by our engineers.
There is considerable data that is better described as real, numeric, or float (4 byte) with a decimal and both negative and positive values; but there is also a lot of data that is better defined as smallint or tinyint (without a decimal).
The problem I am having is figuring out how to get each data type out of the current table and into the table defined for it.
January 8, 2008 at 2:51 pm
It seems to me that you could use:
select *
from currenttable
where col1 between 0 and 255
and col1 = floor(col1)
That would give you all the rows that are TinyInt.
Move those to the TinyInt table, then change the between to "-32768 and 32,767", and move all of those to the SmallInt table. Then what you'd have left is everything else.
(Of course, you'll have to put in correct table and column names.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 8, 2008 at 3:00 pm
That query returned both integers and real data types when run against the table.
January 9, 2008 at 10:32 am
I resolved my particular issue as follows:
The primary key is composed of two columns, TRACKING_ID and MEASUREMENT_ID.
The TRACKING_ID identifies the process which gathered the data and MEASUREMENT_ID identifies the type of data gathered.
I compiled a list of the MEASUREMENT_ID's in the table and systematically went through each catagory of types to determine the data type of the data contained within the table.
Once I identified the data type I then simply ran the query:
INSERT INTO [PRODUCT].DBO.ARRAY_DATA_[DATA TYPE] SELECT *
FROM [PRODUCT].DBO.ARRAY_DATA
WHERE MEASUREMENT_ID BETWEEN [point 1] AND [point 2]
There are four colors for each measurement calling for 4 MEASUREMENT_ID's explaining the points above.
This took some time but was the cleanest method I could come up with to make the separation.
Thank you for your suggestions to my problem here. While they did not result in the solution of this particular problem, they do give me encouragement that there is a resource out there for solutions to problems in the future.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply