Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2006-02-24 : 19:28:27
|
| I'm working with an app that takes some fairly large XML input strings and produces search results based on them. So far so good.The app also caches the results of each query in a table to make pagination less expensive. So far so good.However, since the actual queries are fairly large and performance-sapping, I'd like to first check and see if the same XML document has been requested recently, and use the saved results if so. Here's where my problem lies.The XML is sent in in an NVARCHAR(4000). My theory was to create a table with a guid referencing the search results and an nvarchar(4000) for the source XML (any given source XML is guaranteed to produce the same results).However, trying to create an index on my nvarchar(4000) column produces the warning "The maximum key length is 900 bytes. The index 'ix_matchLog_query' has maximum length of 16000 bytes. For some combination of large values, the insert/update operation will fail." Which looks ominous.It seems that forcing a table scan of my XML cache table for every qury will probably do more harm than good; is there any easy way to story large nvarchar values and index them? Or am I approaching this in a confused manner?Thanks-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-02-24 : 20:00:18
|
| Heh. Just found the CHECKSUM function; that seems to be what I want.Cheers-b |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-02-24 : 20:15:22
|
| I was gonna suggest to create a checksum value using MD5 with is quite powerful, but it seems the CHECKSUM-function will do the same trick. The following link guides you on how to create a UDF to make MD5-hashes: http://www.codeproject.com/database/xp_md5.asp--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2006-02-24 : 20:23:21
|
| Thanks! I've got CHECKSUM installed and working, and it seems to be great. The nice thing is that it can do multiple columns into the same checksum; I didn't want to complicate my example, but my query actually runs on two xml documents, either or both of which can be populated. A quick cache table with columns for the two XML values, and it's saving about 20% of my very expensive search queries.Cheers-b |
|
|
|
|
|
|
Indxes on large rows?