Michael Bryan

Michael Bryan

1561692812

SQL Database Performance Tuning for Developers

SQL Database Performance Tuning for Developers - SQL performance tuning can be an incredibly difficult task, particularly when working with large-scale data where even the most minor change can have a dramatic (positive or negative) impact on performance…

In mid-sized and large companies, most SQL performance tuning will be handled by a Database Administrator (DBA). But believe me, there are plenty of developers out there who have to perform DBA-like tasks. Further, in many of the companies I’ve seen that do have DBAs, they often struggle to work well with developers—the positions simply require different modes of problem solving, which can lead to disagreement among coworkers.

When working with large-scale data, even the most minor change can have a dramatic impact on performance.

On top of that, corporate structure can also play a role. Say the DBA team is placed on the 10th floor with all of their databases, while the devs are on the 15th floor, or even in a different building under a completely separate reporting structure—it’s certainly hard to work together smoothly under these conditions.  In this article, I’d like to accomplish two things:

  1. Provide developers with some developer-side SQL performance tuning techniques.
  2. Explain how developers and DBAs can work together effectively.

SQL Performance Tuning (in the Codebase): Indexes

If you’re a complete newcomer to databases and even asking yourself “What is SQL performance tuning?”, you should know that indexing is an effective way to tune your SQL database that is often neglected during development. In basic terms, an index is a data structure that improves the speed of data retrieval operations on a database table by providing rapid random lookups and efficient access of ordered records. This means that once you’ve created an index, you can select or sort your rows faster than before.

Indexes are also used to define a primary-key or unique index which will guarantee that no other columns have the same values. Of course, database indexing is a vast an interesting topic to which I can’t do justice with this brief description (but here’s a more detailed write-up).

If you’re new to indexes, I recommend using this diagram when structuring your queries: 

Basically, the goal is to index the major searching and ordering columns.

Note that if your tables are constantly hammered by INSERT, UPDATE, and DELETE, you should be careful when indexing—you could end up decreasing performance as all indexes need to be modified after these operations.

Further, DBAs often drop their SQL indexes before performing batch inserts of million-plus rows to speed up the insertion process. After the batch is inserted, they then recreate the indexes. Remember, however, that dropping indexes will affect every query running in that table; so this approach is only recommended when working with a single, large insertion.

SQL Tuning: Execution Plans in SQL Server

By the way: the Execution Plan tool in SQL Server can be useful for creating indexes.

Its main function is to graphically display the data retrieval methods chosen by the SQL Server query optimizer. If you’ve never seen them before, there’s a detailed walkthrough.

To retrieve the execution plan (in SQL Server Management Studio), just click “Include Actual Execution Plan” (CTRL + M) before running your query.

Afterwards, a third tab named “Execution Plan” will appear. You might see a detected missing index. To create it, just right click in the execution plan and choose the “Missing Index Details…”. It’s as simple as that!

(Click to zoom)

SQL Tuning: Avoid Coding Loops

Imagine a scenario in which 1000 queries hammer your database in sequence. Something like:

for (int i = 0; i < 1000; i++)
{
    SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES...");
    cmd.ExecuteNonQuery();
}

You should avoid such loops in your code. For example, we could transform the above snippet by using a unique INSERT or UPDATE statement with multiple rows and values:

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008

INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005

UPDATE TableName SET A = CASE B
        WHEN 1 THEN 'NEW VALUE'
        WHEN 2 THEN 'NEW VALUE 2'
        WHEN 3 THEN 'NEW VALUE 3'
    END
WHERE B in (1,2,3)

Make sure that your WHERE clause avoids updating the stored value if it matches the existing value. Such a trivial optimization can dramatically increase SQL query performance by updating only hundreds of rows instead of thousands. For example:

UPDATE TableName
SET A = @VALUE
WHERE
      B = 'YOUR CONDITION'
            AND A  @VALUE -- VALIDATION

SQL Tuning: Avoid Correlated SQL Subqueries

A correlated subquery is one which uses values from the parent query. This kind of SQL query tends to run row-by-row, once for each row returned by the outer query, and thus decreases SQL query performance. New SQL developers are often caught structuring their queries in this way—because it’s usually the easy route.

Here’s an example of a correlated subquery:

SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c

In particular, the problem is that the inner query (SELECT CompanyName…) is run for each row returned by the outer query (SELECT c.Name…). But why go over the Company again and again for every row processed by the outer query?

A more efficient SQL performance tuning technique would be to refactor the correlated subquery as a join:

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
	LEFT JOIN Company co
		ON c.CompanyID = co.CompanyID

In this case, we go over the Company table just once, at the start, and JOIN it with the Customertable. From then on, we can select the values we need (co.CompanyName) more efficiently.

SQL Tuning: Select Sparingly

