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 - 2005-10-27 : 22:43:48
|
| One of my projects is a dating/community type site, and we're in the process of converting a bunch of code to use XML/XSL rather than ASP-coded rendering for results.Essentially, there are a whole lot of features on the site that serve to return a list of users, all of which are displayed in the same format. The criteria for selection and ordering are different, but the data itself is the same.For instance, you might search for potential dates based on basic criteria (gender, location, etc). Or you might be looking at your list of bookmarked users that you've saved in the past. Either way, the PK is an int UserID, and all of the data is the same.My problem here is that I'm not sure how to efficiently go about (hopefully) creating the same XML (or recordset which will be converted to XML in ASP) with a whole bunch of radically different queries.Since some of the sort criteria can be very expensive (sorting by "compatibility", for instance, entails a pretty hefty amount of work), it seems silly to have a bunch of SP's that select the proper UserID's and hand them off to a recordset-producing or XML-rendering SP; that way, there's the potential to do expensive operations twice for each call (if you sort by compatibility but only hand off the userID's, the rendering SP needs to compute compatibility for every row again for display purposes).Most of the operations which will produce recordsets/XML will be paginated, so only <100 results will actually be rendered, even though the entire dataset (call it 50,000 rows) will often need to be computed so it can be sorted properly.I feel sure that I'm missing something here. I'm thinking of doing something wacky with temporary tables and a rendering SP that updates the temp tables with any values not already there, but that seems, well, wacky.Does anyone have insight on how to tackle something like this? I'd really rather NOT duplicate the same code (the same joins, column lists, etc) in each one of the (many) sp's that can return results, since that would be very difficult to maintain (yes, that's what we're doing now, but only because we just recently unified the results that get returned; it used to be that each feature that generated lists of users returned slightly different results, and *wow* was that a pain).Cheers-b |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-10-28 : 04:47:21
|
can you maybe provide an example?i've read this 3 times and i don't quite get it...Go with the flow & have fun! Else fight the flow  |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-10-28 : 19:06:29
|
| Yeah, I'm having a hard time articulating this, and the code is pretty huge and complex to post. Let me try again:- A bunch of SP's return the same columns (a "list of users", along with attributes for those users)- Internally, those SP's are very different -- they look things up from different tables, sort differently, etc. Some of them use expensive queries in the sort criteria, some don't. - I'm hoping to find an elegant way to share code that produces the results for easy maintenance, essentially decoupling the data lookup from the data return, so if columns are added/removed/reordered, I don't have to dig through a whole bunch of SP's looking for all of this partly-identical code.The best idea I've come up with so far is some kind of intermediate table that has all of the columns that these things return, with only the PK and some kind of "search ID" set to not null. That search ID would be unique to each query, so the various lookup SP's could populate the columns and rows that they have to do anyway, and then some kind of "return" sp would do select against that intermediate table with a whole bunch of "IsNull(intermediate_table.column,[proper lookup])" things. It seems kind of cumbersome, and it means that large resultsets are being actually inserted into a real table with all of that overhead rather than just a table variable, but it does get at my goals.Or am I just raving here? Hello, does this problem/solution make sense to anyone else?Cheers-bCheers-b |
|
|
|
|
|
|
|
|
Dealing with several SP's w/ similar results