You sooo do not want to select distinct on a memo field... If you need memo data, you want to either select everything else and then select the memo data individually, or select without the distinct. For smaller data ( number, varchar(20) and the like ) distinct sometimes returns results faster because the result set is smaller, but with memo data, the likelyhood is that the distinct ( if applicable ) will make the query take much longer to return, because -- assuming the distinct even works on a memo field ( I don't think it's even valid in MS SQL Server ) the database drivers have to compare every character of what could be tens or hundreds of thousands of characters in a given record against the same character position in every other record returned. -- probably most of the time it's going to get to drop out of that routine within the first 20 or so characters, but it's just not worth the potential overhead.
Probably this is why the memo data is trucated at 255 characters -- the folks at MS implemented the distinct keyword to automatically convert memo data to varchar(255) for distinct comparisons to prevent them hozing up a query or worse. S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > OK, this is weird: > <cflock scope="session" timeout="30" type="readonly"> > <cfquery datasource="medsuppapp" name="get_access_exp"> > select id, exp_id, title, org, date_range, hours, description > from experiences > where id = #session.suserid# > </cfquery> > </cflock> > returns the full value of the "description" field, which is type MEMO. > However, > <cflock scope="session" timeout="30" type="readonly"> > <cfquery datasource="medsuppapp" name="get_access_exp"> > select distinct id, exp_id, title, org, date_range, hours, description > from experiences > where id = #session.suserid# > </cfquery> > </cflock> > truncates "description" to 255 characters. Why? (CF 5, Access ODBC data > source) > Ricq Pattay > Senior Analyst/Programmer > College of Veterinary Medicine > University of Minnesota - Twin Cities > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
