|
|
Posted by Erland Sommarskog on 07/07/06 22:01
@sh (spam@spam.com) writes:
>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
>> news:Xns97F91FCEE7C9Yazorman@127.0.0.1...
>> It must also have been hot in the office when this was desiged:
>
> Well it seemed a good idea at the time, and infact works within the
> application itself brilliantly and efficiently without numerous hits on
> the DB - however this one element now is proving tricky
>
> We're using SQL 7 so based on what you've said, I guess you can't help
> ;o)
SQL 7? That will not make it even less painful. :-)
There are two approaches. One is to run a cursor over the rows and
for each row call a stored procedure that unpacks the row into a
table, so that you can run the queries the proper way. You can find
examples of such procedures on my web site:
http://www.sommarskog.se/arrays-in-sql.html#SQL7.
I would in such case such suggest that you put this code in the trigger
on this table, so that you always can work on properly desinged tables.
Overall, you should strive of changing the database design to move away
from this structure. Yes, I see that you in other post said that it's
too late to change the design, but I disagree. Unless the product already
has a declared end of life, it's never too late to change a flat-out
incorrect design like this one.
The other approach is a query similar to:
WHERE ',' + UserHotelIDAcess + ',' LIKE
'%,' + CampaignHotelIDs + ',%'
This theme has some variations, see
http://www.sommarskog.se/arrays-in-sql.html#realslow. All of them are
painfully slow, and could prove unworkable if your data has any volumes.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|