SELECT TOP 10 [Type_ID], [Type_Name], [Type_Path], [Big_ID], [Small_ID] FROM [dbo].[Type_Pro] WHERE [Big_Id]=25 AND Type_ID not in (SELECT TOP 0 Type_ID FROM Type_Pro WHERE [Big_Id]=25 ORDER BY Big_id DESC)ORDER BY Big_id DESC以上是我数据存储过程执行出来的SQL语句
MS SQL 的内存是根据数据库中执行的SQL过程实际需要而分配的,并且在分配后不会主动释放内存,除非重启SQL服务(或者当SQL SERVER空闲不需要大量内存,而其他应该程序需要内存资源时:注:据MSDN中说明的,实际应用中没有发现会自动释放),才会释放内存资源。
USE [Management_sorft] GO /****** Object: StoredProcedure [dbo].[Type_Pro_SelectAll] Script Date: 01/05/2009 13:37:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[Type_Pro_SelectAll] @Big_Id nvarchar(10)=null, @Small_Id nvarchar(10)=null, @TopX NVARCHAR(2), @Pages nvarchar(4), @Bytitle nvarchar(50)=Big_id ASSET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @SQL NVARCHAR(1000) --declare @d datetime --set @d=getdate() set @SQL='SELECT TOP '+@TopX+' [Type_ID], [Type_Name], [Type_Path], [Big_ID], [Small_ID] FROM [dbo].[Type_Pro] '
if @Big_Id='' set @Big_Id='null' if @Small_Id='' set @Small_Id='null'IF @Big_Id = 'null' and @Small_Id = 'null' begin set @SQL=@SQL+ 'WHERE Type_ID not in (SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro ORDER BY '+@Bytitle+' DESC)' endIF @Big_Id !='null' and @Small_Id ='null' BEGIN set @SQL=@SQL+ 'WHERE [Big_Id]='+@Big_Id+' AND Type_ID not in (SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro WHERE [Big_Id]='+@Big_Id+' ORDER BY '+@Bytitle+' DESC)' end IF @Big_Id !='null' and @Small_Id !='null' BEGIN set @SQL=@SQL+ 'WHERE [Big_Id]='+@Big_Id+' and [Small_ID]='+@Small_Id+' AND Type_ID not in (SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro WHERE [Big_Id]='+@Big_Id+' and [Small_ID]='+@Small_Id+' ORDER BY '+@Bytitle+' DESC)' end
set @SQL=@SQL+'ORDER BY '+@Bytitle+' DESC'--PRINT @SQL PRINT @SQL EXECUTE(@SQL) --select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) --endregion 以上是我的存储过程并且我做了索引: USE [Management_sorft] GO/****** Object: Index [Big_Id] Script Date: 01/05/2009 13:52:13 ******/ CREATE NONCLUSTERED INDEX [Big_Id] ON [dbo].[Type_Pro] ( [Big_ID] ASC, [Small_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO 但是还是有点慢!刚开始执行速度很快,但是查询了几次之后速度就慢下来了!高手们:麻烦你们帮忙一下吧.我看了几天都找不出其它问题
[Type_ID],
[Type_Name],
[Type_Path],
[Big_ID],
[Small_ID]
FROM
[dbo].[Type_Pro] WHERE [Big_Id]=25 AND Type_ID not in
(SELECT TOP 0 Type_ID FROM Type_Pro
WHERE [Big_Id]=25 ORDER BY Big_id DESC)ORDER BY Big_id DESC以上是我数据存储过程执行出来的SQL语句
GO
/****** Object: StoredProcedure [dbo].[Type_Pro_SelectAll] Script Date: 01/05/2009 13:37:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Type_Pro_SelectAll]
@Big_Id nvarchar(10)=null,
@Small_Id nvarchar(10)=null,
@TopX NVARCHAR(2),
@Pages nvarchar(4),
@Bytitle nvarchar(50)=Big_id
ASSET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @SQL NVARCHAR(1000)
--declare @d datetime
--set @d=getdate()
set @SQL='SELECT TOP '+@TopX+'
[Type_ID],
[Type_Name],
[Type_Path],
[Big_ID],
[Small_ID]
FROM
[dbo].[Type_Pro] '
if @Big_Id='' set @Big_Id='null'
if @Small_Id='' set @Small_Id='null'IF @Big_Id = 'null' and @Small_Id = 'null'
begin
set @SQL=@SQL+ 'WHERE Type_ID not in
(SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro
ORDER BY '+@Bytitle+' DESC)'
endIF @Big_Id !='null' and @Small_Id ='null'
BEGIN
set @SQL=@SQL+ 'WHERE [Big_Id]='+@Big_Id+' AND Type_ID not in
(SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro
WHERE [Big_Id]='+@Big_Id+' ORDER BY '+@Bytitle+' DESC)'
end
IF @Big_Id !='null' and @Small_Id !='null'
BEGIN
set @SQL=@SQL+ 'WHERE [Big_Id]='+@Big_Id+' and [Small_ID]='+@Small_Id+' AND Type_ID not in
(SELECT TOP '+CONVERT(NVARCHAR,CONVERT(INT,@Pages)*@TopX)+' Type_ID FROM Type_Pro
WHERE [Big_Id]='+@Big_Id+' and [Small_ID]='+@Small_Id+' ORDER BY '+@Bytitle+' DESC)'
end
set @SQL=@SQL+'ORDER BY '+@Bytitle+' DESC'--PRINT @SQL
PRINT @SQL
EXECUTE(@SQL)
--select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
--endregion
以上是我的存储过程并且我做了索引:
USE [Management_sorft]
GO/****** Object: Index [Big_Id] Script Date: 01/05/2009 13:52:13 ******/
CREATE NONCLUSTERED INDEX [Big_Id] ON [dbo].[Type_Pro]
(
[Big_ID] ASC,
[Small_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
但是还是有点慢!刚开始执行速度很快,但是查询了几次之后速度就慢下来了!高手们:麻烦你们帮忙一下吧.我看了几天都找不出其它问题