如何为Sp下方创建视图和索引 [英] How Can I Create A View And Index For Below Sp

问题描述

大家好，

我有很多存储过程如果条件.. 你们中的任何一个人都可以解释我如何创建一个视图和索引下面的查询..如果它适用于以下查询我将在所有缓慢运行的存储过程中实现

 创建  proc  [dbo]。[UserRestaurantsCountrystateandCitys] 
 
  @ Userid   uniqueidentifier ， @ Country   varchar （  50 ）， @ State   varchar （< span class =code-digit> 50 ）， @ City   varchar （ 50 ），
  @ Entity   varchar  （ 50 ）， @ Action   varchar （ 50 ）， @ AppId   uniqueidentifier ， @LocationId   uniqueidentifier  
  as  
 开始 
 如果（@ Action = ' < span class =code-string> NoCountry'）
  begin  
  if （@ AppId = '  00000000-0000-0000-0000-000000000000' 和 @ LocationId = '  00000000-0000-0000-0000-000000000000' ）
 开始 
 如果（@ E ntity = ' 国家'）
 开始 
 选择  distinct  A.Country 来自地址A，AppDetails Ap，地点L 其中 L.AppId = Ap.AppId 和 L.AddressId = A.Id 和 A.Country！= ' ' 和 A.Country   不  null  
  end  
  else   if （@ Entity = ' 州'）
 开始 
 选择 < span class =code-keyword> distinct  A.State  as  Country  from  Address A ，AppDetails Ap，地点L 其中 L.AppId = Ap.AppId 和 L.AddressId = A.Id 
 和 A.Country=@Country 和 A.State！= ' ' 和 A.State   不  null  
 结束 
 结束 
 其他  if （@ AppId！= '  00000000-0000-0000-0000-000000000000'  和 @ LocationId = '  00000000-0000-0000-0000-000000000000'）
 开始 
  if （@ Entity = '  Country'）
 开始 
 选择  distinct  A .Country 来自地址A，AppDetails Ap，地点L 其中 L.AppId = Ap.AppId 和 L.AddressId = A.Id 和 A.Country！= '  ' 和 A.Country   不  null  
 和 Ap.AppId=@AppId 
  end  
  else   if （@ Entity = '  State'） 
 开始 
 选择  distinct  A.State  as 国家来自地址A，AppDetails Ap，地点L  where  L.AppId = Ap.AppId 和 L.AddressId = A.Id 
 和 A.Country=@Country 和 A.State！= ' < span class =code-string>' 和 A.State   不  null  和 Ap.AppId =@AppId 
  end  
  end  
 
  else   if （@ AppId！= '  00000000-0000-0000-0000-000000000000' 和 @LocationId！= '  00000000-0000-0000-0000-000000000000'）
 开始 
  if （@ Entity = '  Country'）
 开始 
 选择  distinct  A.Country 来自地址A，AppDetails Ap，地点L 其中 L.AppId = Ap.AppId 和 L.AddressId = A.Id 和 A.Country！= ' ' 和 A.Co untry   不  null  
 和 Ap.AppId=@AppId 和 L.LocationId=@LocationId 
  end  
  else   if （@ Entity = < span class =code-string>' 州'）
 开始 
 选择  distinct  A.State  as 国家来自地址A，AppDetails Ap，地点L 其中 L.AppId = Ap.AppId 和 L.AddressId = A.Id 
 和 A.Country=@Country 和 A.State！= ' '  和 A.State   不  null  和 Ap.AppId=@AppId 和 L.LocationId=@LocationId 
  end  
  end  
 
  end  
  end  

谢谢

解决方案

如果可能，请考虑用IDENTITY列替换uniqueidentifier列 - 请参阅

uniqueidentifier vs identity [ ^ ] - 这意味着更少读表时分页，因此性能更好。它还消除了测试空guid值的需要。



检查这些表上是否有任何主键用于ID - 检查 Pinal Dave的博客 [ ^ ]获取信息和建议。



除此之外，SP很难阅读和维护。考虑使用动态SQL - 在存储过程中构建动态SQL [ ^ ]



