Posts tagged " Eric Vanier "

ericvanier.com database consultant

12: Top 5 Skills of a Database Consultant

Posted by MySQL NEWS No Comment yet

Big Data has taken over the world by storm. And with focus narrowing to the ever-increasing amount of data, database consultants are more important than ever. Hence, there is an influx of job opportunities for database consultants.

But, with organizations looking out for database professionals, it is very important to understand the right set of skills for a qualified and efficient database consultant. Most of the functions of a Database Administrator are technical in nature. However, there is a set of skills that differentiate the good from the best. Today we look at the top five unique skills of a database consultant and understand their importance in the industry.

Problem Solving abilities

The most basic qualification for any database consultant is the ability to tackle complex problems. Now with such a vast amount of data, it is very important to recognize the patterns being formed. A good database consultant, with problem skills, will not only be able to understand the patterns but also create algorithms and process flows to optimize the output.

While technology has advanced over the years, it is very important to have a human intervention for problem-solving. This human intervention will further reduce any errors and ensure an efficient working of the database.

Data Visualization

ericvanier.com database consultant visualization
For a database consultant, it is very important to understand the data and create a visual representation of the same. This representation consists of charts, graphs and flows charts. But simple visualization lacks innovation and clarity. A quality of a good DBA is the ability to tell stories through the data.

In the modern day, stories are an essential part of our life. Scrolling through the internet or magazines, stories are the easiest way to communicate. This storytelling through data influences the ways products are created and work. An organization looking for a good database consultant should look for an individual who can inspire the User interface as well as the User experience of the product based on data.

Clear communication

While technical qualifications and a sound understanding of the industry are a must for any database consultant, it is equally important to have a set of soft skills as well. Communication is the key to any successful venture.

When it comes to database management, communication with the client is very important. This isn’t necessarily presentations and Excel sheets but a display of the output. This involves understanding the customer’s needs and presenting it accordingly. A website framework presented on a localhost preview will have a better impact than a PPT. But, it is the key skill of a database consultant to identify these methods and communicate effectively.

Managerial Capabilities

Managing a project is one of the most important skills for any industry. Even for database management, project management plays an important role. Organizations looking for a good database consultant should look out for some key management qualities as a skillset.

Not every individual is capable of all the technical aspects. This means work needs to be divided as per skillsets and capabilities. As a good database consultant, it is necessary to manage both the product as well as the client. Hence, management is a key skill that an organization should seek when looking for a database consultant.

The Hardware/ Software understanding

 ericvanier.com database consultant hardware

Database management is a combination of high-end hardware and software. As the amount of data increases, the requirements increase too. Common issues with data often arise and these are highly dependent on the hardware such as storage devices. The hardware is powered by compatible software.

An understanding of this combination is essential to a successful execution. As a database consultant, it is important to know the why and the how. So understanding how the system works both on the inside and the outside is what makes a perfect database consultant.

So, for any organization looking at a database consultant to take control of their database management system, the above-mentioned skills can help them find the perfect match. Moreover, a combination of all these will lead to an efficient and optimized working.

ericvanier.com SQL Performance Tuning

11: Getting Started with SQL Performance Tuning

Posted by MySQL NEWS No Comment yet

Sometimes you might have faced the issues of slow SQL performance due to some queries. This slowdown might have also impacted your operations to a major extent. And every time you might have felt the need to improve the performance of your SQL database. Well, you can easily improve the performance of your SQL database by tuning it.

 

What is SQL Performance Tuning?

SQL performance tuning, are several techniques used to ensure that the queries of the SQL database run in the fastest possible span to give maximum productivity.

 

Why is SQL Performance Tuning important?

While the smaller databases might not find SQL tuning significant, but when the databases are very huge ranging up to some Terabytes (TBs), then SQL performance tuning plays a major role in cutting down the query runtime resulting in a much-improved efficiency, making operations a lot faster than before.

Thus, for better performing fast operations, SQL performance tuning of databases is important.

 

How to do SQL Performance Tuning?

Here are a few ways you can use for doing SQL performance tuning of your database to it speed up:

  • Ensure a database server health check-up using DMVs

Use DMVs (Dynamic Management Views), to ensure server health and find the areas of slowdown. It gives the analytics and information about the active sessions and connections, letting you know the objects which consume most memory causing a slowdown.

 

You can use the below query to know the memory consumption of the given object, to identify areas of slowdown.

 

Select * from $System.discover_object_memory_usage

 

  • Avoid using SELECT * instead use SELECT fields

Using SELECT * (Select All) means calling of all the available data in the given database, which is often a stringent and more time-consuming process, often calling fields which are actually not required. Thereby, causing inefficiency and consuming more time.

 

