• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Query Help?

Can someone help me make this query more efficient:

 
with CTE
as
(
	select Distinct b.pvMake, c.pvModel, 
	Right(CAST(MIN(d.pvYear) as varchar(4)), 4) + '-' + 
	RIGHT(CAST(MAX(d.pvYear) as varchar(4)), 4) ModelYear
	FROM vwSelectPart a
	Inner Join vwSelectPartVehcileMakes b On b.partID = a.partID
	Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
	Inner Join vwSelectPartVehcileModelYears d On d.pvModelID = c.pvModelID
	Where pvMake = @Make
	And pvModel = @Model
	And (@PartID Is Null Or a.partID = @PartID)
	group by a.PartID, b.pvMake, c.pvModel
)
select @Results = IsNull((Select ModelYear From CTE C
                --Where pvMake = @Make
               --And pvModel = @Model
               For xml Path('')), '')
from vwSelectPart
group by PartID, CatID, PartName;

Open in new window


0
kevp75
Asked:
kevp75
  • 4
  • 2
2 Solutions
 
wdosanjosCommented:
Please check if this works for you. It eliminates table vwSelectPart from the query, as it does not seem to be required given that vwSelectPartVehcileMakes has partID already.

with CTE
as
(
	select Distinct b.pvMake, c.pvModel, 
	Right(CAST(MIN(d.pvYear) as varchar(4)), 4) + '-' + 
	RIGHT(CAST(MAX(d.pvYear) as varchar(4)), 4) ModelYear
	FROM vwSelectPartVehcileMakes b
	Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
	Inner Join vwSelectPartVehcileModelYears d On d.pvModelID = c.pvModelID
	Where b.pvMake = @Make
	And c.pvModel = @Model
	And (@PartID Is Null Or b.partID = @PartID)
	group by a.PartID, b.pvMake, c.pvModel
)
select @Results = IsNull((Select ModelYear From CTE C
                --Where pvMake = @Make
               --And pvModel = @Model
               For xml Path('')), '')
from vwSelectPart
group by PartID, CatID, PartName;

Open in new window

0
 
kevp75Author Commented:
I will give it a shot.  Also, I removed the 1st Group By clause as well
0
 
kevp75Author Commented:
sorry...make that the 2nd group by clause
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
wdosanjosCommented:
Here is another version optimizing the DISTINCT / GROUP BY's as you indicated:

with CTE
as
(
	select b.pvMake, c.pvModel, 
	Right(CAST(MIN(d.pvYear) as varchar(4)), 4) + '-' + 
	RIGHT(CAST(MAX(d.pvYear) as varchar(4)), 4) ModelYear
	FROM vwSelectPartVehcileMakes b
	Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
	Inner Join vwSelectPartVehcileModelYears d On d.pvModelID = c.pvModelID
	Where b.pvMake = @Make
	And c.pvModel = @Model
	And (@PartID Is Null Or b.partID = @PartID)
	group by b.pvMake, c.pvModel
)
select @Results = IsNull((Select ModelYear From CTE C
                --Where pvMake = @Make
               --And pvModel = @Model
               For xml Path('')), '')
from vwSelectPart;

Open in new window

0
 
kevp75Author Commented:
step ahead of ya :)

is there anything else that stands out?
0
 
SharathData EngineerCommented:
If there is no JOIN with vwSelectPart in the outer SELECT clause after CTE, you need not to query vwSelectPart.
;with CTE
as
(
	select b.pvMake, c.pvModel, 
	Right(CAST(MIN(d.pvYear) as varchar(4)), 4) + '-' + 
	RIGHT(CAST(MAX(d.pvYear) as varchar(4)), 4) ModelYear
	FROM vwSelectPartVehcileMakes b
	Inner Join vwSelectPartVehcileModels c On c.pvMakeID = b.pvMakeID
	Inner Join vwSelectPartVehcileModelYears d On d.pvModelID = c.pvModelID
	Where b.pvMake = @Make
	And c.pvModel = @Model
	And (@PartID Is Null Or b.partID = @PartID)
	group by b.pvMake, c.pvModel
)
select @Results = IsNull((Select ModelYear From CTE C
                --Where pvMake = @Make
               --And pvModel = @Model
               For xml Path('')), '')

Open in new window

0
 
kevp75Author Commented:
thanks for the help folks.   issue solved :) and the query runs much better
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now