Slow SQL query

Hi!

I have a dynamic SQL Query that returns last logged in users. Please see attached code.

The strange thing is that the query works perfect when I set the input parameter @ProfileImage = "0" or "1", the result is returned immediately. But, if I remove the input parameter @ProfileImage the query is really slow (takes about 15 seconds to return the result).

-----------------------------------------------------
This return the result immediately:
-----------------------------------------------------

EXEC      @return_value = [dbo].[spGetLastLoggedInUsers]
            @Sex = -1,
            @ProfileImage = 1,
            @UserCategoryId = 23,
            @Status = 1,
            @MaxResult = 15,
            @RoleId = N'94663A6D-A755-4617-9EE4-402AE8CD1819',
            @SiteId = 3

-----------------------------------------------------
This takes about 15 Seconds to return the result:
-----------------------------------------------------

EXEC      @return_value = [dbo].[spGetLastLoggedInUsers]
            @Sex = -1,
            @UserCategoryId = 23,
            @Status = 1,
            @MaxResult = 15,
            @RoleId = N'94663A6D-A755-4617-9EE4-402AE8CD1819',
            @SiteId = 3


Hope someone please can help me solving this issue!

ALTER PROCEDURE [dbo].[spGetLastLoggedInUsers]
(
	@Sex int = NULL,
	@ProfileImage bit = NULL,
	@UserCategoryId smallint = NULL,
	@Status bit = NULL,
	@MaxResult int = NULL,
	@RoleId varchar(64) = NULL,
	@SiteId smallint
)
AS
BEGIN	
	SET NOCOUNT ON

    DECLARE @sql nvarchar(max)

	SET @sql = 'SELECT '

	IF @MaxResult > 0
	BEGIN
		SET @sql = @sql + 'top(' + CAST(@MaxResult as varchar(4)) + ') '
	END
	
	SET @sql = @sql + 'UserId, UserName, FirstName, LastName, [Description], Comment, BirthDate, Sex, ProfileImage, MaritalStatus, Relation, County, Religious, Children, UserCategoryId, CreateDate, LastLoginDate, LastUpdatedDate, Status, Cellphone, Email FROM vw_Users WHERE SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @Sex = 0 OR @Sex = 1
	BEGIN
		SET @sql = @sql + ' AND Sex = ' + CAST(@Sex as varchar(2))     
	END

	IF @ProfileImage = 0
	BEGIN
		SET @sql = @sql + ' AND LEN(ProfileImage) < 4'
	END

	IF @ProfileImage = 1
	BEGIN
		SET @sql = @sql + ' AND LEN(ProfileImage) > 4'
	END

	IF @UserCategoryId > 0
	BEGIN
		SET @sql = @sql + ' AND UserCategoryId = ' + CAST(@UserCategoryId as varchar(2))
	END

	IF @Status = 0 OR @Status = 1
	BEGIN
		SET @sql = @sql + ' AND Status = ' + CAST(@Status as varchar(2))
	END

	IF @RoleId IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND RoleId = ''' + @RoleId + ''''
	END

    SET @sql = @sql + ' ORDER BY LastLoginDate Desc'
    
	EXEC sp_executesql @sql  

END

Open in new window

LVL 1
webressursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
You can add the WITH RECOMPILE option to the sp to take care of the problem.

What is happening is when your sp was first run, a specific execution plan was selected.  Now things have changed, and a different execution plan works better.

You can assign your parameters to other variables, then use the other variables within the stored procedure.
0
Vadim RappCommented:
Will you see the same effect if you the sql that gets created in the stored procedure? you can easily obtain it if you put print @sql in the end,

If you do, inspect execution plan of both.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Just guessing here but ....

you use a cast: "WHERE SiteId = ' + CAST(@SiteId as varchar(2))" in the dynamic query.
This destroys the Query optimizer I suspect.
i e suddenly it's not usable with a index on SiteId.

Id try to cast it the firs thing in the proc.
i e
Declare @SiteId2 as varchar(2)
if not isnull(@SiteId)
SET @SiteId2 = CAST(@SiteId as varchar(2))

Now the code is made straight out of my head and may need some alterations. But this would allow for a more optimized Query plan.


Regards Marten
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

