cm2187
asked on
Indexing a real-typed column (MS SQL Server)?
Hi
I am relatively new to databases. I am using MS SQL Server 2005.
I have this huge table (several millions rows) and I need to frequently do lookups with the following WHERE clause on two real-typed colunns:
WHERE ABS(Col1 - value1) < Error AND ABS(Col2 - value2) < Error
with value1, value2 and Error being some real-typed numbers.
My question is does it make sense to create an index for Col1 and Col2? And if so which sort of indexation? Given the sheer size of the table, try and error is a quite painful process.
My intuition from the trainings I found is that it would probably make sense to have a clustered index. But I am not sure if this is relevant to non-integer, non-unique fields, and if the ABS() function will not mess up any saving we could get from the indexation.
If anyone would have any light to share on this topic it would be greatly appreciated!
Thanks
Charles
I am relatively new to databases. I am using MS SQL Server 2005.
I have this huge table (several millions rows) and I need to frequently do lookups with the following WHERE clause on two real-typed colunns:
WHERE ABS(Col1 - value1) < Error AND ABS(Col2 - value2) < Error
with value1, value2 and Error being some real-typed numbers.
My question is does it make sense to create an index for Col1 and Col2? And if so which sort of indexation? Given the sheer size of the table, try and error is a quite painful process.
My intuition from the trainings I found is that it would probably make sense to have a clustered index. But I am not sure if this is relevant to non-integer, non-unique fields, and if the ABS() function will not mess up any saving we could get from the indexation.
If anyone would have any light to share on this topic it would be greatly appreciated!
Thanks
Charles
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Unless you are prepared to reconsider the use of a Real data type, you will have bigger problems than performance. Since Real is an aproximate value, you may find that 2<> 2 which would wreck havoc on your WHERE clause. Use a fixed length numeric (decimal) data tyoe instead.
ASKER
In my case I don't need a very high precision. But would decimals be more efficient than real? (I would tend to think they probably would if they are really an integer)
Use the database tuning adviser - its pretty good at recommending the right indexes for simple queries such as this.
>>In my case I don't need a very high precision.<<
Since you have them in a WHERE clause you could end up where @Value < @Value is true
>>But would decimals be more efficient than real? <<
Unless you are doing astronomical calculations always use fixed length numeric values.
>>I would tend to think they probably would if they are really an integer<<
No, they are not integers, they are just accurate.
Since you have them in a WHERE clause you could end up where @Value < @Value is true
>>But would decimals be more efficient than real? <<
Unless you are doing astronomical calculations always use fixed length numeric values.
>>I would tend to think they probably would if they are really an integer<<
No, they are not integers, they are just accurate.
ASKER
Thanks very much
I would doubt an index will help you here. Because you are basing it on absolute values, the db engine is still
going to have do a full-table scan to evaluate the expressions in the WHERE clause.
Regards,
Patrick