If you use SELECT fields query that would mean you call only the required fields. This helps in significant optimization especially when the databases have a large number of rows and fields.

 

Avoid

SELECT *
FROM Users

 

Instead, use

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

 

  • Avoid using SELECT DISTINCT instead use SELECT for more fields

If you are using SELECT DISTINCT for removing duplicate queries, a lot of processing is required to call the query. As the SELECT DISTINCT query GROUPs all fields to create distinct results, it requires more processing time and can cause a slowdown when the database is significantly large.

 

Instead, if you opt to remove duplicate fields by calling SELECT for more fields, you tend to get unduplicated records without much processing, optimizing your database performance.

 

Avoid

SELECT DISTINCT FirstName, LastName, State
FROM Users

 

Instead, use

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

 

  • Prefer using Parameterized Queries

Using Parameterized Queries can improve the performance of the database, as they compile the query only once while executing the compiled plan multiple times. This can save time for recompiling each time, thereby improving performance.

 

Prefer

SELECT “User ID” FROM User WHERE “Sales ID” = ?

 

  • Prefer INNER JOIN to create joins over WHERE

Using WHERE to create joins result in a Cartesian of CROSS JOIN, where all the possible combinations of variables are created, causing a lot of unnecessary processing. This impacts more especially when the number of variables is more, like that in bigger databases.

 

While using INNER JOIN can be used to optimize the query as it does not create a Cartesian join.

Avoid

SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users, Sales
WHERE Users.UserID = Sales.UserID4

 

Instead, use

SELECT Users.UserID, Users.Name, Sales.LastSaleDate
FROM Users
INNER JOIN Sales
ON Users.UserID = Sales.UserID

 

 

  • Prefer using LIMIT for sample queries

You can limit the number of results when using sample queries. Using LIMIT will run only the given number of outcomes, instead of calling the complete data.

 

Prefer

SELECT FirstName, LastName, Address, City, State, Zip
FROM Users

LIMIT 20

 

The Bottom Line

SQL Performance Tuning is very helpful in optimizing database performance significantly by decreasing the processing time, especially for large databases, improving overall efficiency.

If you are stuck with performance improvement, get in touch with me. I will help you unlock the true performance of your database.

SQL Performance Analyzer

10: How to make the most of a SQL Performance Analyzer?

Posted by MySQL NEWS No Comment yet

With the sudden increase in data and its importance, database management systems have become an essential part of any organization, big or small. And in most cases, a DBA is deployed to set-up and maintain a database system. Post initial setup, a DBA spends most of the time monitoring the database. He also constantly works on optimizing the throughput.

But, running an analysis can be tough whenever there’s a change in action or execution. This could include any amendments in the SQL database such as upgrade or addition of indexes. Moreover, these processes can impact your SQL database performance. With the release of Oracle Database 11g, the Real Application Testing option was introduced.

A key feature of the Real Application testing is the SQL Performance Analyzer (SPA). It brought relief to the DBAs with its ability to cut down on the time and effort required to identify slow queries.

 

How does the SQL Performance Analyzer Work?

 

An SQL Performance Analyzer offers a granular view of how a change impacts the performance. An SQL performance Analyzer executes the SQL query in isolation and offers the before and after results. It then generates a report with a proper detailing of the effects of the statement on the workload.

In case of a regressive SQL statement, the Analyzer identifies it and repairs it before the execution of the changes to the database such as migration, upgradation or new index introduction. Moreover, running SPA prior to running the database replay feature is one of the best practices which save a lot of time and effort. The main aim is to identify and repair any regression. For this purpose, SPAs make use of the already present SQL Tuning Sets (STS). An STS contains predefined statements, execution information, and execution plans from the workload.

 

Where is a SQL Performance Analyzer used?

 

Some common scenarios which need an SQL Performance Analyzer are mentioned below. These are scenarios when changes are made to the database, potentially affecting its performance.

 

Database upgradation: Whenever a newer version of the database is installed, it leads to the installation of a new optimizer. This affects the system performance. The analyzer is then used to create the before and after view, pointing out to any regressions. This enables the DBA to re-write the required SQL queries prior to the change.

Hardware and Software change: At times, the system is upgraded, meaning additional CPUs or memory. Additionally, new operating systems can be installed. These changes to the system affect the SQL performance. In such scenarios, an SQL Performance Analyzer plays a key role in identifying any deterioration.

Patch Deployment: If a patch to fix issues with the performance is being deployed, it is advisable to run it through the SQL performance analyzer. It checks your production for any errors which might cause regression post-implementation to the workload.

Optimizer Statistics: The plans generated by the system rely heavily on the optimizers’ decisions. These decisions, in return, are affected by Optimizer statistics. Whenever new statistics are collected and the existing refreshed, it can cause an impact on the SQL performance. Hence, preventing any regressions the analyzer keeps a check on the statistics.

