|
Joining 18 tables does not seem like a good idea. You need a data warehouse with denormalized design.
Maybe the Right outer join is a business requirement. But we should not assume that the database will just handle this!! Here comes our responsibility as developers, designers and DBAs to optimize the structure to satisfy business needs.
|
|
|
|
|
|
Basically that's not a hard select for a database, especially for db2. But if you have performance problems, something is wrong, no doubt about it. Perhaps you are lacking indices, logic in the query is wrong etc. It's not possible to say based on the query text what's the right corrective action (or I suspect actions) in your this case.
For the performance: I think you don't have to argue about it. Simply take the execution plan and go through it. It will reveal your bottlenecks in this query.
But most importantly, you should go through the logic of the database design. I don't say that it's bad since I'm not aware of the logic nor the requirements, but I doubt that there are problems.
Some observations:
- why are the tables having same names, with a running number at the end. Some kind of custom partitioning or is it reasoned
- seems that there are cumulated fields in the tables
- based on conditions this seems like an OLTP query but based on the select and from portions the modeling is more close to a star-model
- if the rows in classification tables are just 0/1 related to rentobj, perhaps use scalar subqueries in select
- this contains two different joining syntaxes (between car and rentobj). Use just one etc...
|
|
|
|
|
Hello,
Many thanks for the replies.
The problem is. This query does not work at all. The query returns with an error telling me that there is no temporary system tablespace which can handle this query (pagesize). Without the order by clause the query is working. Strange enough, no error number is returned, only the description (in german)
An object (rentobj) are assigned 12 categories like manufactor , type of object (truck,van,bus) etc. The category descriptions are stored in an own table. There can be only one category assigned to one object (1:1) But one object can have up to 12 categories, this is why there are 12 categories numbered from 1 til 12. Basically there are 12 joins on the same table category using 12 different fields in rentobj. This joins are used to retrieve the description of each category of the object.
I am not the DBA of this database so I can't change system tables space settings. And the design is done by somebody else.
The base table rentobj has more than hundred thousend records. I can't look at the execution plan. The query tool we are using does not allow that.
Many thanks again
modified on Wednesday, December 10, 2008 11:09 PM
|
|
|
|
|
dl4gbe wrote: Without the order by clause the query is working.
Obviously too much I/O and space usage.
dl4gbe wrote: I can't change system tables space settings
Concentrate on the execution plan. Find the bottlenecks and eliminate them. For starters create efficient indices if not present.
dl4gbe wrote: I can't look at the execution plan. The query tool we are using does not allow that
Have a chat with your DBA or whoever has the access to correct tools (if I remember correctly, graphical tool for db2 administration has a very nice execution plan visualization).
If you're not allowed to modify system settings, nor design, I hope that you still may create indexes (perhaps with you DBA). Otherwise you don't have tools to correct the problem.
|
|
|
|
|
I am building a query which returns a few calculated values. Some of these are as simple as ColA - ColB, while others are far more complex - but are often based on the result of a previous result. My question is, how can I reuse these fields that I have calculated? A lot of the time that I do these calculations, I also need to exclude some values in the WHERE clause, based on these answers.
Example:
SELECT ColA - ColB [Result]
FROM Table
WHERE (ColA - ColB) > 0
How could I calculate ColA - ColB just once - and use the result of that in the where clause or another column?
Thanks in advance
|
|
|
|
|
One way is to use inline views, like:
SELECT alias1.Result
FROM (SELECT (ColA - ColB) AS Result
FROM Table) alias1
WHERE alias1.result > 0
|
|
|
|
|
Thanks for the reply. I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis, but using inline views does the trick as well - so thanks.
|
|
|
|
|
toticow wrote: I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis
That won't be possible (and wouldn't be efficient) since sql is set based language. What you were hoping for would be record based and this would be close to cursor handling (and of course could be implemented in an procedure using cursors). But you don't want to use cursors since (as Mycroft in this forum said) they are evil 
Also have a look at other set based variations Ben Fair posted.
toticow wrote: so thanks
You're welcome.
|
|
|
|
|
In SQL Server 2005+ you can use CTEs (Common Table Expressions) to do the same kind of thing:
WITH Results AS
(
SELECT ColA - ColB [Result]
FROM TABLE
)
SELECT *
FROM Results
WHERE Result > 0
Or with a temp table like so:
SELECT ColA - ColB [Result]
INTO #temp
FROM TABLE
SELECT *
FROM #temp
WHERE Result > 0
DROP TABLE #temp
Or with a table variable like so:
DECLARE @temp TABLE (Result int)
INSERT @temp
SELECT ColA - ColB [Result]
FROM TABLE
SELECT *
FROM @temp
WHERE Result > 0
I prefer to use the CTE because I think it's less code and easily readable.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
That's true and leads to the same result. Writing inlines is just a (bad) habit that always comes first in mind. Guess it's because I've written them over fifteen years so I believe it's like teaching an old dog... 
|
|
|
|
|
Well, I can relate to that!
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Mika Wendelius wrote: comes first in mind
Probably more portable too?
|
|
|
|
|
PIEBALDconsult wrote: Probably more portable too?
I'd say so. Many DBMS support inline views.
|
|
|
|
|
In Oracle 9i the maximum length of a list used in an IN clause is 1000 records.
Does anyone know if it's still the same limit in version 10g or 11g?
A related question is what the limit is in SQLServer?
|
|
|
|
|
Jörgen Andersson wrote: In Oracle 9i the maximum length of a list used in an IN clause is 1000 records
In Oracle 1000 limitation is not for records, but only for comma separated values in the list. So you cannot pass more literal values for an IN-list than 1000.
Jörgen Andersson wrote: Does anyone know if it's still the same limit in version 10g or 11g?
At least in 10g this applies.
Jörgen Andersson wrote: A related question is what the limit is in SQLServer
In SQL Server I'm not aware of such limitation. The limitation you could hit is the maximum size of a sql statement which is 65,536 * network packet size.
Mika
|
|
|
|
|
Thanks!
Mika Wendelius wrote: In Oracle 1000 limitation is not for records, but only for comma separated values in the list. So you cannot pass more literal values for an IN-list than 1000
I know, I'll try to express myself better in the future.
Mika Wendelius wrote: At least in 10g this applies
Which means I'll have to keep on using temp tables. 
|
|
|
|
|
Jörgen Andersson wrote: Thanks!
You're welcome
Jörgen Andersson wrote: Which means I'll have to keep on using temp tables
That's one possibility, but since the the values for IN come from a subquery, you can use basically all the techniques what the database offers to create the result set (normal tables, temporary tables, xml tables, external tables etc etc).
|
|
|
|
|
Mika Wendelius wrote: hat's one possibility, but since the the values for IN come from a subquery, you can use basically all the techniques what the database offers to create the result set (normal tables, temporary tables, xml tables, external tables etc etc).
The data for the IN clause (or maybe rather a join) is coming from a .net datatable.
Is there an easier way than inserting it into a temptable?
|
|
|
|
|
Since I don't know the wholse situation, just throwing some ideas:
- Can you put the data into an xml format and in the subquery use xquery to fetch the data again
- if you're inserting data into the database using this IN operator, you could use arrays to pass the data to a procedure
- if you're selecting data you can do the same and then return ref cursor
- possibly you could use UDT's in the above scenarios
- you could create a function as pipelined, pass the data to that with an array and then use the return value of the function as a table in your subquery etc.
|
|
|
|
|
Thanks again.
I'll have a deeper look into this tomorrow. Eventually with another question...
|
|
|
|
|
hi
i have a update trigger that get fired when any column updated of the table
create TRIGGER trigger_Update_ABC
ON ABC
FOR Update
AS
declare @Id bigint
set @Id = (select Id from Inserted)
update ABC set ABC.Post = getdate() where Id =@Id
when i execute such sql quarry
Update ABC set ABC.Active = 1 where ABC.Id in(1,4,5,6,3,7)
then i got error
|
|
|
|
|
Triggers must *always* be written with the expectation that multiple rows will be affected. So SELECT ID from Inserted could return a series of INT not just one.
CREATETRIGGER trigger_Update_ABC ON ABC
FOR Update AS
UPDATE ABC
SET ABC.Post = GetDate()
WHERE ABC.ID IN (SELECT ID from inserted)
You should check to see if recursive triggers are set on your server, as this is an update trigger on ABC that updates ABC. If recursive triggers are enabled it could cause an infinite loop and the statement will fail.
Do some research into recursive triggers if this is enabled.
'Howard
|
|
|
|
|
than x Richards
my confusion is now much clear now
|
|
|
|
|
Full-text index tab is disabled and
I can only see partition schemes and partition functions under storage.
Help me out in this regard.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
