Databases
StackOverflow2013
Postsdbo
POOptimize SQL view/query with self joins
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimize SQL view/query with self joins
    primarykey
    data
    text
    <p>I'm an extreme novice running SQL SERVER 2005. I have two tables that I'm putting together in a view. The first table just has 2 columns that go into the view. One column is an ID number and the other is name. The second table is where all the data is. The columns in that table are ID, Date, Hour, PriceType, MarketType, and Price. My goal is to have a view that filters PriceType to be a fixed value, and then takes the difference of Price when MarketType is DAM from the price with the MarketType is RTM. Right now that looks like the following....</p> <pre><code>SELECT dbo.nodes.commonname, dbo.nodes.node_id, da.pricedate, da.hour, rt.price AS rtm, da.price AS dam, da.price - rt.price AS dart FROM dbo.pnodes INNER JOIN dbo.pnode_prices AS da ON dbo.pnodes.node_id = da.pnode_id INNER JOIN dbo.pnode_prices AS rt ON dbo.pnodes.node_id = rt.pnode_id AND da.pricetype = rt.pricetype AND da.pricedate = rt.pricedate AND da.hour = rt.hour WHERE ( da.pricetype = 'LMP' ) AND ( da.markettype = 'DAM' ) AND ( rt.markettype = 'RTM' ) </code></pre> <p>This view doesn't take so long by itself. Sometimes I want to take it another step and have the difference between 2 views where I'll run a query that joins the above view and takes the difference of Price <code>where view1.CommonName=abc and view2.CommonName=xyz</code>. When I run that query it takes 10 times as long to run as it takes to run the simple view by itself. I assume that if I did things optimally that it wouldn't take 10x the time. Is there any low hanging fruit that I can do to improve this?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USKermit
    1. USDean MacGregor
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COAny extra information you can give would be useful - table structures, data volumes, example data, the second query, any indexes you have. Most likely correct indexing will speed this up.
      singulars
      1. POOptimize SQL view/query with self joins
      1. USLaurence
    2. COare the nested views a requirement?
      singulars
      1. POOptimize SQL view/query with self joins
      1. USTahbaza
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload