Enhancing Web Application Performance through Database Optimization (2024-05-29)

SQLServerCentral Newsletter for May 29, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Enhancing Web Application Performance through Database Optimization: A Comprehensive Study]( - [SQL ConstantCare® Population Report: Spring 2024]( - [From the SQL Server Central Blogs - I like KQL and I like managing my test data – are they mates?]( - [From the SQL Server Central Blogs - Full-Text Search in SQL Server: A Comprehensive Guide]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [Automated Azure SQL VM Patching]( The Voice of the DBA  Common Algorithm Concerns When we build software, many of us use the same algorithms to solve problems. We might choose a similar method for a quicksort or a lambda validation or a regular expression. For database work, your code for a running total (or other common challenge) is likely very similar to many other people. At least on the same platform. You might solve this differently in SQL Server and Oracle, but for the same type of database, many of us write very similar code. Actually, for many developers, they copy and paste an answer from SQL Server Central, Stack Overflow, or another site. I'm not sure if I think this is good or bad, as it's a good idea to reuse code if it solves the same problem. If you copy it and don't test it, that's bad. After all, the code might not solve your slightly different problem if you don't check it. In the modern world, if we built software for our business using an AI assistant, could our company be liable if we knew our competitors were using the same AI service? Is this any different than a human developer performing a copy/paste from SQL Server Central? I don't think it is in many cases, though the same concerns about intellectual property might be present in either case. The concerns over AI seem murky in some sense, especially as the AI might "generate" code that isn't directly available on some public resource. I do think that this is more of a collusion using a service than an algorithm. Still, in the hyper-connected world, where many of our applications might look to take advantage of some service instead of implementing it ourselves, this could be an issue. I ran across a piece that discusses [a lawsuit about a common pricing algorithm]( being used by different hotels. In this case, it's not that the developers at different hotels used the same code, but rather that the hotels used the same service from a company, which of course, used the same code for all their customers. Whether you think this is a valid lawsuit or not, this is the type of legal action that others might bring if two competitors ended up using the same AI service and developer very similar code that might behave the same way. I don't think that AIs (at present) can actually develop new algorithms or solve problems in a new way. Instead they predict the likely solution based on how they've been trained by similar scenarios. In that case, how concerned are we about how getting common solutions in disparate pieces of software? For most of us, I think we've be pleased we have well-tested (hopefully) code that runs efficiently re-used in many places. That would be better for most systems in the world. What isn't better is if humans become more adept at specifying prompts and producing software without lots of specialized expertise. For many developers that might be average, or even slightly below average, this might cause them concern for the security of their position. With good reason, as labor is one of the most expensive parts of building software. Ultimately, just as with any other position, the best way to build a safe, secure career, is to continue to build your skills and produce value for your employer. That way it's unlikely any AI will every outperform you. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Enhancing Web Application Performance through Database Optimization: A Comprehensive Study]( nai3 from SQLServerCentral In the contemporary digital landscape, web applications play a pivotal role in delivering services and facilitating interactions between users and businesses. However, the performance of these applications heavily relies on the efficiency of underlying databases. This comprehensive study aims to explore the intricate relationship between database optimization techniques and web application performance enhancement. The study begins by delineating the significance of web application performance and its correlation with database operations. It examines various factors affecting performance, including data retrieval, storage, and processing. Furthermore, it identifies common challenges such as latency, scalability issues, and inefficient query execution. Through an extensive review of the literature, this study evaluates state-of-the-art database optimization methodologies and their applicability to web applications. Techniques such as indexing, query optimization, denormalization, caching mechanisms, and data partitioning are scrutinized, highlighting their potential to mitigate performance bottlenecks. Moreover, the study investigates emerging trends and technologies in database management systems (DBMS), such as NoSQL databases, in-memory databases, and cloud-based solutions. It assesses their suitability for enhancing web application performance, considering factors like data volume, query complexity, and real-time processing requirements. [External Article]( [SQL ConstantCare® Population Report: Spring 2024]( Additional Articles from Brent Ozar Blog The short story: SQL Server 2019 appears poised to swallow the SQL Server market altogether, hahaha. [Blog Post]( From the SQL Server Central Blogs - [I like KQL and I like managing my test data – are they mates?]( HamishWatson from The Hybrid DBA's Blog Test Data Management (TDM) is essential in software development to ensure your application runs smoothly and reliably across different environments. When deploying applications in Azure, Test Data Management can... [Blog Post]( From the SQL Server Central Blogs - [Full-Text Search in SQL Server: A Comprehensive Guide]( Rohit Garg from MSSQLFUN Introduction Full-Text Search in SQL Server is a feature that provides linguistic search capabilities against text data in SQL Server tables. It allows for searching of character-based data types... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Automated Azure SQL VM Patching There is an Automated Patching service for VMs running SQL Server in the Azure Cloud. This uses the SQL Server IaaS Agent Extension to work with PowerShell. What types of patches are installed with this service? Note: All updates are marked with tags, such as Important, Critical, or something else Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Implementing Change Data Capture I have a complex environment that uses quite a few SQL Server 2022 servers. We have a powerful SQL Server running Enterprise that hosts most of our transaction data. However, there are remote locations that run Standard, Web, Express, and Express with Advanced Services editions for local collection of data before the data is transferred to our central Enterprise Edition server. We are considering using Change Data Capture (CDC) to lower the amount of data we transfer from clients. Which editions can we implement this feature on? Answer: Standard and Enterprise only Explanation: CDC is an Enterprise and Standard edition feature only. Ref: Data Warehouse Features and Editions - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Backup with Checksum Show No Issues, While DBCC CHECKDB Reports Errors]( - I took a backup with the CHECKSUM option, which was successful and showed no errors. However, when I restored the same backup, it showed DBCC CHECKDB errors. I want to understand why the checksum did not detect any issues, but DBCC CHECKDB did. What are the issues that a checksum will not detect but DBCC […] [Linked Server Product Name?]( - Just came across something weird. Was wondering if anyone else has ever seen this. All the years I've worked with SQL server linked server objects, I have always put "SQL Server" in product name. All the code examples on the internet include that or mention (in BOL) "Product name Type the product name of the […] [Can index fragmentation cause the database to enter suspect mode?]( - My database was in suspect mode, and when I checked using DBCC CHECKDB, it returned errors. As mentioned in the link below, I used the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command, but few errors persisted. Later, I dropped and recreated the indexes. This resolved the issue. My question is whether index fragmentation could […] SQL Server 2016 - Development and T-SQL [Multiple deadlocks occurring with contentious code]( - We are seeing multiple frequent deadlocks occurring with the following extract of code, which forms part of a stored procedure called sp_WS_Save_Record_Details. We have a web service that calls multiple instances of this stored procedure simultaneously. Records in the Record_Stub table are identified by an external reference. The code is trying to ensure that if […] [select string removing two sets of characters]( - Hi , I need help replacing a certain strings from a query response. My searches are pointing to a replace in the actual table but I am looking for sql that returns a different value than what is in the table. I am selecting a varchar(25) field and want to return the characters without the […] [View generating different plan]( - Hello  I have a view based upon a number of tables I'm selecting the same data from this view on 3 identical servers (same memory, processors, tempdb, settings etc.) The amount of data is nearly the same - Same on most dimensional tables; 10's of thousand out of millions of the fact (so < .5%) […] SQL 2012 - General [getting logged in user name to the pc that running the report]( - hi all, is there anyway in SQL to getting logged in user name to windows on the pc that running the power bi report connected to SQL as i've tried some scripts but it retrieved the logged in user to the SQL server itself I did search on some and found that script but it […] SQL Server 2019 - Administration [Swapping ports between 2 instances - can't understand results]( - Have 2 instances on the same TestServer 1. Default 2014 Dev edition on port 1433 2. Named 2019 Express instance "SQLExpress2019" on port 15191 SQLBrowser is working. TCP=1433 port is open on this TestServer. I swapped ports between the 2 instances and restarted the services. Can't tell if UDP 1434 is open.. UDP port 1434 […] [SQL JOB add/modified]( - Is there a trigger to notify when a new SQL Agent job is created/modified? SQL Server 2019 - Development [Unable to connect to SQL 2019 using Pyodbc]( - Development Environment I am learning how to use pyodbc and accessing database in SQL server 2019. Here are the software versions I am using: SQL server 2019 VS Code 1.89.1 Microsoft ODBC driver 18 for SQL Server Python 3.12.3 pyodbc 5.1.0 Developer laptop: Windows 11 Connection Errors: When running python test program in VS, it […] [Aggregating over multiple columns]( - Hello friends, See setup code below. I have a working solution but I am concerned that the DISTINCT is going to kill performance. I am hoping to see some alternative ways to solve this problem so I can compare performance with my real data. DROP TABLE IF EXISTS #Sales CREATE TABLE #Sales ( Country VARCHAR […] Connecting [Can you connect Power Apps to SQL Server if you work in the government?]( - My agency has been using the combo of on-premises SQL Server for the back end and local desktop software for the front end. Now upper management is saying that they want web applications only. And they really want to use our MS 365 license rather than have to pay for anything else. I'm investigating Power […] Design Ideas and Questions [Database design help and tips for a small app used to coach tennis players.]( - Hi, I hope this isn't too long of a question- I tried to make as brief as simplified as possible but maybe I am posting to the wrong place. I am a tennis coach and creating a database/app to automate tasks related to students, programs, practices, performances, and testing. I am finishing the database design […] SQL Server 2022 - Development [I have few snapshot transactions, now i need to put noloc in all tables.]( - I have few snapshot transactions , now my admin has informed me that put no lock in all tables in snapshot transactions, as it was blocking locking deadlocking with others. so I suggested to put read uncommited instead of snapshot when u are putting no lock in all tables of the transaction. q1) snapshote takes […] [Querystore bug(?), not showing data]( - My company where I worked recently upgraded to SQL Server 2022 Enterprise and Querystore started to behave odd (the problem was detected then anyway). If I start Querystore, and filter data, "last hour/last 12 hours" etc everything seems to work ok. But if I pick "custom" and manually specify date+time (even if it's the same […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -