TR Forums

Thanks guys, it does seem like the two query idea is the best approach...
And I will look into MSDE...might as well since this is just in the exploratory phase. Data import is a big issue, but then again, it doesnt work out smoothly with Access either (importing excel spreadsheets into access definately doesnt work as well as it should)
Thanks again,

~Turkina
if coding_ethics = 0 then goto microsoft

thegleek wrote:

yeah well, i still probably would never use an IF or CASE statement within a sql query... it's just not in my coding ethics to do so...

So you've got something against writing efficient code?

The reason SQL constructs like case exist in the first place -- and are allowed within a select -- is to allow you to explicitly tell the DBMS what you are trying to accomplish. This allows the DBMS to optimize the query for efficient execution. Simply pulling all of the data and sorting through it using a procedural language can be horrendously inefficient in comparison.

This kind of optimization becomes even more important in multi-tier applications where the DBMS is running on a machine other than the one issuing the query, since it can reduce the amount of data sent over the network by orders of magnitude.

The classic case for using the CASE construct is to reduce the number of sub-queries used in statements. Don't know about other databases, but during our performance analysis on Oracle those little sub-queries look innocent enough, but then combined in the larger expression they become full table scans on the mini-results, and is seriously taking up the shared memory. Quite inefficient and unscalable.

As with everything, YMMV

I've used IF statements (similar to CASE) in a couple of places exactly to avoid sub-queries and they were a great help, however, they were simple cases to so I guess it's a very nice tool to be used with caution.

I've split off the completely off-topic posts that were in this thread to the Back Porch. Let's the keep the tech threads on topic.

Kevin

thegleek wrote:

Flying Fox wrote:

Edit: gleek, I think CASE is SQL-92, it can be quite new for even experienced DB developer to use. Last company I worked we just couldn't use it since bloody Oracle 8i did not have it yet. The performance of the CASE statement is quite phenominal once we switched to Oracle 9 and compared old and new queries.

yeah well, i still probably would never use an IF or CASE statement
within a sql query... it's just not in my coding ethics to do so...

i am thinking about trying that line at work.
"hey mr. bossman, i realize it would be much more efficient to do it your way.. but thats just not in my coding ethics."

heh. that would be hilarious. .. at least for a minute till i got fired.