还可以考虑替换

 和 A.State！= < span class =code-string>' ' 和 A.State   不  null  

with

  WHERE  ISNULL（A.State，' '）！= ' ' 

和使用显式连接而不是使用where条件来链接表格

 来自地址A 
  INNER   JOIN 地点L  ON  L.AddressId = A.Id 
  INNER   JOIN  AppDetails Ap  ON  L.AppId = Ap.AppId 

你已经对空的Guid值进行了很多比较 - 考虑做类似的事情......

 声明  @ emptyGuid   uniqueidentifier  
  set   @ emptyGuid  = cast（强制转换（ 0    二进制） as   uniqueidentifier  
 
  if  @ AppId = @ emptyGuid 和 @ LocationId = @ emptyGuid 

credit [ ^ ]想法。

Hi All,
I have lot of stored procedures with if conditions..Can any one of you explain how can i create a view and index for below query..if it works for below query I'll implement in all my slowly running stored procedures

CREATE proc [dbo].[UserRestaurantsCountrystateandCitys]

@Userid uniqueidentifier,@Country varchar(50),@State varchar(50),@City varchar(50),
@Entity varchar(50),@Action varchar(50),@AppId uniqueidentifier,@LocationId uniqueidentifier
as
begin
if(@Action='NoCountry')
begin
 if(@AppId='00000000-0000-0000-0000-000000000000' and @LocationId='00000000-0000-0000-0000-000000000000')
 begin
 if(@Entity='Country')
 begin
 select distinct A.Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id and A.Country!='' and A.Country is not null
  end
 else if(@Entity='State')
 begin
 select distinct A.State as Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id
 and A.Country=@Country  and A.State!='' and A.State is not null
 end
 end
 else if(@AppId!='00000000-0000-0000-0000-000000000000' and @LocationId='00000000-0000-0000-0000-000000000000')
 begin
 if(@Entity='Country')
 begin
 select distinct A.Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id and A.Country!='' and A.Country is not null
 and Ap.AppId=@AppId
 end
 else if(@Entity='State')
 begin
 select distinct A.State as Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id
 and A.Country=@Country  and A.State!='' and A.State is not null and Ap.AppId=@AppId
 end
 end

 else if(@AppId!='00000000-0000-0000-0000-000000000000' and @LocationId!='00000000-0000-0000-0000-000000000000')
 begin
 if(@Entity='Country')
 begin
 select distinct A.Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id and A.Country!='' and A.Country is not null
 and Ap.AppId=@AppId and L.LocationId=@LocationId
 end
 else if(@Entity='State')
 begin
 select distinct A.State as Country from Address A,AppDetails Ap,Locations L where L.AppId=Ap.AppId and L.AddressId=A.Id
 and A.Country=@Country  and A.State!='' and A.State is not null and Ap.AppId=@AppId and L.LocationId=@LocationId
 end
 end

end
end

Thank you

解决方案

If possible, consider replacing your uniqueidentifier columns with IDENTITY columns - see
uniqueidentifier vs identity[^] - this will mean less paging when reading your table and therefore better performance. It also removes the need to test against the empty guid value.

Examine if you have any primary keys on these tables for the IDs - check Pinal Dave's blog[^] for information and suggestions.

As an aside the SP is very difficult to read and maintain. Consider using Dynamic SQL - Building Dynamic SQL In a Stored Procedure[^]

Also consider replacing

and A.State!='' and A.State is not null

with

WHERE ISNULL(A.State,'') != ''

and use explicit joins rather than using the where conditions to link tables E.g.

from Address A 
INNER JOIN Locations L ON L.AddressId = A.Id 
INNER JOIN AppDetails Ap ON L.AppId=Ap.AppId

You've got a lot of comparisons against the empty Guid value - consider also doing something like ...

declare @emptyGuid uniqueidentifier
set @emptyGuid = cast(cast(0 as binary) as uniqueidentifier

if @AppId=@emptyGuid and @LocationId=@emptyGuid

credit[^] for the last idea.

这篇关于如何为Sp下方创建视图和索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！