SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Not In Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/27/2013 :  09:45:31  Show Profile  Reply with Quote
Hi Everyone,
please refer the following query. it's bring the result after 15 seconds. please simplify this query.

select distinct top 15 '' as sel,RecordId,menuDesc,MenuID
from dbo.table_LIST('admin','xxx','zzz')
where len(RecordId) >3
and RecordId not in
(select distinct top 0 RecordId
from dbo.table_LIST('admin','xxx','zzz')
where len(RecordId) >3 order by RecordId) order by RecordId

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/27/2013 :  09:50:31  Show Profile  Reply with Quote
without seeing what table_LIST does its difficult to suggest. can you see execution plan and see what it suggests

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Dhanushkodi
Starting Member

India
21 Posts

Posted - 06/27/2013 :  10:04:24  Show Profile  Reply with Quote
table list is the function. Function is following.

ALTER FUNCTION [dbo].[table_LIST](@User varchar(100),@SuperUSer varchar(100),@InType varchar(100))
RETURNS @Result TABLE (RecordId varchar(200),menuDesc varchar(800),MenuId int)
AS
BEGIN
Declare @userId int
Declare @ModuleIdlike varchar(200)
set @userId=0

select @userId=inUserId from table1 where vcDelflag='False' and vcUSerName=@User
if @User=@SuperUSer
begin
if @InType ='material'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btMaterial=1
end
else if @InType='services'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btServices=1
end
else
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and len(vcRecordId) > 3 and btServices=1 and btMaterial=1
end
End
else
begin
declare ModuleId_List cursor for select distinct a.vcRecordId as modID from table2 a inner join table3 b on a.inModuleScreenId=b.vcModuleId and
b.vcdelFlag='False' and b.vcItemNatureType=@InType and (b.inUsrorGrpId=@userId and b.vcAuthorizationLevel='user' ) or
(b.inUsrorGrpId in(select distinct inSgroupId from table4 where inUserId=@userId
and vcItemNatureType=@InType and vcDelFlag='False') and b.vcAuthorizationLevel='group')

open ModuleId_List
fetch next from ModuleId_List into @ModuleIdlike
WHILE @@FETCH_STATUS=0
BEGIN
if @InType ='material'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btMaterial=1
end
else if @InType ='services'
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btServices=1
end
else
begin
insert into @Result select distinct vcRecordId,vcDesc,inModuleScreenId from table2 where vcdelFlag='False'
and vcRecordId is not null and vcRecordId like @ModuleIdlike+'%' and len(vcRecordId) > 3 and btMaterial=1 and btServices=1
end

FETCH NEXT FROM ModuleId_List into @ModuleIdlike
END
CLOSE ModuleId_List
DEALLOCATE ModuleId_List

end
RETURN

END





please give me a solution.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/27/2013 :  11:58:10  Show Profile  Reply with Quote
can you explain what you're trying to do inside udf? its quite difficult from your current code.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000