Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

Introduction to Bitmasking in SQL Server 2005 Expand / Collapse
Author
Message
Posted Wednesday, December 20, 2006 12:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,397, Visits: 9,611
What about adding an indexed calculated field based on those 4 columns.  Than would make the range search much more simpler and it should be much more overhead on the server (not more than having to scan an index (not sure here but it seems that a scan is hard to avoid)).
Post #331898
Posted Wednesday, December 20, 2006 1:15 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 2,901, Visits: 1,805
I don't think that there is any easy answer.

One of the examples in the "Update your development skills to SQL2005" course uses a CLR datatype to hold an IP address.

I will have to experiment to see if that works.

The dilema I am facing is that on one hand I need efficient storage but on the other I need readable data.

I haven't index a calculated field so I'm not sure if the field is calculated on the fly or whether it is materialised.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #331907
Posted Wednesday, December 20, 2006 1:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,397, Visits: 9,611

It gets materialised when indexed.  So you add another 8 bytes / row + PK size.

 

But then again this may be very acceptable if the inserts can be a tiny bit slower but the selects much easier to write, and maybe more efficient (still don't know if that bunch of ors completely miss the index seek or not)).

Post #331912
Posted Wednesday, December 20, 2006 2:56 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 1,945, Visits: 2,906
What is bad about a four octet index? In fact, I can probably build a very one by ordering the octets from fastest changing to slowest.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #331952
Posted Wednesday, December 20, 2006 3:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,397, Visits: 9,611
I'm not sure I'm following you here Joe.  Can you explain more on what you think is the best indexing approach?  I have no experience live or theorical in that case, especially with ultra large amounts of data.
Post #331953
Posted Wednesday, December 20, 2006 4:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 1,945, Visits: 2,906
Assume that octet_4 is the fastest changing octet-1 is the slowest, so I would use:

CREATE INDEX IPaddresses_idx ON Foobar (octet_4, octet_3, octet_3, octet_1);

That will give me the best tree structure and then I can assemble a display string in a VIEW or the front end.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #331972
Posted Friday, December 22, 2006 9:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 2,901, Visits: 1,805
In 192.168.2.10 the octets would be as follows

  1. 192

  2. 168

  3. 2

  4. 10


Would I be correct in thinking that by using the most changing octet SQL Server is most likely to think that the index is a good selective index?


LinkedIn Profile
Newbie on www.simple-talk.com
Post #332554
Posted Friday, December 22, 2006 9:49 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,397, Visits: 9,611

The problem has never been selectivity... I'm still wondering how the heck the server will be able to do a seek on a search like this one :

 

WHERE (
Oct_1 = 72
AND (Oct_2>108 OR (Oct_2=108 AND Oct_3>10) OR(Oct_2=108 AND Oct_3=10 AND Oct_4>=2))
)
OR
Oct_1 = 73
AND (Oct_2<90 OR (Oct_2=90 AND Oct_3<=205) OR(Oct_2=90 AND Oct_3=205 AND Oct_4<=1))
)

Post #332559
Posted Friday, December 22, 2006 9:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 2,901, Visits: 1,805
I've got to put up a Christmas tree, swear at the lights, murder a few carols and fight with relatives, but I will give it a go after Christmas and get back to you.

LinkedIn Profile
Newbie on www.simple-talk.com
Post #332560
Posted Friday, December 22, 2006 2:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 1,945, Visits: 2,906
Don't forget to go to the mall and snarl "Merry Christmas, you stupid %^%!@#$& !" at your fellow man.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #332635
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse