February 28, 2025 at 1:14 am
Hello, I want to ask you about the following case that I live with a company in my country. They have a SQL server licensed with SQL enterprice ver 2016, where in the same instance about 25 databases coexist and in particular the main one and heart of all the systems, the Erp base. There are countless applications within this company and many of them require consulting information from the main base, called Organization. The query is performed by consuming views in the source database and accessed from each of the other databases and client applications. To date there have been many delays in obtaining information from this base through hearings. Could you tell me the best way to improve the obtaining of data from that database other than with views? For example, a replica base, data snapshot processes, intermediate tables, use of sp to select data in exchange for views, etc. I am not an expert in SQL, I am a developer and there is no DBA at this time. I appreciate your help, likewise the mechanism to detect which views are used most often, what resources are involved in their execution, average records obtained, etc. If this is possible to obtain it with the SQL server tools. thank you
March 1, 2025 at 2:06 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 1, 2025 at 3:37 pm
You are going to have to provide a lot more detail for anyone to give you a sensible answer but, from what you are saying, this sounds more like an application design problem and not a database problem. There are a lot of potential solutions among which are:
March 12, 2025 at 7:19 am
Your organisation really does need someone whose job is to focus on DB performance and availability. The normal job title for this role is DBA.
There are a lot of aspects to your problem:
* Merging data from multiple DBs is likely to take longer than getting data from a single DB.
One common aspect (there are others) is that filtering where joins and where clauses involve multiple DBs, larger sets of data have to be shipped between DBs than would be the case if all data was in a single DB.
There are many mitigations around this, including establishing a consolidated DB designed to minimise data movement at query time. It needs a SME to look at what you are doing and your pain points to work out the most cost effective solution.
* Performance within a single DB requires good indexing. You may well find SQL server recommends additional indexes on some of your tables. However, don't blindly implement all the suggested indexes, as this will likely result in multiple almost identical indexes. They would be good for queries but each index on a table impacts update performance on that table.
* View design can often be improved. I have seen many view upon view designs that force subqueries to be run that have no bearing on the final results required.
SQL Server is much better at dealing with this than Oracle, but often the best optimiser for view upon view designs is the human eyeball that can direct the human hand to write a better view for the end-user query.
* Chatty applications. I have seen many applications that treat a DB as a dumb data store and do joins and filtering within the application. This results in shedloads of data being retrieved, held in shedloads of memory, then filtered using multiple scans of that data.
This is the data equivalent of having no classes or subroutines, and using in-line code for every case when you want to perform a given process. Efficient it is not. Just as good application design will identify classes and subroutines within classes to push down functionslity to perform common processes in a consistent manner, good DB design will push down data access and filtering to perform that in a consistent and efficient manner.
Going back to your OP, you probably know most of the above already. If your management is pushing you to get better performance, it is time to talk to them about the business cost of poor performance and the salary cost of getting somebody whose job is to minimise some of that business cost. Hopefully this will result in you getting a DBA.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy