Tuesday, October 4, 2011

SQL Optimizations

The past few weeks have seen me quite busy with a rewrite of our reporting application at work.


My first sprint for this rewrite composed of SQL stored procedure optimisations. 


We currently use a Sybase database so some of the below mentioned tips is specifically aligned to the sybase database. 
  1. If you are analyzing SQL use 'set noexec on' and if you are analysing 'set fmtonly on'.Also run 'set showplan on' to view the plan/flow of execution of your sql statment
  2. Replaced the UNIONs with UNION ALL - this was because the datasets that I was merge were already unique and thus the distinct funtionality available with the UNION function was not needed
  3. Updated necessary tables where there indexes missing - The filters in the WHERE and INNER JOIN statements were not indexed and thus full table scans were taking place
  4. Created functional indexes based on columns that are being filtered but are changed to a different type for filter purposes eg. datetime field called varis used as convert(var, date)  in filter. So a functional index is created for convert(var, date).
Hope the above tips gets you going on your optimization process.

Thursday, September 1, 2011

New look blog

It's been about over a year since I actively blogged about the happenings in my career but I finally thought I should come back from exile and get it going again.

A lot has changed and a lot has been learnt. I will be adding at least one post a week on what is currently going on in my career and also on what I am currently reading, coding and investigating.

I hope you like the new look blog layout and design.

Till next time,
Garth

Monday, July 26, 2010

9 Things you should be doing with your server

Cool server checklist. I found option one very useful.  A good way of setting up servers seamlessly with no problems and less manual intervention.

http://www.roundhousesupport.com/blog/9-things-you-should-be-doing-with-your-server-but-probably-arent

Friday, July 9, 2010

UX Myths or Not?

The difference between fact and opinion is a very thin line in terms of what we require from users, and what they require from us. To get a better understanding of these user experience myths visit http://uxmyths.com/.

Tuesday, June 29, 2010

MySql Optimisation Part 1

Writing SQL code is something I haven't done in a long time. I mean with ActiveRecord , ORM used in Rails, there is no need really.

Except for complex sql queries with unions and joins(left,right,inner etc). Here we find ourselves writing down lengthy queries returning data from multiple tables.

A few tips for improved speed of execution of these queries.

  1. Select only what you need eg. SELECT * is not what you need unless you need everything.
  2. Joins - be careful which joins you want to use, dont join to unused tables
  3. Add indexes on column you use in where clauses for filtering indexes on integers,booleans and numbers
  4. If you want to order the data do it in the query ,db computation faster than program computations
  5. Isolate long running queries 
       +----------------+--------+----------------------+---------+------------+---------+--------+-----------------+

        | table | type | possible_keys | key | key_len | ref | rows | Extra |

        +----------------+--------+----------------------+---------+------------+---------+--------+-----------------+

        | employee | ALL | NULL | NULL | NULL | NULL | 2 | where used |

        +----------------+--------+----------------------+---------+------------+---------+--------+------------------+

      So what are all these things?
  •  table shows us which table the output is about (for when you join many tables in the query)
  •  type is an important one - it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
  •  possible_keys Shows which possible indexes apply to this table
  •  key And which one is actually used
  •  key_len give us the length of the key used. The shorter that better.
  •  ref Tells us which column, or a constant, is used
  •  rows Number of rows mysql believes it must examine to get the data
  •  extra Extra info - the bad ones to see here are "using temporary" and "using filesort"
     6. Refactor SQL queries
         SELECT * FROM (SELECT province_id,id FROM regions WHERE province_id = 11) as D
         INNER JOIN stores ON D.id = stores.region_id

         instead of :

        SELECT * FROM regions
        INNER JOIN stores ON regions.id = stores.region_id
        WHERE regions.province_id = 11

   7. No calculations on index, index should be standalone

      Example:

      SELECT name FROM sales WHERE paid / 2 < 20

      rather ... WHERE paid < 20 * 2 with paid being indexed

   8. ANALYZE TABLE tablename - This stores the key distribution for the table
   9. Many deletes and updates leave gaps in the table (especially when you're using varchar,
       or in particular text/blob fields). This means there are more unnecessary disk I/O's, as the head needs
       to skip over these gaps when reading. Running OPTIMIZE TABLE tablename, solves this problem
  10. ANALYSE TABLE and OPTIMIZE TABLE should be statements that should be run fairly
        frequently   in any well looked after system.

Windows 8 Plans Leaked

Microsoft's plans for Windows 8 leaked. Thank the italians, at least they good for something apart from their football (FIFA 2010 first round, knocked out).

I'm not 100% sure the content is valid but it is certainly worth a look nonetheless to see what Microsoft has in store for the next version of Windows.

http://msftkitchen.com/2010/06/windows-8-plans-leaked-numerous-details-revealed.html

Temp Email Alias

Being a IT guy I enjoy finding/exploring new software to see how they compare to others that I currently use. The only issue that holds me back is the fact that my email address will be spammed.

As we know, most if not all the software, prior download,  requires us to register beforehand. I then follow the steps of registering , download the application and shortly after , which is fairly likely, find out I hate the application. So now I am stuck receiving the spam.Sigh. Why did I register?!

Well, no need to worry anymore. There  is a solution, tempalias. All you need to do is signup to this website and then you can create multiple temp alias email addresses. The temp alias email address will only be valid for a certain amount of days or messages. Thats correct, we are now in control of how long we are spammed for.

Now we can smile again.

http://webworkerdaily.com/2010/06/28/tempalias-temporary-anonymous-email-addresses/