Changes to Schema: Whenever a database undergoes schema changes such as addition or change in the index, it will inevitably affect the SQL performance. As this can lead to deterioration of performance, it is important for the SQL Performance Analyzer to keep a check on the same.

The SQL Performance Analyzer process

 

The execution process of an SPA is very simple. After capturing the SQL workload, and storing it in an SQL test set, the DBA will set up the test target. This is where the analyzer comes into the picture.

An SQL Performance Analyzer task is then assigned to the test system. Using this, the DBA creates a pre-change task followed by the implementation of the change. Once the change has been implemented, a post-change task is created.

The two tasks are compared for any regressive queries or changes which might affect the performance of the SQL system. The analysis will guide the DBA on the optimization process. The process is repeated until the required performance statistics are obtained.

 

I have been working for almost 2 decades on Database optimizations. I can help you optimize your database to make your application perform better and save costs. Contact me to know more about how I can help you.

ericvanier.com-sql-join

09: Introduction to the SQL Join concepts

Posted by MySQL NEWS No Comment yet

Introduction to the SQL Join concepts

 

As the importance of data continues to grow exponentially, so does the amount of data. And data management is the most important factor for any organization, big or small. Hence, a very important part of normal functioning is a database management system.

 

Owing to the ever-increasing amount of data, the databases have multiple tables. It is highly unlikely that the user will refer to a single table. Multiple tables need to be accessed. In a relational database management system, a simple way to access multiple tables is through the use of joins. Let’s look at joins and its types with a few examples.

What is a Join?

Whenever data from different tables need to be accessed, joins are used to link data between different rows based on related columns of a database. A simple example would be retrieving different details of an individual such as phone number, address, and age against the Name of an individual.

Why are Joins important?

 

With the multiple data fields and types, working with multiple tables is necessary. Without the access to joins, it will be extremely difficult to retrieve multiple data entries. They also form the basis for a relational database. For any relational DBMS, it is very important to relate tables. Joins is the perfect solution to that problem.

What are the different types of SQL Joins?

ericvanier.com-database-schema-sql-join

 

Joins have multiple types of functionalities. And hence, different situations call for different types of joins. There is majorly four type of joins which are used. Let’s have a look at these and understand.

1.    Inner Join

Consider a Venn diagram with two different sets, namely, A and B. The intersection between the two sets A and B (A ∩ B) is the condition of the join. An inner join will consist of the selection of this common area. In technical terms, the inner join will return all the rows that fulfill the condition of the join from two or more tables.

Syntax:

SELECT columns
FROM A
INNER JOIN B
ON A.columnname = B.columnname

2.    Left  SQL Join

Also known as the left outer join, this is the join which focuses on the left-hand table. Consider a Venn diagram with A and B. The left join will include the output from entire set A and the intersection of A and B (A ∩ B). In a DBMS, a left join will retrieve all rows from the left side table and only those from the right which fulfill the criteria of the join.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname

Left Outer join without intersection: Another subtype of the left join is where the rows returned include all rows from the left side table which also meet the condition of the SQL join but none of the rows from the right side table fulfilling the conditions of the joins.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE B.columnname IS NULL

3.    Right SQL Join

Also known as right outer join, this conditional join focuses on the right side table. The right join returns all the rows from the right side table and the rows from the left side tables that fulfill the conditions of the join.

Syntax:

SELECT columns
FROM A
RIGHT OUTER JOIN B
ON A.columnname = B.columnname

Right Outer Join Without intersection: Similar to the left outer join without intersection, this join retrieves all the rows from tables on the right side except those fulfilling the condition of the joint from the left.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL

4.  Full Join

Also known as Full outer join, retrieves all the values from both the tables mentioned in the ON condition. Rows from tables on either side are retrieved where the join condition is not met. With a Venn diagram, it includes data from the entire set of A and B include the unions. This includes all NULL Values.

Syntax:

SELECT columns
FROM A
FULL JOIN B
ON A.columnname = B.columnname

Full Join without intersection: Similar to a full SQL join, this retrieves all the rows from either side of the tables mentioned in the ON condition where the join condition is not met. But, this excludes all the NULL Values, unlike a FULL OUTER JOIN.

Syntax:

SELECT columns
FROM A
LEFT OUTER JOIN B
ON A.columnname = B.columnname
WHERE A.columnname IS NULL
OR B.columnname is NULL

Conclusion

The above article explained the different types of SQL joins used in the relational DBMS environment. But, it also emphasizes on the fact that joins are an essential part of the RDBMS as they enable the relational part of the system by interconnecting different tables and their data.