The iHub Wish List

[Bob Zumbrunnen]

Awww... Not gonna tell me now? There are so many possible combinations to try.

But I'll probably run across it today anyway. There are more features to add, and just before putting into production, I'd just barely gotten done converting it from spaghetti code to something more closely resembling gotelbows.

During the beginning of the project, I was concatenating a big StrSQL statement to be submitted to the server based on each choice I encountered. Didn't occur to me until pretty late that the only way to really do it was to start with a big StrSQL, build an InnerJoinSQL, build a WhereSQL, then concatenate all 3.

As far as workload goes, it's a bear. The great and powerful db server (don't look behind the curtain), which hums along at something like 10% or less while the webserver is in the 60%+ range, was getting spikes as high as 25% in testing yesterday.

And not much can be done about that using SQL Server's built-in way of indexing CLOB/Text fields. No matter what you do, the function used for searching that index ("CONTAINS()", in this case) has to run across the entire table. Then your other constraints can be applied. Even if I said "select * from message where msg_id=1234567 and contains(msg_text,'Zumbrunnen')", the thing would search the entire table first to find all occurrences of 'Zumbrunnen' then figure out which one of all those posts bore msg_id=1234567.

You can't even do "select * from (select * from message where msg_id=1234567) where contains(msg_text,'Zumbrunnen').

Contains() will only operate on a raw table. Not on a recordset.

Sucks.

Still, it runs pretty fast and the only way it runs really slow is if it's returning a very large recordset. Then the slowness is because of the webserver; not the db server.

Unless a later version of SQL Server does such searches a better way (just letting me use contains() on a recordset would be sufficient), I'll have no choice but to do a home-grown indexer (ala SI) once the message-count gets way up there.