One of my favorite SQL optimization tips is to avoid SELECT *! Instead, you should individually include the specific columns that you need. Again, this sounds simple, but I see this error all over the place. Consider a table with hundreds of columns and millions of rows—if your application only really needs a few columns, there’s no sense in querying for all the data. It’s a massive waste of resources. (For more issues, see here.)

For example:

SELECT * FROM Employees

vs.

SELECT FirstName, City, Country FROM Employees

If you really need every column, explicitly list every column. This isn’t so much a rule, but rather, a means of preventing future system errors and additional SQL performance tuning. For example, if you’re using an INSERT... SELECT... and the source table has changed via the addition of a new column, you might run into issues, even if that column isn’t needed by the destination table, e.g.:

INSERT INTO Employees SELECT * FROM OldEmployees

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

To avoid this kind of error from SQL Server, you should declare each column individually:

INSERT INTO Employees (FirstName, City, Country)
SELECT Name, CityName, CountryName
FROM OldEmployees

Note, however, that there are some situations where the use of SELECT * could be appropriate. For example, with temp tables—which leads us to our next topic.

SQL Tuning: The Wise Use of Temporary Tables (#Temp)

Temporary tables usually increase a query’s complexity. If your code can be written in a simple, straightforward manner, I’d suggest avoiding temp tables.

But if you have a stored procedure with some data manipulation that cannot be handled with a single query, you can use temp tables as intermediaries to help you to generate a final result.

When you have to join a large table and there are conditions on said table, you can increase database performance by transferring your data in a temp table, and then making a join on that. Your temp table will have fewer rows than the original (large) table, so the join will finish faster!

The decision isn’t always straightforward, but this example will give you a sense for situations in which you might want to use temp tables:

Imagine a customer table with millions of records. You have to make a join on a specific region. You can achieve this by using a SELECT INTO statement and then joining with the temp table:

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

(*Note: some SQL developers also avoid using *SELECT INTO* to create temp tables, saying that this command locks the tempdb database, disallowing other users from creating temp tables. Fortunately, this is *fixed in 7.0 and later.)

As an alternative to temp tables, you might consider using a subquery as a table:

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

But wait! There’s a problem with this second query. As described above, we should only be including the columns we need in our subquery (i.e., not using SELECT *). Taking that into account:

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

All of these SQL snippets will return the same data. But with temp tables, we could, for example, create an index in the temp table to improve performance. There’s some good discussion here on the differences between temporary tables and subqueries.

Finally, when you’re done with your temp table, delete it to clear tempdb resources, rather than just wait for it to be automatically deleted (as it will be when your connection to the database is terminated):

DROP TABLE #temp

SQL Tuning: “Does My Record Exist?”

This SQL optimization technique concerns the use of EXISTS(). If you want to check if a record exists, use EXISTS() instead of COUNT(). While COUNT() scans the entire table, counting up all entries matching your condition, EXISTS() will exit as soon as it sees the result it needs. This will give you better performance and clearer code.

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
    PRINT 'YES' 

vs.

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
    PRINT 'YES'

SQL Performance Tuning With SQL Server 2016

As DBAs working with SQL Server 2016 are likely aware, the version marked an important shift in defaults and compatibility management. As a major version, it, of course, comes with new query optimizations, but control over whether they’re used is now streamlined via sys.databases.compatibility_level.

SQL Performance Tuning (in the Office)

SQL database administrators (DBAs) and developers often clash over data- and non-data-related issues. Drawn from my experience, here are some tips (for both parties) on how to get along and work together effectively.

Database Optimization for Developers:

  1. If your application stops working suddenly, it may not be a database issue. For example, maybe you have a network problem. Investigate a bit before you accuse a DBA!
  2. Even if you’re a ninja SQL data modeler, ask a DBA to help you with your relational diagram. They have a lot to share and offer.
  3. DBAs don’t like rapid changes. This is natural: they need to analyze the database as a whole and examine the impact of any changes from all angles. A simple change in a column can take a week to be implemented—but that’s because an error could materialize as huge losses for the company. Be patient!
  4. Do not ask SQL DBAs to make data changes in a production environment. If you want access to the production database, you have to be responsible for all your own changes.

Database Optimization for SQL Server DBAs:

  1. If you don’t like people asking you about the database, give them a real-time status panel. Developers are always suspicious of a database’s status, and such a panel could save everyone time and energy.
  2. Help developers in a test/quality assurance environment. Make it easy to simulate a production server with simple tests on real-world data. This will be a significant time-saver for others as well as yourself.
  3. Developers spend all day on systems with frequently-changed business logic. Try to understand this world being more flexible, and be able to break some rules in a critical moment.
  4. SQL databases evolve. The day will come when you have to migrate your data to a new version. Developers count on significant new functionality with each new version. Instead of refusing to accept their changes, plan ahead and be ready for the migration.

Improving Query Performance with Database Indexing

#sql #web-development

What is GEEK

Buddha Community

SQL Database Performance Tuning for Developers
Cayla  Erdman

Cayla Erdman

1594369800

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Models for SQL exist. In any case, the SQL that can be utilized on every last one of the major RDBMS today is in various flavors. This is because of two reasons:

1. The SQL order standard is genuinely intricate, and it isn’t handy to actualize the whole standard.

2. Every database seller needs an approach to separate its item from others.

Right now, contrasts are noted where fitting.

#programming books #beginning sql pdf #commands sql #download free sql full book pdf #introduction to sql pdf #introduction to sql ppt #introduction to sql #practical sql pdf #sql commands pdf with examples free download #sql commands #sql free bool download #sql guide #sql language #sql pdf #sql ppt #sql programming language #sql tutorial for beginners #sql tutorial pdf #sql #structured query language pdf #structured query language ppt #structured query language

Ruth  Nabimanya

Ruth Nabimanya

1621850444

List of Available Database for Current User In SQL Server

Introduction

When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current logged user in SQL Server

Get the list of database
Conclusion

#sql server #available databases for current user #check database has access #list of available database #sql #sql query #sql server database #sql tips #sql tips and tricks #tips

Ruth  Nabimanya

Ruth Nabimanya

1620633584

System Databases in SQL Server

Introduction

In SSMS, we many of may noticed System Databases under the Database Folder. But how many of us knows its purpose?. In this article lets discuss about the System Databases in SQL Server.

System Database

Fig. 1 System Databases

There are five system databases, these databases are created while installing SQL Server.

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
Master
  • This database contains all the System level Information in SQL Server. The Information in form of Meta data.
  • Because of this master database, we are able to access the SQL Server (On premise SQL Server)
Model
  • This database is used as a template for new databases.
  • Whenever a new database is created, initially a copy of model database is what created as new database.
MSDB
  • This database is where a service called SQL Server Agent stores its data.
  • SQL server Agent is in charge of automation, which includes entities such as jobs, schedules, and alerts.
TempDB
  • The Tempdb is where SQL Server stores temporary data such as work tables, sort space, row versioning information and etc.
  • User can create their own version of temporary tables and those are stored in Tempdb.
  • But this database is destroyed and recreated every time when we restart the instance of SQL Server.
Resource
  • The resource database is a hidden, read only database that holds the definitions of all system objects.
  • When we query system object in a database, they appear to reside in the sys schema of the local database, but in actually their definitions reside in the resource db.

#sql server #master system database #model system database #msdb system database #sql server system databases #ssms #system database #system databases in sql server #tempdb system database

Joe  Hoppe

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Fredy  Larson

Fredy Larson

1595059664

How long does it take to develop/build an app?

With more of us using smartphones, the popularity of mobile applications has exploded. In the digital era, the number of people looking for products and services online is growing rapidly. Smartphone owners look for mobile applications that give them quick access to companies’ products and services. As a result, mobile apps provide customers with a lot of benefits in just one device.

Likewise, companies use mobile apps to increase customer loyalty and improve their services. Mobile Developers are in high demand as companies use apps not only to create brand awareness but also to gather information. For that reason, mobile apps are used as tools to collect valuable data from customers to help companies improve their offer.

There are many types of mobile applications, each with its own advantages. For example, native apps perform better, while web apps don’t need to be customized for the platform or operating system (OS). Likewise, hybrid apps provide users with comfortable user experience. However, you may be wondering how long it takes to develop an app.

To give you an idea of how long the app development process takes, here’s a short guide.

App Idea & Research

app-idea-research

_Average time spent: two to five weeks _

This is the initial stage and a crucial step in setting the project in the right direction. In this stage, you brainstorm ideas and select the best one. Apart from that, you’ll need to do some research to see if your idea is viable. Remember that coming up with an idea is easy; the hard part is to make it a reality.

All your ideas may seem viable, but you still have to run some tests to keep it as real as possible. For that reason, when Web Developers are building a web app, they analyze the available ideas to see which one is the best match for the targeted audience.

Targeting the right audience is crucial when you are developing an app. It saves time when shaping the app in the right direction as you have a clear set of objectives. Likewise, analyzing how the app affects the market is essential. During the research process, App Developers must gather information about potential competitors and threats. This helps the app owners develop strategies to tackle difficulties that come up after the launch.

The research process can take several weeks, but it determines how successful your app can be. For that reason, you must take your time to know all the weaknesses and strengths of the competitors, possible app strategies, and targeted audience.

The outcomes of this stage are app prototypes and the minimum feasible product.

#android app #frontend #ios app #minimum viable product (mvp) #mobile app development #web development #android app development #app development #app development for ios and android #app development process #ios and android app development #ios app development #stages in app development