SQL Question – Use Case Statement or Cursors?

Are you split over whether to use a CASE statement or CURSORS in your SQL code?

torn_photo

This article will help clarify your dilemma.

First of all let’s agree that both methodologies are useful for performing actions on a database table based on processing one SQL data row at a time.

Now let’s look at the main characteristics of each methodology.

CURSORS

Looks more complicated. Support costs are higher.

Best when working with multiple tables and complicated algorithms.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 45 seconds.

cursor_sql

CASE STATEMENT

Looks simpler.  Easier to maintain.

Best with select and where clauses used on a single table.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 137 seconds.

case_sql

CONCLUSION

Each methodology has its advantages and each can help you get where you are going.

Good SQL, good night.

Kelley’s Software Installation & Upgrade Rules

Recently a site was performing a software upgrade over the weekend.  No big deal, everyone thought, because this happens thousands of times across the world every day.

stoprobocallsYours truly was on call to support the site if any issues arose.

Needless to say they called Sunday afternoon after message traffic had gotten stuck in their queues for several hours and queue refreshes had not made a difference.

What I learned about the site was amazing.  They had performed not one, not two, but three software upgrades all at once.  They only backed up the system at the beginning.  They were the first site to go to this release.

Well, this prompted me to draft the following list of rules.

  1. Never install release 1.0 of anything.
  2. Never be the first site for any release.
  3. Always read the release notes and review the installation instructions.
  4. Always hold a readiness review meeting to answer questions.
  5. Never start an installation or upgrade without a backup of both your database and your system.
  6. Never install or upgrade more than two releases at a time.
  7. Always backup your databases before your second upgrade.
  8. Optionally backup your system before your second upgrade.
  9. Always backup both your database and your system after the last release.
  10.  Always test the functionality mentioned in the release notes.
  11.  Always examine all the log files for errors.
  12.  Always hold a party when the smoke clears aka the system is stable.

Now I feel a whole lot better.  Creating this list was quite a catharsis.

Let me know if you agree or have something to add.

Sybase SQL Challenge – Sum Of Max Values

--------DATA in Cat_Tbl---------------------------
Cat1           Cat2          Value
a              e1            113
b              e1            14
a              e2            15
c              e3            13
a              e1            13
a              e2            11
c              e4            1

Sybase

PROBLEM:

For each distinct value in Cat1, user wants to find maximum of Values grouped by Cat2 and then sum them up. 

 

 

Step 1: User wants to extract the following rows for example where Cat1 = ‘a’.

Cat1 Cat2 Value 
a    e1   113 
a    e2   15 
a    e1   13 
a    e2   11

Step 2: User wants to extract maximum corresponding to Cat1 and Cat2:

Cat1 Cat2 Value 
a    e1   113 
a    e2   15

Step 3: User wants to sum the Values for Cat1.

Cat1 SumOfValueColumn 
a    128 
b    14 
c    14

stackoverflow

 

 

 

 

SOLUTION 1 USING TEMPORARY TABLE #Max_Cat_Tbl

select Cat1, Cat2, max(Value) Maxvalue 
into #Max_Cat_Tbl from Cat_Tbl 
group by Cat1, Cat2 order by 1, 2 
select Cat1, sum(Maxvalue) 
from #Max_Cat_Tbl 
group by Cat1 
order by Cat1 
drop table #Max_Cat_Tbl 
go

SOLUTION 2 USING VIEW Max_Cat_Tbl_View

create view Max_Cat_Tbl_View 
( Cat1, Cat2, Maxvalue ) as 
select Cat1, Cat2, max(Value) 
from Cat_Tbl 
group by Cat1, Cat2 
select Cat1, sum(Maxvalue) 
from Max_Cat_Tbl_View 
group by Cat1 
order by Cat1 
go

Good SQL, good night.

How do you catch a log suspend error in Sybase?

Sybase

You have two issues.

One is the UNIX/Linux script does not detect errors correctly.

Secondly the transaction log is filling up frequently.

First the script needs to have the standard output AND the error output go to your log file. When you use the > it is using file descriptor 1. When you want to capture error output you need to use 2> for file descriptor 2. So the command would look like the following.

isql    > error.log    2> error.log

