SPServices & XSLT Data Views

Jul 8, 2013 at 3:02 PM
Hello -

I am currently working in 2007 (yes, still!) and I have built a data view using 2 lists. The lists are large and have a lot of fields along with being on separate sites. So, I have built the DV on one of the sites w/ a list and limited the fields in the list that lives on that site. When I try to limit the fields from the other list (on a different site), it errors out. I cannot move the lists to the same site. So, long story short the data view works, but is super slow. I was trying to see if I could pull the data a different way - I have pulled both the lists using SPServices, but I am not experienced enough to make it talk to my XSLT or how to use the data being pulled from SPServices and make it look like my data view that I currently have. Our network isn't the best to begin with, but the performance of this sucker is just SLOW. Any suggestions would be greatly appreciated.

Thanks,
Jen
Coordinator
Jul 9, 2013 at 3:53 AM
Jen:

Nothing wrong with working in 2007; lots of my clients are still there!

It's hard to know what to suggest. Your DVWP will probably work if you can edit the XSL manually. If you want to use SPServices instead, you'll need to build the logic in script and there will be a bit of a lag after page load. Generally speaking, either approach ought to work.

M.
Jul 9, 2013 at 8:53 AM
I can edit the XSL manually, but not sure I'm smart enough to know what to edit to make the performance better. Doesn't really matter how the data is pulled, whether its SPServices or not, just somehow need to make the process faster.
Coordinator
Jul 9, 2013 at 1:22 PM
Well, the winning strategy here is to retrieve the least amount of data possible to provide the functionality you need. Only retrieve the items you need and set the ViewFields to only retrieve the columns you need.

M.
Jul 9, 2013 at 1:51 PM
Is there a trick to making that work with a list on a different site? I've tried the copy and modify method and it errors when I got show data. I've also tried just putting the CAML in the selectcommand and no luck either.
Jul 9, 2013 at 3:19 PM
Hey prplrosez1, have you downloaded U2U CAML builder to verify the CAML is good?

How many items are in that list, and how many will be returned?

If you make a DVWP on the same site at the slow list, is there a performance change?
Jul 9, 2013 at 3:39 PM
both lists are large. i have tried the DVWP on both sites to see which runs faster and they are both crap. so, the one list is basically a directory of everyone (has about 300 items) and it pulls a people picker field & 5 other fields (department, rate, etc.). this list tracks literally everything on a person; from their name to their shoe size (gov't customer), so it has about 375 fields. the other list (on a different site) is a list in which the people submit travel (has about 1100) it's a large form w/ about 350 fields. my DVWP points the people pickers in both lists to each other and populates the names down the left side, with grouping and sorting. in the travel list, the creator has the option to travel with up to 30 other people. so there are 31 people picker fields that it is matching up. not every list item has 31 people attached to it. it varies from 1 to 31 and everything in between. however, i can only get it to do about 7 of the addt'l people before it breaks. i only need about 8 fields + the 30 additional people fields, so a max of 40 fields from this list. the DVWP filters out 2 weeks back from today and 90 days forward. ideally they wanted a year out, but when i try that, it breaks due to the amount of data being pulled.

i can't use the CAML builder because it is on the SIPR (Secret) side and I cannot download that there. but even using the copy & modify method, deleting the columns i don't need from the travel list, i get an error on show data.

i'm not really locked in on doing things a certain way. just how the data is displayed. they basically want people down the side with their attached events displayed linearly (similar to a gantt chart). current solution works, just takes a long time to initially load the page, which no one is liking.
Coordinator
Jul 9, 2013 at 3:54 PM
Your lists don't have that many items, but they are "wide". It sounds like a poor architecture on some level (375 columns in a list is a lot!). You may be stuck with the performance you are getting unless you write things from scratch and do more of the work yourself.

The fact that one list is in a different site won't have any impact at all; that's simply a different WebURL in the DataSource and the retrieval is identical.

M.
Jul 9, 2013 at 4:11 PM
Ok. So this is what I have:

<SharePoint:SPDataSource runat="server" DataSourceMode="List" SelectCommand="<View><Query><Where><And><Geq><FieldRef Name="StopDate"/><Value Type="DateTime"><Today
OffsetDays="-15"/></Value></Geq><Leq><FieldRef Name="StopDate"/><Value Type="DateTime"><Today
OffsetDays="90"/></Value></Leq></And></Where></Query><ViewFields><FieldRef
Name="AdditionalTravelers"/><FieldRef Name="Traveler"/><FieldRef Name="ID"/><FieldRef
Name="NameTraveler1"/><FieldRef Name="NameTraveler2"/><FieldRef
Name="NameTraveler3"/><FieldRef Name="NameTraveler4"/><FieldRef
Name="NameTraveler5"/><FieldRef Name="NameTraveler6"/><FieldRef
Name="NameTraveler7"/></ViewFields></View>" UseInternalName="True"><SelectParameters><asp:Parameter DefaultValue={C431DCEB-94A2-4612-8855-C57408552F0C}" Name="ListID"></asp:Parameter><asp:Parameter DefaultValue="/suppact2/n3/n33-35/" Name="WebURL"></asp:Parameter> blah blah blah

for the CAML i tried the above along with adding the FileDirRef field and ProjectProperty Name="Title" as well and no luck. the site the page is on is /suppact2

these lists were created prior to my arrival here and are used in a bajillion different ways which is why there are so many columns. i have no issues working from scratch and doing it myself to make this go faster but i'm not sure what else to do. i know enough to be dangerous but not enough to be smart.

thanks again,
Jen
Jul 9, 2013 at 4:21 PM
by the way...the error i get is the "non-specific" one :)
Coordinator
Jul 9, 2013 at 5:01 PM
It's still a little hard to figure out exactly what you're trying to do, but the CAML above ought to reduce the data retrieved quite a bit. (Seeing the NameTraveler1..7 columns tells me that your data isn't well normalized and should probably be stored one-to-many in separate lists.)

My only suggestion would be to start with the straight<View></View> selectcommand and then slowly (one clause at a time) build up the CAML until it is where you need it. You can do this in a DVWP with the single DataSource and then copy it into the AggregateDataSource when it is right.

Why are you trying to use the ProjectProperty? That will just give you the name of the list.

M.
Jul 9, 2013 at 5:24 PM
i started out with one field at a time, which is how i figured out where the threshold was for the travelers. so basically i'm screwed because of the dumb way the lists are created/set up. :)
Coordinator
Jul 9, 2013 at 5:28 PM
I think you're running into the 8 lookups per view threshold. Person or Group columns are actually considered lookup columns in that count because they pull their data from the User Information List.

M.
Jul 9, 2013 at 5:31 PM
ok. that makes sense. that's the only field that i can use to match up the list items to each other. maybe i will talk to them about pulling the additional people out and separating them. I appreciate the help, thanks very much!