Scott Weikert wrote: > > I'm not so concerned about my code and getting the data in/out - I could > do it either way with equal ease, I figure - I'm asking more from a > best-practices standpoint, and a database-efficiency and size standpoint. > You should definitely try to split data like this out into separate tables. When you include repeated groups of data in a column like that, you don't take advantage of a relational database's strengths.
When you do this: WHERE fieldname LIKE '%|1|%' The database has to look at EVERY row in the table, do the LIKE, and then return the matches. If you have that data split out into a separate table, the database can make use of an index in searching for matches, which may mean it only looks at a small portion of the rows before being able to return your recordset. The performance difference might not be noticeable on a small table (which might just be doing a table scan anyway), but on a large dataset, your performance will be much better with the separate table. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2549 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
