Performance Improvements for a AZURE SQL DB

Performance Increase for philipa-PatStat SQL DB We have implemented a SQL database on AZURE cloud services which hosts a commercial patent database (PatStat) from the European Patent Office. We need professional SQL expert support to improve the performance of queries. We are familiar with SQL query language but not too much with database design and definitely not with performance optimization. Here, we urgently need expert support. We are a small group of patent attorneys and web developers which have some sound knowledge of programming and basic knowledge of database design and queries, but our technical knowledge in SQL performance improvement is limited. About the SQL DB: Database is hosted on AZURE SQL (S2 tier) Database is a commercial database from the European Patent Office which contains around 90 Mio patent datasets. PatStat has been optimized for statistical analysis. Background information on the database and its data content and structure can be found here [login to view URL] We have uploaded the XML files to AZURE SQL 2-3 weeks ago and played with the data a bit. The possibilities this data shows are impressive. Nevertheless, the queries are taking between 2-10 minutes. This is by far too long. Acceptable would be query times < 5 (max.10) sec. as we would like to use the query results for graphical online dashboards. The data from PatStat are “static” which means there are only updates twice a year. We have thought of indexed/materialized views to improve query performance but couldn’t manage to code this. Foreign-Key relations have to be added according to the attached database diagram Indexes might needed to be added / altered accordingly.