February 22, 2012 at 3:25 am
While i am executing the same query using SP,logical reads goes very high (About -80k). But when i use using the select query logical read are only 2k.
I am attaching script for both including Function that was used by SP as well.
Any help would be greatly appreicate.
Using SP :
CREATE PROCEDURE [dbo].[s_PowerProjects_getRecentUpdates_]
-- Add the parameters for the stored procedure here
@keyPlantOperatingStatusExt varchar (max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select distinct
PowerPlant = rtrim(x0_0.PowerPlant),
x5_0.KeyInstn,
x0_1.KeyPowerPlant,
x0_1.PlantDevelopmentCapacity,
x9_0.KeyEnergyPlantType,
x0_0.KeyPrimeMoverParent,
COALESCE(x3_0.State, x3_0.ForeignProvince) 'State/Province',
x0_1.EstimatedPlantCompletionYear,
x1_0.KeyPlantDevelopmentEvent,
x0_1.PlantDevelopmentLastVerified,
x0_1.ConstrCostEstHigh,
x0_1.PlantCostEstimateSource,
x1_0.KeyPlantProject
INTO #RecentUpdates
from ObjectViews.dbo.PowerPlant x0_0
inner join ObjectViews.dbo.EnergyPlantProject x0_1 on x0_0.KeyPowerPlant = x0_1.KeyPowerPlant
inner join ObjectViews.dbo.EnergyPlantProjectEvent x1_0 on x0_1.KeyPlantProject=x1_0.KeyPlantProject
inner join ObjectViews.dbo.PowerPlantSite x2_0 on x0_0.KeyPowerPlantSite=x2_0.KeyPowerPlantSite
inner join ObjectViews.dbo.Address x3_0 on x2_0.KeyAddress=x3_0.KeyAddress
INNER join ObjectViews.dbo.EnergyPlantOwner x5_0 on x0_0.KeyPowerPlant=x5_0.KeyPowerPlant
inner JOIN ObjectViews..EnergyPowerPlantFuelGroup x9_0 on x0_0.KeyPowerPlant=x9_0.KeyPowerPlant and x9_0.MostRecentSequence=1
where (x0_1.PlantDevelopmentLastVerified>=dateadd(dd,-30, GETDATE())
and (x5_0.KeyOwnershipStatus=0
and (x1_0.MostRecentSequence=1
and (x0_1.KeyPowerPlantDevelopmentType=0
and (x0_0.KeyPlantOperatingStatusExt in (SELECT number FROM iter_intlist_to_table(@keyPlantOperatingStatusExt))
and x3_0.Country <> 'MX' )))))
SELECT TOP 500
PowerPlant = rtrim(PowerPlant),
x8_0.KeyInstn,
x6_0.ShortName,
KeyPowerPlant,
PlantDevelopmentCapacity,
KeyEnergyPlantType,
KeyPrimeMoverParent,
COALESCE(State, ForeignProvince) 'State/Province',
EstimatedPlantCompletionYear,
KeyPlantDevelopmentEvent,
PlantDevelopmentLastVerified,
ConstrCostEstHigh,
PlantCostEstimateSource,
KeyPlantProject
FROM #RecentUpdates x8_0
left join ObjectViews.dbo.Instn x6_0 on x6_0.KeyInstn=x8_0.KeyInstn
order by PlantDevelopmentLastVerified desc, RTRIM(PowerPlant)
DROP TABLE #RecentUpdates
END
exec s_PowerProjects_getRecentUpdates_ @keyPlantOperatingStatusExt = N'0 1 8'
Using Select query.
select distinct
PowerPlant = rtrim(x0_0.PowerPlant),
x5_0.KeyInstn,
x0_1.KeyPowerPlant,
x0_1.PlantDevelopmentCapacity,
x9_0.KeyEnergyPlantType,
x0_0.KeyPrimeMoverParent,
COALESCE(x3_0.State, x3_0.ForeignProvince) 'State/Province',
x0_1.EstimatedPlantCompletionYear,
x1_0.KeyPlantDevelopmentEvent,
x0_1.PlantDevelopmentLastVerified,
x0_1.ConstrCostEstHigh,
x0_1.PlantCostEstimateSource,
x1_0.KeyPlantProject
INTO #RecentUpdates
from ObjectViews.dbo.PowerPlant x0_0
inner join ObjectViews.dbo.EnergyPlantProject x0_1 on x0_0.KeyPowerPlant = x0_1.KeyPowerPlant
inner join ObjectViews.dbo.EnergyPlantProjectEvent x1_0 on x0_1.KeyPlantProject=x1_0.KeyPlantProject
inner join ObjectViews.dbo.PowerPlantSite x2_0 on x0_0.KeyPowerPlantSite=x2_0.KeyPowerPlantSite
inner join ObjectViews.dbo.Address x3_0 on x2_0.KeyAddress=x3_0.KeyAddress
INNER join ObjectViews.dbo.EnergyPlantOwner x5_0 on x0_0.KeyPowerPlant=x5_0.KeyPowerPlant
inner JOIN ObjectViews..EnergyPowerPlantFuelGroup x9_0 on x0_0.KeyPowerPlant=x9_0.KeyPowerPlant and x9_0.MostRecentSequence=1
where (x0_1.PlantDevelopmentLastVerified>=dateadd(dd,-30, GETDATE())
and (x5_0.KeyOwnershipStatus=0
and (x1_0.MostRecentSequence=1
and (x0_1.KeyPowerPlantDevelopmentType=0
and (x0_0.KeyPlantOperatingStatusExt in (0,1,8)
and x3_0.Country <> 'MX')))))
SELECT TOP 500
PowerPlant = rtrim(PowerPlant),
x8_0.KeyInstn,
x6_0.ShortName,
KeyPowerPlant,
PlantDevelopmentCapacity,
KeyEnergyPlantType,
KeyPrimeMoverParent,
COALESCE(State, ForeignProvince) 'State/Province',
EstimatedPlantCompletionYear,
KeyPlantDevelopmentEvent,
PlantDevelopmentLastVerified,
ConstrCostEstHigh,
PlantCostEstimateSource,
KeyPlantProject
FROM #RecentUpdates x8_0
left join ObjectViews.dbo.Instn x6_0 on x6_0.KeyInstn=x8_0.KeyInstn
order by PlantDevelopmentLastVerified desc, RTRIM(PowerPlant)
February 22, 2012 at 4:01 am
If the procedure doesn’t have a query plan in the cache, it generate one according to the parameters that were passed and then stores that plan in the cache. The next time that it is activated, it uses the query plan that is in the cache (if it wasn’t flushed out by that time). Sometimes different parameters need different plans. Maybe this is the case with your procedure. Try to run it with recompile in order to make sure that it runs with a new query plan, that is suitable to the parameter that you are using.
Other factors that can modify the query plan are set options. The set ansi_nulls option is “stuck” with the procedure when it is created. You can check if your session settings when you run the procedure and when you run the query are the same. If they are, check that the ansi_nulls setting is the same as the one that was used when the procedure was created.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2012 at 4:19 am
It doesn't make any difference.
I have used both option.
Most probably the problem occurs from iter_intlist_to_table function.
Is there any way i can rewrite this function better way?
Please check the .SQL plan, so you will get better idea.
February 22, 2012 at 6:32 am
Can anyone please help with this?
It's little urgent....
February 22, 2012 at 10:49 pm
Any update?
February 22, 2012 at 11:10 pm
and (x0_0.KeyPlantOperatingStatusExt in (SELECT number FROM iter_intlist_to_table(@keyPlantOperatingStatusExt))
The above snippet is not present in your query. Please use the same and look at the execution plan.
Or other way, change it to hardcoded value in the sp and look at the execution plan. It may give you some headsup.
February 22, 2012 at 11:15 pm
It's already there in SP.
If i use that function then logical reads goes very high.
I am not sure but can you please help me with
creating a temporary table or temp variable with "number" . the first query could insert the list of @keyPlantOperatingStatusExt into this temporary table.
I am not familiar with SQL Server, so your help would be greatly appreciate.
I have already attached Query plan and function details in first post for your reference.
February 22, 2012 at 11:30 pm
SELECT number into #TempInts FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)
This is the easy way to create a temp table.Use this temp table(#TempInts) for the query.
Apart, I would recommand you to use Exists instead of IN.
February 22, 2012 at 11:46 pm
I have created temp table as below
Create PROCEDURE [dbo].[s_PowerProjects_getRecentUpdates_]
-- Add the parameters for the stored procedure here
@keyPlantOperatingStatusExt varchar(max)
as
Declare @mytable table
(
Number tinyint
)
Insert into @mytable (Number)
Select distinct KeyPlantOperatingStatusExt
from ObjectViews.dbo.PowerPlant
BEGIN
and then used within and clause as
and (x0_0.KeyPlantOperatingStatusExt in (SELECT number FROM @mytable) .
I am getting the result but data are not accurate.
I know i am missing something but cant recognize.
February 22, 2012 at 11:51 pm
sqlzealot-81 (2/22/2012)
SELECT number into #TempInts FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)This is the easy way to create a temp table.Use this temp table(#TempInts) for the query.
Apart, I would recommand you to use Exists instead of IN.
I have also modify the condition as you mention but getting error incorrect syntax near into. I think my query is fine.
and exists (SELECT number into #tempkey FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)
where x0_0.keyPlantOperatingStatusExt = @keyPlantOperatingStatusExt)
February 23, 2012 at 12:46 am
EasyBoy (2/22/2012)
sqlzealot-81 (2/22/2012)
SELECT number into #TempInts FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)This is the easy way to create a temp table.Use this temp table(#TempInts) for the query.
Apart, I would recommand you to use Exists instead of IN.
I have also modify the condition as you mention but getting error incorrect syntax near into. I think my query is fine.
and exists (SELECT number into #tempkey FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)
where x0_0.keyPlantOperatingStatusExt = @keyPlantOperatingStatusExt)
Try the below:
SELECT number into #TempInts FROM iter_intlist_to_table(@keyPlantOperatingStatusExt)
In the query,
and exists (SELECT 1 from #tempkey where number= x0_0.keyPlantOperatingStatusExt)
February 23, 2012 at 1:34 am
Thanks a lot for your help.
That works for me.
Greatly appreciate your help.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply