question

cornpoppy avatar image
cornpoppy 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
databasedbasystem-databasesquery-plandba-developer
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
Have you compared the query plans? That should tell you what's happening.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes,SQL Server used different execution plans for this query,but i checked in another server have same execution plans( in master database and my database)
0 Likes 0 ·
In master database used hash match strategy for plan but in my database used nested loop and estimated number of rows is different in two database
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.