ansaurus

Question

Efficient way to Query a Delimited Varchar Field in SQL

Answer 1

+1 A:

the most efficient way is to normalize your db design. never store CSV values into a single cell. other than using like you might consider full text search.

Mladen Prajdic 2009-07-15 15:46:55

Worthless answer.

Josh Stodola 2009-07-15 15:48:47

are you quite sure about it being worthless? i gave you the only other valid option you can use. patindex and all other stuff is just like "Like"

Mladen Prajdic 2009-07-15 16:23:22

Outside of normalization (which isn't an option to OP), and PATINDEX, FTS is the other performant option. FTS is, honestly, probably the best option since it is going to be optimized for the volume of data you have and most likely will return better results in less time and with fewer I/O operations than PATINDEX.

Jeremiah Peschka 2009-07-15 16:26:52

Answer 2

A:

If you have the freedom, change the design to reflect a tag system. With a table for these 'tags' which links them to the respective entries.

tharkun 2009-07-15 15:47:46

I can't change the design.

Josh Stodola 2009-07-15 15:49:19

I'm curious. Even if I could change the design, what is this "tag" system? How is that designed?

Josh Stodola 2009-07-15 15:51:07

Just a very simple step of design normalization. Don't store your tags in strings but store them one entry each in a 'tags' table (n:n).

tharkun 2009-07-15 16:00:00

Then you can do JOIN queries, count how many times a certain tag is used, count the number of tags per main entry, etc.

tharkun 2009-07-15 16:01:07

Can you add to the design without changing the existing? If you think that WHERE Keywords LIKE '%endorse%' is not efficient enough you could have a background process index these strings. E.g. have a cron job, cut these strings up into single units and store them in a tags table and reference them.

tharkun 2009-07-15 16:06:12

Searching would definitely be faster that way.

tharkun 2009-07-15 16:07:02

this would also remove the comma problem.

tharkun 2009-07-15 16:14:36

but then again... you're in a different world

tharkun 2009-07-15 17:03:28

I don't see how that normalizes anything. Just introduces a join. I still have repeating data... no?

Josh Stodola 2009-07-15 17:11:00

want to normalize it properly, ok. make also a reference table. the tags table has every tag only once, the reference table links the main table with the respective tags. this table has just two columns (can have more) which are the the mainID and the tagID. you can set an index across the two and make it unique for proper integrity. no repeating data anymore.

tharkun 2009-07-15 17:59:48

Still... assume I have a main table record that has 6 tags, I will have six of these records all with the same mainID, pointing to different tagID records... no? This just seems to complicate matters further!

Josh Stodola 2009-07-15 18:51:15

in a way, yes. but you asked for alternatives and efficiency. proper normalization would definitely increase performance and possiblities. maybe it's not worth the hassle.

tharkun 2009-07-15 19:57:02

Answer 3

A:

You could use PATINDEX()

USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

http://msdn.microsoft.com/en-us/library/ms188395%28SQL.90%29.aspx

Shane Cusson 2009-07-15 15:48:04

Thanks. Do you think this is more efficient than LIKE?

Josh Stodola 2009-07-15 15:50:14

According to the first comment here: http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/is-patindex-faster-than-like.aspx, Full Text indexing might be an even better way to go. Full Text: http://msdn.microsoft.com/en-us/library/ms142571.aspx

Shane Cusson 2009-07-15 16:12:12

Answer 4

+4 A:

Since normalization is not an option, the next best option is going to be to configure and use Full Text Search. This will maintain an internal search index that will make it very easy for you to search within your data.

The problem with solutions like LIKE '%pattern%' is that this will produce a full table scan (or maybe a full index scan) that could produce locks on a large amount of the data in your table, which will slow down any operations that hit the table in question.

Jeremiah Peschka 2009-07-15 16:34:53

Looks like the right answer... but if FTI is not already enabled/configured/whatever in the system, would adding it in be as much a deal-breaker as modifying the table structure?

Philip Kelley 2009-07-15 16:44:19

With SQL Server 2005 you will have to get a DBA to install the FTS feature, so depending on the situation this could present difficulties. Once FTS is installed, creating the indexes is simple. This could be an easier sell than normalization, though, because other applications that access the source data won't need to be changed.

Jeremiah Peschka 2009-07-15 17:07:22

Thanks for the answer! How do I configure Full Text Search? Can you link me up, please?

Josh Stodola 2009-07-15 17:08:54

Installation instructions can be found here: http://msdn.microsoft.com/en-us/library/ms142490(SQL.90).aspxAnd here's a big list of "how to" topics on FTS from Microsoft: http://msdn.microsoft.com/en-us/library/ms142503(SQL.90).aspx

Jeremiah Peschka 2009-07-15 17:38:31

Thanks for the help, I think we will finally be installing this soon!!

Josh Stodola 2009-11-24 14:39:50

ansaurus

tags:

views:

answers:

Efficient way to Query a Delimited Varchar Field in SQL

related questions