Need some advice! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Need some advice!

Author

Topic

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 14:03:41

I have run into an issue and need a little advice. I am in the middle of a complex java application and now have the need to create "database-independent" queries. As I am sure you all know, that means NO JOINS (damn oracle 8i). Well, until today I've designed all my search queries without that in mind. Everything is INNER and LEFT JOINS.

I've already taken a couple remedial attempts at recreating these queries without the use of joins by now converting to a long where clause with a select from multiple tables.

Sadly the resultset is not remotely the same.

Seeking advice from anyone who has been burdened with this task and perhaps some tip on how I can do an "old school" join?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 14:15:03

Oracle's outer join syntax is unique, so there's NO WAY you can support it and another database product with the same query. It's just not possible. The inner join syntax can be used, but the best you can do is to have a well-performing Oracle query (maybe) while the other products suffer (definitely) It's like trying to find a car engine that can run on both gas and diesel; you can't, and even if you could, the engine will run poorly regardless of the fuel.

I imagine your application creates SELECT statements on the fly. If that's the case, then you can develop a simple API-kind of intermediate step that identifies the tables, columns, and relations (joins) between the tables, and somehow specify the data source used (Oracle, SQL Server, Access, DB2, whatever) That code would then build the query statement on-the-fly with the appropriate syntax for that data source.

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 14:37:11

Rob, As always, thank you for the quick response!

I agree with you and have implemented more robust ways of managing the database independence issues in the past. Problem is, I don't have control over the project. The main architect is *positive* that I can recreate this type of db-indy sql statements without using the joins.

For example:

