Tag Archives: database

SQL Question – Use Case Statement or Cursors?

Posted on by
Reply

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

Posted on by
Reply

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 Query Analyzer – showplan

Posted on by
2

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 SQL Question – Remove percentage sign from values in table

Posted on by
Reply

Solution:

If your DBMS does NOT have a “replace” function, you will have to use character substitution using several string functions.

Here is an example in Sybase and SQL Server.

UPDATE YourTable

SET YourColumn = stuff(YourColumn, patindex(YourColumn, ‘%’), 1, NULL)

This says find the pattern of ‘%’ in YourColumn. Now use that position number and replace aka stuff the character with NULL instead.

Good SQL, good night.

SQL Question – Conditional Join on Columns based on value of one column

Posted on by
Reply

Here is my answer.

Try a union
select * from tbla ta, tblb tb
where ta.column_1 = 1
and ta.column_2 = tb.column_2
union
select * from tbla ta, tblb tb
where ta.column_1 = 2
and ta.column_2 = tb.column_2
and ta.column_3 = tb.column_3
union
select * from tbla ta, tblb tb
where ta.column_1 = 3
and ta.column_2 = tb.column_2
and ta.column_3 = tb.column_3
and ta.column_4 = tb.column_4
the end

Here is the website to see other answers

http://stackoverflow.com/questions/10846936/sybase-conditional-join-on-columns-based-on-value-of-one-column/10866671#10866671

Needle.com and SQL Needler

Posted on by
Reply

Just read this article in USAToday regarding Needle.com an online customer service company that answers questions for various products.

http://www.usatoday.com/tech/columnist/talkingtech/story/2012-05-30/needle-talking-tech/55287900/1

I wonder if I should join the company and answer questions regarding SQL, Sybase, and database management.  But the pay looks to be rather low.

Hello world!

Posted on by
1

Thanks for visiting my site.  This is my world and I am sticking to it.  Hopefully you will find something of interest whether it is the latest advances in technology or something humorous to get your mind off the serious stuff.

Most of my life has evolved around computers working as a Software Engineer, implementing Database Management, and providing Project Management and Program Management.  But I am constantly trying to improve myself.

This website showcases many sides of me including:

Software-Database – extensive experience in database/SQL design, application development, and operations support.

Consulting – customer bonding to craft solutions to help them improve their jobs or lives via the firm Kelley Research.

Speaking – tailored public speeches using a vast background in Toastmasters International.

Publications – literary works including published stories, novellas and books in electronic or printed form through the company Noble Edwin Publishing.

Humor – attempts to make people laugh and get our minds off the serious stuff.

Futures – ideas of what will be forthcoming in society and technology.

Thanks for partaking.  Don’t forget to “Advance The Agenda”.  Have a great day!

Mike

PS Feel free to contact me and let me know what you need or what you are thinking.