How to increase SQL query performance of a view?

%3CLINGO-SUB%20id%3D%22lingo-sub-1426450%22%20slang%3D%22en-US%22%3EIncrease%20update%20performance%20view%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1426450%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20new%20to%20Microsoft%20community%2C%20so%20please%20let%20me%20know%20if%20this%20post%20is%20not%20in%20the%20right%20forum%20and%20if%20so%20which%20one%20would%20be%20the%20right%20one.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eissue%3A%3CBR%20%2F%3E-%20Update%20%26amp%3B%20query%20(using%20Power%20BI)%20of%20my%20Sales%20Weekly%20View%20takes%20forever%20(%26gt%3B30min)%20and%20I%20would%20like%20to%20understand%20how%20I%20could%20improve%20this%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrent%20setup%3A%3CBR%20%2F%3E-%20Sales%20Daily%20View%3A%20View%20containing%20aggregation%20and%20joins%20of%20two%20main%20sales%20tables%20and%20a%20date%20table%3C%2FP%3E%3CP%3E-%20Sales%20Weekly%20View%3A%20View%20joining%20the%20%22Sales%20Daily%20View%22%2C%20a%20sales%20table%20and%20two%20dimension%20tables%20and%20containing%20extensive%20logics%20(mainly%20aggregations)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20you%20have%20any%20idea%20how%20to%20streamline%20this%20and%20improve%20the%20performance%20of%20the%20view.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1426450%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Intelligence%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Warehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443593%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20increase%20SQL%20query%20performance%20of%20a%20view%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443593%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F683411%22%20target%3D%22_blank%22%3E%40kevinconseil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHi%20Kevin%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI1)f%20you%20want%20to%20use%20a%20view%20(slow)%20then%20%3CU%3E%3CSTRONG%3Eyou%20can%20put%20indexes%20on%20the%20view%3C%2FSTRONG%3E%3CSTRONG%3E%20on%20the%20columns%20which%20are%20used%20in%20the%20join%20clauses%3C%2FSTRONG%3E%3C%2FU%3E.%3C%2FP%3E%3CP%3E3)You%20can%20also%20create%20indexes%20on%20the%20columns%20of%20the%20tables%20of%20the%20%3CSTRONG%3Eview%2C%20that%20are%20used%20in%20the%20join%20clauses%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E4)Stores%20procedures%20are%20usually%20faster%20then%20views%20because%20they%20are%20compiled%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Earticle%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.techrepublic.com%2Farticle%2Fsee-performance-gains-by-using-indexed-views-in-sql-server%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.techrepublic.com%2Farticle%2Fsee-performance-gains-by-using-indexed-views-in-sql-server%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHope%20this%20helps%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EBye%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1458354%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20increase%20SQL%20query%20performance%20of%20a%20view%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F690089%22%20target%3D%22_blank%22%3E%40Simple_Balayeur%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20info%20%3A)%3C%2Fimg%3E%3CBR%20%2F%3E%3CBR%20%2F%3E1-%20By%20putting%20indexes%20on%20a%20view%2C%20do%20you%20mean%20creating%20a%20indexed%20view%20%3F%20If%20yes%20then%20this%20is%20currently%20not%20an%20option%20as%20my%20view%20does%20not%20fit%20many%20requirements%20from%20the%20indexed%20view%3CBR%20%2F%3E3-%20I%20already%20have%20indexes%20on%20the%20base%20tables%20of%20the%20view%3C%2FP%3E%3CP%3E4-%20I%20m%20gonna%20investigate%20the%20stored%20procedure%20thanks%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi everyone,

I am new to Microsoft community, so please let me know if this post is not in the right forum and if so which one would be the right one. 

 

issue:
- Update & query (using Power BI) of my Sales Weekly View takes forever (>30min) and I would like to understand how I could improve this

 

Current setup:
- Sales Daily View: View containing aggregation and joins of two main sales tables and a date table

- Sales Weekly View: View joining the "Sales Daily View", a sales table and two dimension tables and containing extensive logics (mainly aggregations)

 

Please let me know if you have any idea how to streamline this and improve the performance of the view.

2 Replies
Highlighted

@kevinconseil 

 

Hi Kevin

 

I1)f you want to use a view (slow) then you can put indexes on the view on the columns which are used in the join clauses.

3)You can also create indexes on the columns of the tables of the view, that are used in the join clauses

4)Stores procedures are usually faster then views because they are compiled

 

article:

https://www.techrepublic.com/article/see-performance-gains-by-using-indexed-views-in-sql-server/

 

Hope this helps,

Bye

Highlighted

@Simple_Balayeur Thanks for the info :)

1- By putting indexes on a view, do you mean creating a indexed view ? If yes then this is currently not an option as my view does not fit many requirements from the indexed view
3- I already have indexes on the base tables of the view

4- I m gonna investigate the stored procedure thanks :)