Yes, views can be a performance problem, to do with the table scans, and indexing (or lack thereof) .... when i had a huge IN statement querying my view it caused problems, but on the actual indexed tables, no problem ...
Dean Cleaver <[EMAIL PROTECTED]> wrote: Is that efficient though? I had a similar situation where I had a many to many relationship between users and programs, so I created a view that reflected this, and used this view in several other views. Once the view got to a certain size (the number of rows now exceeds 100,000 easily) all the other queries became very slow - the reason being is that I presume a temp table of every user/program combination is created, joined within my new view, and then filtered after the fact - rather than being filtered for the program I want immediately. Not sure if I explained that well, but by removing this view and replacing it with the actual tables in each of the other queries improved the speed dramatically. Dino -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Phil Sayers Sent: Friday, 11 August 2006 06:17 To: [email protected] Subject: Re: [ADVANCED-DOTNET] SQL Server 2005 Xml question... Views are you friend. create views that each do 4 or 5 joins making sure you SELECT the fields you need. then use those views in your main query. but if you need all the jons, then you may have to resign yourself to "this is what it takes to get the results as defined by requirements" so you go ahead and do it. :) -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] Behalf Of Mike Andrews Sent: Thursday, August 10, 2006 2:16 PM To: [email protected] Subject: [ADVANCED-DOTNET] SQL Server 2005 Xml question... Guys, I'm using SQL Server 2005 and implementing a solution where utilizing the Xml capabilities will prove quite useful. However, I have a question that I wanted to see if any of y'all knew or would know where I might find some help on. I'm using a PATH mode query (FOR XML PATH syntax) on a query. My problem is that my query is going to get really, really large. I will need to join a single table with 11 other tables and several of those tables will in turn join with other tables. Instead of me trying to build a tremendously large, all encompassing query, I'd like to create smaller queries and then join them together. Does anyone know if that's possible? Thanks, Mike =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com --------------------------------- Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta. =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
