April 22, 2009 at 10:41 pm
Hi,
I am using table variables and CTE's to do paging.. These stuff works fine when i am using queries with minimal joins and other conditions
If I use the same for bigger queries with two or three union and 5 to 6 joins in each set of query its taking lots of reads and duration.
Is there any other option for paging...Can I use forward only cursor for this...? plz help me....
April 23, 2009 at 7:05 am
Can you post some example code? Since 2005 came out I have seen CTE's as the recommended method for doing this, but without seeing your code it is hard to say if you are implementing it in the best way.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 23, 2009 at 10:17 pm
In our procedure we are returning a record set which is made up of multiple queries.
I am fetching rows using seperate queries..
Here is small example......
here I am using one table variable
INSERT INTO @tblResource (AssetID,ResourceName,ResourceType,Path,LastModifiedDate,IsShared,FirstName,LastName,AssetDescription,AlternateText,FileSize,Keywords,UserID,IsSco,Initial)
SELECT [Asset].[AssetID],
[Resource].[ResourceName],
REVERSE(SUBSTRING(REVERSE([File].[Name]),0,CHARINDEX('.',REVERSE([File].[Name])))),
[File].[Location],
COALESCE([Asset].[ModifiedDate], [Asset].[CreatedDate]),
[Asset].[IsShared],
[User].[FirstName],
[User].[LastName],
[Asset].[AssetDescription],
[Resource].[AlternateText],
[File].[FileSize],
[Resource].[Keywords],
[User].[UserID],
0,
(CASE WHEN [User].[UserType]=4 THEN 'STUDENT'
ELSE (SELECT TOP 1 [Initial] FROM [UserProfile] WHERE [IsDeleted]=0 AND [UserID]=[User].[UserID]) END)
FROM [Resource] WITH(NOLOCK)
INNER JOIN [File] WITH(NOLOCK) ON [File].[AssetID]=[Resource].[FileID] AND [File].[IsDeleted]=0
INNER JOIN [Asset] WITH(NOLOCK) ON [Asset].[AssetID]=[File].[AssetID] AND [Asset].[IsDeleted]=0
INNER JOIN [User] WITH(NOLOCK) ON [User].[UserID]=[Resource].[UserID] AND [User].[IsDeleted]=0
WHERE [Resource].[IsDeleted]=0
AND [Resource].[FolderID] IN (CASE WHEN @FolderID > 0 THEN (SELECT [FolderID] FROM [Folder] WITH(NOLOCK) WHERE [IsDeleted]=0 AND [AssetID]=@FolderID) ELSE (SELECT [Resource].[FolderID]) END)
AND [Resource].[CourseID] = (CASE WHEN @CourseID > 0 THEN @CourseID ELSE [Resource].[CourseID] END)
AND [Resource].[UserID] = (CASE WHEN @Type!=1 THEN @user-id ELSE [Resource].[UserID] END)
AND [Asset].[AssetID] NOT IN (SELECT AssetID FROM @tblResource)
-----------------------------
And here some more select queries like above is used to select rows and insert to same table variable.... As I need sorting and paging at last I am using CTE
WITH CTE AS
(
SELECT AssetID,
ResourceName,
ResourceType,
Path,
LastModifiedDate,
IsShared,
FirstName,
LastName,
AssetDescription,
AlternateText,
FileSize,
Keywords,
UserID,
IsSco,
Initial
FROM @tblResource
)
SELECT * FROM CTE (with paging options)
April 23, 2009 at 10:30 pm
As this is done inside a stored procedure, I'd drop using a table variable and use a temporary table instead. If there are a large number of records in the result set loaded in to the temporary table, you are not going to get a good execution plan. The optimizer will always assume that there is only one record in the table. Also, if you need additional indexes on the table variable, you can't create them.
A temporary table will allow you to add additional indexes if needed, statitistics will be created for the data allowing the optimizer to generate a better execution plan as well.
April 24, 2009 at 12:37 am
Here is a recent article about Paging
http://www.sqlservercentral.com/articles/T-SQL/66030/
And here is the following discussion
http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx
N 56°04'39.16"
E 12°55'05.25"
April 24, 2009 at 6:27 am
Lynn and Peso, thanks for jumping in, that's good advice.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 24, 2009 at 10:01 pm
Hi Lynn..
Will it be extra overhead if I create indexes on temporary tables..??
As that procedure will be frequently used ..... Temporary table will be created and deleted,. Indexes will get created and deleted.....
Will this impact on performance...?
April 24, 2009 at 10:50 pm
manohar (4/24/2009)
Hi Lynn..Will it be extra overhead if I create indexes on temporary tables..??
As that procedure will be frequently used ..... Temporary table will be created and deleted,. Indexes will get created and deleted.....
Will this impact on performance...?
The impact on performance from using a temporary table and creating proper indexes will most likely be made up easily in the improved performance of the procedure overall. Best way to tell is make the changes and test it. I think you will be surprised.
Just be sure to uniquely name the indexes in the procedure so that each call gets a different name.
If you want, test both ways surrounding the call to the procedures with like this:
set statistics io on;
set statistics time on;
exec dbo.your_procedure
set statistics time off;
set statistics io off;
I am pretty sure you will see a significant difference between the two procedures.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply