Subscription View performance issue

Question

Not sure if this is an MDS thing, or if this belongs in the SSIS forum:

I have a view that is based on a coupld of subscritpion views. The usage is as a Source in an SSIS package, which ultimately feeds a Cached Connection manager, but that is not important here.

When I use the "table or view" option is SSIS and specify my view, it takes forever to get rows out and into the SSIS pipeline. If I then switch the SSIS Source to be "SQL command" and paste in the SELECT statement that is defined in the view, it is lightning fast.

Anyone experience this? I would think that under the covers, that SQL would use the same execution plan regardless of if I have a named view or use the base T-SQL script. (Keep in mind that with MDS databases, the base subscription views are themseleves made up of many table with complex joins, as is the nature of MDS)

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 1

I guess it is because of the provider which is used by each of this type.

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 2

Sorry, but I don't quite understand what you mean, "the provider which is used by each of this type." Do you mean "Table or view" vice "SQL command"?

If you are saying that a SQL command type would be faster than a direct table or view, I even tried this but did not mention it in my original post: I selected "SQL command" and for the command, instead of the entire view definition, used "SELECT * FROM <my view>". That was just as slow the first option.

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 3

How many rows is each view supposed to return? How many rows is the view combining both rows supposed to to return? How complex is the join between the 2 views? What version of MDS and of SSIS are you using (2008 R2 or 2012)?

Answer 4

Versions: SQL 2012 SP1 all the way around. And now that you mention it, this started when we upgraded the database from 2008 R2.

Can't remember exact row counts, but maybe 350,000 and 20,000, with a left outer join to the smaller one.

QA and PROD environments are still using the 2008 R2versions without issue, but the DEV environment is the one that isn't playing nice.

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

Answer 5

Not too surprised, I had a similar experience; although I've never been able to substantiate what the performance problem is.

Just a couple of pointers, you might have done this already:

1. If you are joining by Code (which is something I have the nasty habit of doing), try using the ID or the MUID for the join. Both the MUID and the ID are indexed and the Code is not. This of course assumes that your 'smaller' entity is a domain entity in your 'bigger' entity.
2. Try reducing the number of columns you are getting back. I personally think the views generated by MDS include too many columns, most of which are not needed.

One last thing, I could be SSIS 2012. I've got the feeling that when you write a query it starts supplying rows as it gets them but you access a table or view directly it does something else, perhaps even getting the row count.

King regards,

M

Answer 6

I'm going to chaulk this up the MDS 2012, not SSIS 2012 and here is why:

Even running the exact same SSIS package inside of BIDS 2008 from the desktop has horrible performance when pointed to the 2012 MDS database. That and the fact that the QA and PROD installations have not seen any issues. But as soon as DEV got upgraded, the package started hanging up in the middle of the night.

Thanks for the suggestions. All good.

---

Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.