asked
when i select from my view in my database is much slower than i select that( database.schema.table) from system databases why ?
SELECT id,name,date,IsGlobal FROM database..view where @PosType IN (0, 2) and IsGlobal=Case when @IsGlobal<>'-1' then @IsGlobal else IsGlobal End
A huge number of factors affect what plan the Query Optimizer chooses. This is a fairly complete list: the options you have in your connection (eg. which language your session is using), the database's compatibility level, the number of rows in the tables, the data in them, how up to date the statistics are, the version, edition and patching level of SQL. It's a huge subject. Views can be a performance problem - especially when nested - so I try to avoid them if I can. Sometimes they are useful to encapsulate some widely used logic but otherwise they don't help much and can be harmful. You've clearly hit a case where the view can cause a bad query plan so I suggest you avoid it or try re-factoring it so the problem stops.
thanks i think you are right ,but can i ask another question : i installed SQL server version 2014 in this server but my database compatibility level is 100,is it poor performance ? because in another Server that query runs well has SQL server 2012 and my database compatibility level is 100
Have you compared the query plans? That should tell you what's happening.
18 People are following this question.
