quote:

---

Originally posted by Peso

select     top 1000 table1.id,
           table1.clob
from       table1
inner join somewhereelse on somewhereelse.id = table1.id

Peter Larsson
Helsingborg, Sweden

---

create table ##TMP (id int);
while ... begin
     insert into ##TMP values (...);
end
select     top 1000 table1.clob
from       table1
inner join ##TMP on ##TMP.id = table1.id

quote:

---

Originally posted by Peso

It depends on what "outside the database" is. If all 1000 values already are stored in a textfile, try to use OPENROWSET. If located in another database, try to use linked server.


Peter Larsson
Helsingborg, Sweden

---

select top 1000 clob
from table1
where id in (
    select id 
    from openxml (@hDoc, '/ids/*')
    with(id int));

quote:

---

Originally posted by Kristen

quote:

---

Do you have an ORDER BY on the TOP 1000?

---

Yes, ORDER BY timestamp DESC.

quote:

---

If YES then it may help if the IDs are on a Clustered index.

---

They are.

quote:

---

Are the XML blobs in excess of 8K (average)?

---

Approx. 30% of them are expected to be over 8K in size. Text In Row option is not currently used (but is being considered).

quote:

---

Is it worth trying SQL2k5's nvarchar(MAX) instead of ntext?

---

Do you have any suggestions for a particular storage method that provides the fastest retrieval time, being it nvarchar(max), xml, image (using utf8 encoding), or ntext with 'Text In Row' option enabled?

quote:

---

Possibly store them as files, and just store the filename (and maybe path) in the DB.

---

Yes, but what are the practical implications of having millions of files (updated daily) stored on a disk? How quickly (and badly) will performance deteriorate once we start deleting/creating tens of thousands of small files on a daily basis?

[quote]I go with PSamsig's "How long does it take to do a SELECT TOP 1000 with no WHERE?"

---