Alternative to self-join
-
I have a need to join a table to itself in a query. The problem is, I am trying to use an indexed view for this. Indexed views do not allow self joins. Do anyone have any alternatives to doing a self-join?
I appreciate the feedback..
A.J.
DBA with an attitude -
Perform 1 select as a derived table, and join to that ?
Select *
From YourView As v
Inner Join
(
Select *
From YourView
) dt
On ( v.JoinKey = dt.JoinKey )
-
Derived tables also not allowed in Indexed Views. That's yet another restriction of these oh-so-helpful-pain-in-the-ass-indexed-views.
A.J.
DBA with an attitude -
can you join to another view?
-
Nope either... the list of constraints is REALLLLLLLY lllllllllong
.Why do you want to do an indexed view for this task... and what is the task??
-
Indexed view idea is to speed up performance.
The task is extremely complex. Do you seriously want me to post a novel on what this nasty query is trying to accomplish?
A.J.
DBA with an attitude -
Well maybe you can post a short version
.Is it for reporting?
-
The idea is...
The table in question contains a list of Symbols that are associated to SymbolSetIDs.
I need to be able to lookup data from this (and other joined tables), based on a particular Symbol and associated SymbolSetID. However the data I need to retreive is a list of Symbols with different (or maybe the same) associated SymbolSetIDs.
- Each Symbol may or may not be different for each symbolsetID
- Different Symbols for different symbolsets may refer to the same "thing".
- Each "thing" is uniquely identified by a ThingID which is shared amonst common "things" with different symbols and symbolsetIDs.
- On top of all that, some parts of the WHERE clause are dependent upon the "Lookup Symbol and SymbolSetID", others are dependent upon the "Returned Symbols and SymbolSetIDs". Ditto for the SELECT clause.
- One table holds all Symbols with associated SymbolSetIDs.
Does that make any sense?
A.J.
DBA with an attitude -
Ya it makes sens, I think
.How is that gonna be used in the application?
-
The application is a cross-reference piece that cross-references symbols from different data providers (symbolsetIDs). In the end it's used to display stock market information / research to web clients.
A.J.
DBA with an attitude -
Ok, so you can't run the query once/how or whatever and insert the data in reporting table for fast access.
We'll need more info to suggest improvements on the query. But I think you're gonna have to use almost all the tricks in our bag to make this one run fast.
-
My bag of tricks has been on empty for a while with this issue.
A.J.
DBA with an attitude -
That still doesn't give us the schema, sample data and needed output. Plus execution plans could really be a big help here.
-
I am not going to take the time to supply all that. I know execution plans are good. Sometimes you just gotta say $#%@ it.
A.J.
DBA with an attitude -
I understand, must be on a tight deadline since you can't take 3 more minutes to Help us help you.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply