Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage


  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats

    • 7,369,762 Views

Archive for June 12th, 2009

FreeStyle, anyone?

Posted by decipherinfosys on June 12, 2009

No, we are not talking about freestyle wrestling here 🙂  We are talking about Coke’s new RFID based freestyle dispensers.  The new dispensers will not only provide the consumers with more than 100 beverage choices, the real benefit of it is the business intelligence data that Coke will be able to gather based on the consumption of thosse beverages.  There are many more data points of interests as well – you can read more on it in the information week article here.

Posted in Business Intelligence, News, Technology | Leave a Comment »

Do not use “Select *”

Posted by decipherinfosys on June 12, 2009

How many times have you seen code where a “select *” is being used?  Chances are that if you have been in the industry for some time, you must have seen code that has a “select *” in it (either application code or DB code).  It is not a good choice for a variety of reasons –

Reason #1: Performance:

Consider these 2 queries:

Query 1: select * from tableA where colx = 5

vs say using

Query 2: Select Col1, Col2 from tableA where colx = 5

If there was a covered index on col5, col1 and col2, then in the case of the second query, it can just retrieve all the data from the index itself while the select * query will need to go to the data pages as well.  Since the intention is only to return Col1 and Col2, specifically mention which columns you need.

In addition to this performance issue, the un-necessary data transfer is another performance issue.  Just get the data that you need…nothing more, nothing less.

Reason #2: Plain simple code maintenance issue:

Say you have inherited a stored procedure which defines a table variable or a temp table (SQL Server lingo) and then does an “insert into #temp select * from…”.  If you add a new column that changes that then makes that select * to return an extra column, then this insert statement will fail.  In this case also, be explicit – mention which columns you are going to be inserting and where those columns are coming from.

Reason # 3: When using it in an EXISTS/NOT EXISTS condition:

Read Conor Cunningham’s post here.  You will see that when using an EXISTS/NOT EXISTS  condition, there is an advantage to use a “select 1” vs using a “select *”.  The reasons are well explained in Connor’s post.

Reason # 4: Column Dependency in Oracle 11g:

In Oracle 11g, the dependency mechanism has been extended down to the column level…so, what does that mean in relation to this post?  This means that if you are using explicit column names in your code, then the chances of the code getting invalidated and recompiled is less.

So, run through your code as a sanity check and see where all you have a “select *” in the code and then work on fixing it.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

sys.dm_os_performance_counters

Posted by decipherinfosys on June 12, 2009

We had briefly covered this DMV in one of our blog posts before – here.  This DMV can be used for tracking SQL Server related perfmon counters through plain simple T-SQL statements.  This DMV is a replacement for the sysperfinfo view from releases prior to SQL Server 2005 and for every object name, we get the counter name, instance name, counter type and counter value – essentially the same things that we get through perfmon.  Here is the BOL entry for this DMV which also covers what to do in case these counters are missing from your system (you can also read more on that here):

http://msdn.microsoft.com/en-us/library/ms187743(SQL.90).aspx

Now,  the data is good but data without analysis is useless.  So, how can we make use of this data?  Practically the same way as we have been making use of perfmon – scheduling those traces and capturing the data for doing trend analysis and analyzing the load characteristics on the system.  Pick the counters that you track in perfmon for SQL Server and schedule a job using SQL Server Agent which then takes the output based on this DMV and dumps it into a table.  Put the conditions in that job also to check for the thresholds or patterns and send out automatic alerts – in the end, having a SSRS report based off that summary table will be pretty useful for the production support DB teams.  Do remember that this only exposes the SQL Server counters and not the disk, network and other counters.

Before you jump into it, we would suggest to open up perfmon, go to that object name, the specific counter and hit Explain –

Perfmon_1

Once you have read it, play around with those counters for some time in perfmon to get a solid understanding of the different objects and their counters.  Once you have a good understanding, then you should move forward with creating a data collection table in the DBA database and dump the data from this DMV into that table and do your performance analysis, trend analysis and alerts.

An excellent resource to read more on this DMV is Greg Larsen’s post here.

References:

  • The BOL post and Greg Larsen’s posts mentioned in this article.

Posted in SQL Server | Leave a Comment »

FROM clause in the UPDATE statement

Posted by decipherinfosys on June 12, 2009

Yesterday, a friend asked why this statement was failing in Oracle:

UPDATE item_master
SET mf_item_number = gm.SKU
FROM
item_master im
JOIN group_master gm ON im.sku=gm.sku
JOIN Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerID
WHERE im.mf_item_number like 'STA%' AND gm.manufacturerID=34

Now, there are some other issues as well in this SQL (keeping the table relationships in mind) but the main thing to point out is that in ANSI standards, FROM is not allowed in the UPDATE statement.  That is an extension in T-SQL that is available to the users of SQL Server.  Take a look at our post which shows how to update a table with data from another table and it covers the big three RDBMS (Oracle, SQL Server and DB2 LUW):

https://decipherinfosys.wordpress.com/2007/01/31/update-data-in-one-table-with-data-from-another-table/

Also, this query can be re-written to remove the FROM clause and use an EXISTS condition – read up here on some general guidelines for making use of the EXISTS condition – it is not a panacea for performance issues so understand when it makes sense to use it.

References:

  • BOL entry on the Update statement – here.
  • Post by Microsoft MVP Hugo Kornelis – here.

Posted in Oracle, SQL Server | 1 Comment »