SELECT DISTINCT
t1.col1, t1.col2, t2.col1, t3.col1, t3.col2, t4.col1, t4.col2, t5.col1, t6.col1, t7.col1
FROM t1
INNER JOIN t2 ON (t2.col1 =
... etc (more joins and a where)...

Do you mean to say there is no way I can obtain this resultset *without* the use of joins? He mentioned subqueries and aliasing (performance...ugh)?

I know it's going to bite him in the end, but I have to have a good argument :) before I can definitively say "no".

Edited by - bglodde on 04/17/2002 14:42:34

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-17 : 15:11:42

You can get the result set you are looking for as long as it does not include outer joins. Oracle and SQL Server have unique syntax for outer joins. (+)= for Oracle and *= for SQL Server. So they will not work with the same code. Inner joins will work but will perform very poorly in SQL Server.

If you post an example query with ansi join syntax we might be able to help you convert it to the shorthand syntax.

Don't quote me on this, but I think that support for the sql shorthand syntax will be dropped in the next release. Let your *architect* know that he is asking for something that will soon be unsupported.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/17/2002 15:14:05

Edited by - efelito on 04/17/2002 15:14:47

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 15:17:09

Cannot thank you guys enough.

Here's the ugliness.

SELECT DISTINCT
TBLPSKU10.COMPANY, TBLPSKU10.SKU_SIZE, TBLPSKU10.SKU, TBLPSKU10.SKU_STYLE, TBLPSKU00.POS_DESC, TBLPSKU20.QTY_PER_RETAIL, TBLPSKU20.RETAIL, TBLDEPT10.DEPARTMENT, TBLDEPT10.DEPARTMENT_DESC, TBLMESR10.MEASURE, TBLMESR10.MEASURE_ABBREV, TBLPSKU20.EFF_DATE, TBLINVY00.INVENTORY_QTY
FROM TBLPSKU10
INNER JOIN TBLPSKU20 ON (TBLPSKU20.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU10.SKU = TBLPSKU20.SKU)
INNER JOIN TBLPSKU00 ON (TBLPSKU00.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU00.SKU_STYLE = TBLPSKU10.SKU_STYLE)
INNER JOIN TBLDEPT10 ON (TBLDEPT10.DEPARTMENT = TBLPSKU00.DEPARTMENT)
INNER JOIN TBLMESR10 ON (TBLPSKU10.SKU_MEASURE = TBLMESR10.MEASURE)
LEFT JOIN TBLINVY00 ON (TBLINVY00.COMPANY = TBLPSKU10.COMPANY AND TBLINVY00.SKU = TBLPSKU10.SKU)
WHERE TBLPSKU10.COMPANY = 1 AND TBLDEPT10.DEPT_ACCUM_LEVEL = 1
ORDER BY SKU ASC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 15:49:21

Perhaps it CAN be done, where one syntax *works* in every product, but as far as compromises go, it's the greatest compromise of all: in trying to please everyone, it will please no one!

I can't say it's a "good" argument (it is, but not the kind your PM is looking for), but it goes like this: if he's "positive" it can be done, why doesn't HE do it???? If YOU are his SQL expert, and YOU can't do it, who is HE to argue?

Subqueries do not act like joins; although you could program them to sort-of act like it, it wouldn't be any different from this format:

SELECT A.* FROM TableA A, TableB B
WHERE TableA.ID = CASE WHEN TableB.ID IS NULL THEN TableA.ID ELSE TableB.ID END

That would be equivalent to a LEFT JOIN, and *might* work for all database products. It will also generate the WORST possible query plan under EVERY database product. Anything that can optimize that to actually use an index would be a miracle worker. Notice how long that is to join only 2 tables together? And if you have additional WHERE constraints on the joined columns, you'd have to duplicate that CASE statement for each one!

Considering how different the optimizers are between Oracle and SQL Server, I imagine one of them just might be able to do it, but not both. I doubt that Oracle can use any optimizations it might have for the (+) join syntax, nor can SQL Server use LEFT JOIN optimizations, because neither syntax is used.

So as far as his suggestions about subqueries and aliasing, well, try that version out, and compare its performance to native Oracle/SQL Server/whatever-else-you-need-to-support versions of a LEFT JOIN. I could be totally wrong, and it could work very well for all of them, but I *HIGHLY* doubt it. Check every version's query plan in every product (if you can, SET SHOWPLAN ON in SQL Server, I can't remember the Oracle statement but I think it's called EXPLAIN) That's the best way to do it: show that the "universal" query works badly on every product, and that should change his mind.

In all honesty, I think this guy is just name-dropping SQL terms without understanding them. Do yourself a favor, have him EXPLAIN why building a specific SELECT statement for each product is so bad. Make HIM justify HIS position; after all, he's asking you to deliberately compromise query performance. Have HIM take the heat when the client complains. And in case Mr. I-read-SQL-For-Dummies-So-I-Know-My-Shit brings up the term "correlated subquery", let him know that it will perform EVEN WORSE.

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 16:03:19

Rob, I know the performance issue will be HUGE, but I almost have to display it just as you suggested ... to *convince*. I totally agree with you guys this is a horrible idea. My suggestion at this point is to create a SQLQuery object that encapsulates different versions of queries...thus optimized for supported dbs.

I'll post more as this adventure progresses.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-04-17 : 16:21:08

The example query cannot be written in a syntax that works correctly in both Oracle and SQL Server because the last join is an outer join and the syntax is simply different and not compatiable.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 04/17/2002 16:21:25

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 16:51:06

quote:
last join is an outer join

Jeff, Not sure I understand what you mean...I only use INNER and LEFT. The LEFT is only used because the results might be null. Are you saying there's an implicit OUTER that causes an incompatibility?

Edited by - bglodde on 04/17/2002 16:52:11

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 18:55:04

I'm not making any promises, but using the example I had before as a guideline, see if this works:

SELECT DISTINCT
TBLPSKU10.COMPANY,
TBLPSKU10.SKU_SIZE,
TBLPSKU10.SKU,
TBLPSKU10.SKU_STYLE,
TBLPSKU00.POS_DESC,
TBLPSKU20.QTY_PER_RETAIL,
TBLPSKU20.RETAIL,
TBLDEPT10.DEPARTMENT,
TBLDEPT10.DEPARTMENT_DESC,
TBLMESR10.MEASURE,
TBLMESR10.MEASURE_ABBREV,
TBLPSKU20.EFF_DATE,
TBLINVY00.INVENTORY_QTY
FROM TBLPSKU10,
TBLPSKU20,
TBLPSKU00,
TBLDEPT10,
TBLMESR10,
TBLINVY00
WHERE TBLPSKU20.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU10.SKU = TBLPSKU20.SKU
AND TBLPSKU00.COMPANY = TBLPSKU10.COMPANY AND TBLPSKU00.SKU_STYLE = TBLPSKU10.SKU_STYLE
AND TBLDEPT10.DEPARTMENT = TBLPSKU00.DEPARTMENT
AND TBLPSKU10.SKU_MEASURE = TBLMESR10.MEASURE
AND TBLINVY00.COMPANY = CASE WHEN TBLPSKU10.COMPANY Is Null THEN TBLINVY00.COMPANY
ELSE TBLPSKU10.COMPANY END
AND TBLINVY00.SKU = CASE WHEN TBLPSKU10.SKU Is Null THEN TBLINVY00.SKU
ELSE TBLPSKU10.SKU END
AND TBLPSKU10.COMPANY = 1 AND TBLDEPT10.DEPT_ACCUM_LEVEL = 1
ORDER BY SKU ASC

Ohhhhhhhhh man, you picked a doozie

There is NO WAY that will ever optimize on either system

Unless there are 10 rows max in each table, this thing won't run in your natural, hell, even in your artificially extended lifetime! This is the first time I'm GLAD that a query won't optimize!

What Jeff was saying is that using the WHERE syntax for joins does not allow you to mix a LEFT and INNER join type in SQL Server (using the obsolete *= syntax), but I do know that Oracle can handle it with its (+) operator. I don't think that'll be a problem here because the LEFT join is handled using the CASE statement (the red portion).

It should work in at least ONE of them without too much modification. Let me know if it doesn't, and I'll take a crack at fixing it up a bit.

EDIT: The original CASE statement had a mistake in it, I corrected it and it should work properly now.

Edited by - robvolk on 04/17/2002 22:07:03

bglodde
Starting Member

25 Posts

Posted - 2002-04-17 : 21:12:53

Ok, now I see what you're saying. Yes, it's a doozie, but this will pound home the importance of optimizing for each supported db. I'll work with that and see what I can come up with. You guys are the best!

bglodde
Starting Member

25 Posts

Posted - 2002-04-18 : 11:16:17

Response: CASE WHEN is not available in Interbase, which is one of the databases that is supported.

I am in SQL hell.

bglodde
Starting Member

25 Posts

Posted - 2002-04-22 : 12:03:57

Took awhile but I finally think I may have translated the JOINS down into basic SQL:

SELECT
TBLPSKU10.COMPANY, TBLPSKU10.SKU_SIZE, TBLPSKU10.SKU, TBLPSKU10.SKU_STYLE, TBLPSKU00.POS_DESC, TBLPSKU20.QTY_PER_RETAIL,
TBLPSKU20.RETAIL, TBLDEPT10.DEPARTMENT, TBLDEPT10.DEPARTMENT_DESC, TBLMESR10.MEASURE, TBLMESR10.MEASURE_ABBREV, TBLPSKU20.EFF_DATE,
TBLINVY00.INVENTORY_QTY
FROM
TBLPSKU00, TBLPSKU10, TBLPSKU20, TBLDEPT10, TBLMESR10, TBLINVY00
WHERE
TBLPSKU00.COMPANY = 1 AND
TBLPSKU00.SKU_STYLE = TBLPSKU10.SKU_STYLE AND
TBLPSKU10.COMPANY = 1 AND
TBLPSKU20.COMPANY = 1 AND
TBLPSKU10.SKU = TBLPSKU20.SKU AND
TBLPSKU10.SKU_MEASURE = TBLMESR10.MEASURE AND
TBLINVY00.SKU = TBLPSKU10.SKU AND
TBLDEPT10.DEPT_ACCUM_LEVEL = 1 AND
TBLDEPT10.DEPARTMENT = TBLPSKU00.DEPARTMENT
ORDER BY SKU

Just wanted to finish the thread and say *thanks* to you guys for entertaining my questions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources