Database Optimization techniques for amateurs.

Posted by Zombies on Stack Overflow See other posts from Stack Overflow or by Zombies
Published on 2010-04-25T21:18:18Z Indexed on 2010/04/25 21:23 UTC
Read the original article Hit count: 152

Filed under:

sql-optimization

|

rdbms-agnostic

Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks.

And for the sake of not being too vague, let's say we are using a maintstream DB such as MySQL or Oracle, and that the DB will contain 500,000-1m or so records across ~10 tables, some with foreign key contraints, all using the most typical storage engines (eg: InnoDB for MySQL). And of course, the basics such as PKs are defined as well as FK contraints.

© Stack Overflow or respective owner

Related posts about sql-optimization

SQL Optimization Tips

as seen on C# Corner - Search for 'C# Corner'
This article lists some of the optimization tips for SQL Server development. >>> More
SQL SEVER – Finding Memory Pressure – External and Internal

as seen on SQL Authority - Search for 'SQL Authority'
Following query will provide details of external and internal memory pressure. It will return the data how much portion in the existing memory is assigned to what kind of memory type. SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressure FROM sys.dm_os_memory_clerks GROUP… >>> More
SQL SERVER – Quick Look at SQL Server Configuration for Performance Indications

as seen on SQL Authority - Search for 'SQL Authority'
Earlier I wrote SQL SERVER – Beginning SQL Server: One Step at a Time – SQL Server Magazine. That was the first article on the series of my real world experience of Performance Tuning experience. I have written second part the same series over here. Read second part over here: Quick Look at SQL Server… >>> More
SQL SERVER – Wait Stats – Wait Types – Wait Queues – Day 0 of 28

as seen on SQL Authority - Search for 'SQL Authority'
This blog post will have running account of the all the blog post I will be doing in this month related to SQL Server Wait Types and Wait Queues. SQL SERVER – Introduction to Wait Stats and Wait Types – Wait Type – Day 1 of 28 SQL SERVER – Signal Wait Time Introduction with Simple Example – Wait… >>> More
SQL SERVER – Convert IN to EXISTS – Performance Talk

as seen on SQL Authority - Search for 'SQL Authority'
In recent training one of the attendee asked if I can show simple method to convert IN clause to EXISTS clause. Here is the simple example. USE AdventureWorks GO -- use of = SELECT * FROM HumanResources.Employee E WHERE E.EmployeeID = ( SELECT EA.EmployeeID FROM HumanResources.EmployeeAddress EA WHERE… >>> More

Related posts about rdbms-agnostic

What resources will help me understand the fundamentals of Relational Database Design.

as seen on Stack Overflow - Search for 'Stack Overflow'
This are few of the fundamental database questions which has always given me trouble. I have tried using google and wiki but I somehow I miss out on understanding the functionality rather than terminology. If possible would really appreciate if someone can share more insights on this questions using… >>> More
Do database engines other than SQL Server behave this way?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a stored procedure that goes something like this (pseudo code) storedprocedure param1, param2, param3, param4 begin if (param4 = 'Y') begin select * from SOME_VIEW order by somecolumn end else if (param1 is null) begin select… >>> More
Database Optimization techniques for amateurs.

as seen on Stack Overflow - Search for 'Stack Overflow'
Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be very useful, thanks. And for the sake of not being… >>> More