完全一样的语句,为何Access运行出来,只需要1秒钟,而sqlserver用存储过程用了30秒,sql语句如下,请各位帮我看一下:access 的视图:
PARAMETERS username Text ( 255 );
SELECT *
FROM VISIT
reporter=@username
or customererpcode in (select customererpcode from customer where sales=','+username+',')
or visit.PL In (select pdesc from permission where ptype='PL' and username= username)
or visit.Office In (select pdesc from permission where ptype='OFFICE' and username= username )
ORDER BY [visitid] DESC;--sqlserver用存储过程
CREATE PROCEDURE dbo.visitlist
(
@username nvarchar(100)
)
AS
SELECT *
FROM VISIT
WHERE reporter=@username
or customererpcode in (select customererpcode from customer where sales=','+@username+',')
or visit.PL In (select pdesc from permission where ptype='PL' and username= @username)
or visit.Office In (select pdesc from permission where ptype='OFFICE' and username= @username )ORDER BY [visitid] DESC;
RETURN
GO
PARAMETERS username Text ( 255 );
SELECT *
FROM VISIT
reporter=@username
or customererpcode in (select customererpcode from customer where sales=','+username+',')
or visit.PL In (select pdesc from permission where ptype='PL' and username= username)
or visit.Office In (select pdesc from permission where ptype='OFFICE' and username= username )
ORDER BY [visitid] DESC;--sqlserver用存储过程
CREATE PROCEDURE dbo.visitlist
(
@username nvarchar(100)
)
AS
SELECT *
FROM VISIT
WHERE reporter=@username
or customererpcode in (select customererpcode from customer where sales=','+@username+',')
or visit.PL In (select pdesc from permission where ptype='PL' and username= @username)
or visit.Office In (select pdesc from permission where ptype='OFFICE' and username= @username )ORDER BY [visitid] DESC;
RETURN
GO
(
@username nvarchar(100)
)
AS
SELECT *
FROM VISIT
WHERE reporter=@username union
SELECT *
FROM VISIT
WHERE customererpcode in (select customererpcode from customer where sales=','+@username+',')union
SELECT *
FROM VISIT
WHERE visit.PL In (select pdesc from permission where ptype='PL' and username= @username)union
SELECT *
FROM VISIT
WHERE visit.Office In (select pdesc from permission where ptype='OFFICE' and username= @username )ORDER BY [visitid] DESC;