webressursAuthor Commented:
lludden: Should I use WITH RECOMPILE during stored procedure creation or execution? Can you please show how to implement it in my stored procedure?

Vadimrapp1: I'm not sure if I understand what you mean here: "Will you see the same effect if you the sql that gets created in the stored procedure?"

Thanks for your help! :-)
0
webressursAuthor Commented:
martenrune: I tried your suggestion, but it did not help :(
0
webressursAuthor Commented:
I also tried recompile, but it did not help. Finally, I added an IF/ELSE statement to check if @ProfileImage IS NULL, then the stored procedure returned the result fast.

	
IF @ProfileImage = 0
BEGIN
	SET @sql = @sql + ' AND LEN(ProfileImage) = 0'
END

IF @ProfileImage = 1
BEGIN
	SET @sql = @sql + ' AND LEN(ProfileImage) > 4'
END

IF @ProfileImage IS NULL
BEGIN
	SET @sql = @sql + ' AND (LEN(ProfileImage) = 0 OR LEN(ProfileImage) > 4)'
END

Open in new window

0
Randy Knight, MCMPrincipal ConsultantCommented:
If you parameterize your dynamic sql that will help too. Instead of building literals into your dynamic sql string, use the variable names and pass them in as parameters.  This will allow SQL Server to optimize based on parameter values and not the literal.  

Example:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
0
Mark WillsTopic AdvisorCommented:
Because you are building a SQL string to execute, then the RECOMPILE and parameter sniffing are not likely to be the cause.

But what is also true is that Stored Procedures retain their execution plan on first compile.

In that regard, you need to give your Stored procedure every opportunity to work as efficiently as it can.

That can often mean "preprocessing" your parameters and make sure you have every parameter possible values covered off.

The other challenge you have is using BIT datatypes which basically resolve to TRUE or FALSE and maybe INT might serve you better.

The way I would have tackled that SP would be :

ALTER PROCEDURE [dbo].[spGetLastLoggedInUsers]
(
	@Sex int = NULL,
	@ProfileImage bit = NULL,
	@UserCategoryId smallint = NULL,
	@Status bit = NULL,
	@MaxResult int = NULL,
	@RoleId varchar(64) = NULL,
	@SiteId smallint
)
AS
BEGIN	
	SET NOCOUNT ON

	declare @vSex varchar(4)
	declare @vProfileImage varchar(4)
	declare @vUserCategoryId varchar(4)
	declare @vStatus varchar(4)
	declare @vMaxResult varchar(4)
	declare @vRoleId varchar(64)
	declare @vSiteId varchar(4)

-- add in any extra validation below

	set @vSex = isnull(@sex,9)
	set @vProfileImage = @ProfileImage  -- BIT so no use doing ISNULL
	set @vUserCategoryId = isnull(@UserCategoryID,0)
	set @vStatus = @Status -- BIT so no use doing ISNULL
	set @vMaxResult = isnull(@MaxResult,0)
	set @vRoleID = @RoleId
	set @vSiteId = isnull(@SiteId,'')


    DECLARE @sql nvarchar(max)

	SET @sql = 'SELECT '

	IF @vMaxResult > '0'
	BEGIN
		SET @sql = @sql + 'top(' + @vMaxResult + ') '
	END
	
	SET @sql = @sql + 'UserId, UserName, FirstName, LastName, [Description], Comment, BirthDate, Sex, ProfileImage, MaritalStatus, Relation, County, Religious, Children, UserCategoryId, CreateDate, LastLoginDate, LastUpdatedDate, Status, Cellphone, Email 
FROM vw_Users 
WHERE SiteId = ' + @vSiteId 

	IF @vSex in ('0','1')
	BEGIN
		SET @sql = @sql + ' AND Sex = ' + @vSex     
	END

	IF @vProfileImage = '0'
	BEGIN
		SET @sql = @sql + ' AND LEN(ProfileImage) < 4'
	END

	IF @vProfileImage = '1'
	BEGIN
		SET @sql = @sql + ' AND LEN(ProfileImage) > 4'
	END

	IF @vUserCategoryId > '0'
	BEGIN
		SET @sql = @sql + ' AND UserCategoryId = ' + @vUserCategoryId
	END

	IF @vStatus in ('0','1')
	BEGIN
		SET @sql = @sql + ' AND Status = ' + @vStatus
	END

	IF @vRoleId IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND RoleId = ''' + @vRoleId + ''''
	END

    SET @sql = @sql + ' ORDER BY LastLoginDate Desc'

--print @sql
    
	EXEC sp_executesql @sql  

END

Open in new window


And in taking that approach you could then pass the param definition as suggested by rknight.. above into the executeSQL procedure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
""Will you see the same effect if you the sql that gets created in the stored procedure?"

sorry, skipped one word: should be "Will you see the same effect if you run the sql that gets created in the stored procedure?"

That is, when you call your s.p. with and without specified @ProfileImage, the dynamic sql that gets built in the s.p will be different - probably one will have AND LEN(ProfileImage) < 4 and another will not. So I suggest to take those two built statements and run them in SSMS directly.

It probably looks obvious that there will be the same difference, but I think it's necessary step to do, to make sure.
0
ZberteocCommented:
Two questions:

1. What happens when len(ProfileImage) = 4?
2. Why do you use dynamiq SQL for this query?

You can use this instead:

ALTER PROCEDURE [dbo].[spGetLastLoggedInUsers]
(
	@Sex int = NULL,
	@ProfileImage bit = NULL,
	@UserCategoryId smallint = NULL,
	@Status bit = NULL,
	@MaxResult int = NULL,
	@RoleId varchar(64) = NULL,
	@SiteId smallint
)
AS
BEGIN

	SELECT top(@MaxResult) 	
		UserId, UserName, FirstName, LastName, [Description], Comment, BirthDate, 
		Sex, ProfileImage, MaritalStatus, Relation, County, Religious, 
		Children, UserCategoryId, CreateDate, LastLoginDate, 
		LastUpdatedDate, Status, Cellphone, Email 
	FROM 
		vw_Users 
	WHERE 
			SiteId = @SiteId
		AND (Sex = @Sex or @Sex is null)
		AND ( -- it is better not not wram columns in functions in where clause
					@ProfileImage is null 
				or 
					ProfileImage like '_____%' and  @ProfileImage = 1 -- this is equivalent with len>4
				or 
					ProfileImage not like '____%' and  @ProfileImage = 0  -- this is equivalent with len<4
			)
		AND (UserCategoryId = @UserCategoryId or isnull(@UserCategoryId,0)=0) 
		AND (Status = @Status and @Status in (1,2) or @Status is null)
		AND (RoleId = @RoleId or @RoleId is null)
	ORDER BY 
		LastLoginDate Desc

END

Open in new window


Make sure you have the right indexes. You can run the query from inside procedure with some parameter you use normally, with exec plan and it will show you if any indexes are missing in the green color right at the top of the execution plan.
0
webressursAuthor Commented:
Thanks for your replies, I'm working on this now. Just got one additional question. This problem startet after I moved the database from our own server (backup) to a virtual private server in Windows Azure (restore). Can this problem be caused by a backup and restore like this?
0
Vadim RappCommented:
I remember that when we tried on sql server 2008 some of the longer queries that used to run perfectly on sql server 2000, the execution time went from seconds to minutes. Microsoft looked into that and admitted that it was something to improve in sql server. In any case, when something like that happens, the solution is to find out what was the execution plan that was fast, and then force that plan by using hints.
0
Mark WillsTopic AdvisorCommented:
When moving to a new instance you really should be updating the statistics. And also depends a bit on what edition Azure is hosting. If SQL2012 some aspects are taken care of (for a start it automatically upgrades the DB to 2012).

Would also suggest reindexing, especially if you want to move toward compression (which is often recommended for Azure). When restoring you will want to make sure those clustered indexes are clean and reindexed first then the secondary indexes use the pointers from the clustered index. Then you might want to recompile procedures.

So, it is not so much a backup and restore as much as it is a new home. For example, it wasn't until the November 2012 release that it was able to support DBCC SHOW_STATISTICS and there were upgrades to soft throttling being replaced by worker thread governance (mainly an azure rather than SQL issue).

So, there are differences and nuances within Azure, but despite that, the one truth is the query optimiser relying so very heavily on stats.

You should read up : http://msdn.microsoft.com/en-us/library/windowsazure/dn133149.aspx and in particular, follow that link and get the Performance Guidance for SQL Server in Windows Azure Virtual Machines white paper.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.