Better yet, use this.

isql    2>&1    > error.log

The previous command says to have file descriptor 2 go to file descriptor 1 which goes to a log file.

Now to detect errors, look for “Msg” which all Sybase errors have in front by using the grep command.

stackoverflow

 

 

 

 

Secondly to resolve the transaction log space, you need to grow the size of the log which should be on its own device separate from data. Then you need to set the threshold to dump aka flush the completed transactions to a disk file automatically. You need to investigate the following commands to do this.

alter database, sp_helpthreshold, sp_addthreshold, sp_thresholdaction

Good SQL, good night.

Does Sybase support string types that it doesn’t right trim?

Yes.

Sybase

For char columns to be fixed length, you must use the not null attribute.

For variable length columns, use varchar or char with a null attribute.

Then to measure the real data size including trailing spaces use the datalength function not the len function nor the charlength function.

Good SQL, good night.

stackoverflow

Sybase Functionality

QUESTION:

Does anybody know if Common Table Expressions and User-defined functions (not from Java) are supported in Sybase?

ANSWER:

Microsoft SQL Server is based on Sybase 7.0. Sybase may not have Common Table Expressions (CTE) and User-Defined Functions, but there are equivalent ways to do the same thing.

For example CTE can be done either in nested queries or via temp tables using a number sign (#) in front of the table name.

For User Defined Functions, create a stored procedure with simple SQL code and call it via the exec function for example “exec my_sql_code”. This allows nesting of stored procedures.

Good SQL, good night.

Reverse Engineer Create Table Schema for Sybase or SQL Server

QUESTION:  Is there any way to fetch the create table schema including the textual representation of actual Sybase datatypes (instead of the number)?

ANSWER: Yes

Sybase Query Analyzer – showplan

QUESTION:

I’m querying Sybase database from C# code. Is there any good query analyzer to understand the exact queries being generated like the Query Analyzer for Sql Server?

ANSWER:

You can set some Sybase variables to see the Query Plan such as the following:

set showplan  on
set statistics io on
set statistics time on
exec my_proc (my_arg1, my_arg2, ...)
go

I have put this into a shell script to pass the command line arguments including stored procedure name and parameters.

isql  -Uxxx   -Pyyy   <<-_EOF_
set showplan  on
set statistics io on
set statistics time on
exec $*
go
_EOF_

You use this to see if there are any table scans which should be avoided at all costs.

Good SQL, good night.

Sybase Error Codes

Problem: Need to distinguish between Sybase RDBMS related exceptions and user data exceptions .

 

Solution:  Since Sybase uses error numbers less than 2000, you can choose to use numbers larger than 3000. You can control the returned error code by using the raiserror function. Yes that is not a misspelling.

Note: Sybase with many of its functions, tried to save keystrokes by eliminating duplicate characters. Here Sybase did not allow double “e”s but left the double “r”s in. Oh well.

For more info see http://stackoverflow.com/questions/7482383/distinguish-sybase-expections-types

Good SQL.  Good night.

Stuck In The Middle of Middleware?

Have you ever felt stuck with something?

stuckThere have been products in the software industry that have been a little less than spectacular.  For instance, Motif was supposed to be the new holy grail for developing graphical user interfaces (GUIs).  (I know I am showing my age.)  But it was slow coming up to the screen.  Why?  Because the Motif designers, decided to load everything into memory at start up.  Even rarely used functions.

Now comes middle ware products.  And guess what?  The designers of those products decided to load everything into memory upon start up.  You know what happened?  Yep, they were slow at start up.  You would think developers would learn from past mistakes.

But in addition, middle ware products were bogged down because the functions stayed in memory.  That is because they were designed and tested for middle sized problems.  Yes the name for these products was “Middle Sized Ware”.  But marketeers decided the name limited the market for sales, so they dropped the “sized” part.

So why don’t software companies load functions into memory “as they are needed”?

So why aren’t software systems designed in case they are used by a thousand users?

It is because software companies are in a hurry and want to keep costs down.  So they hire cheap, inexperienced developers.

The solution is to hire a few experienced people (not all) and take some time to design a killer app like Steve Jobs did.  Nobody ever said they got stuck with an iPhone.