SQL Server parse and compile time TOO HIGH

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server parse and compile time TOO HIGH

Author

Topic

logpop
Starting Member

16 Posts

Posted - 2014-01-02 : 12:19:39

This stored procedure takes half a minute to precompile. Is there a way to save it precompiled, so that it doesnt get compiled everytime it isnt in cache?


----------------------------------------------------------------------------------------------------------
--------- _Elura_ValidateLogin
----------------------------------------------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = '_Elura_ValidateLogin' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
	EXEC ('DROP PROCEDURE dbo._Elura_ValidateLogin')
GO
CREATE PROCEDURE [dbo].[_Elura_ValidateLogin]	
	@Username varchar(13),@Password varchar(256)
AS
	SELECT TOP (1) thr_prsn.acregno, 
               thr_prsn.acworker, 
               the_setsubjcontact.acsurname, 
               the_setsubjcontact.acname, 
               thr_prsn.acregno                                          AS 
               acUserId, 
               the_setsubj.acsubject, 
               _elura_workers.usr_lev, 
               _elura_workers.parent, 
               Ltrim(Rtrim(isnull(thr_prsnjob.acdept, thr_prsn.acdept))) AS 
               usrDept, 
               (SELECT Count(1) 
                FROM   _elura_responsible 
                WHERE  _elura_responsible.acregnowho = thr_prsn.acregno) AS 
               Resp_Count 
	FROM   thr_prsn 
		   LEFT JOIN the_setsubj 
				  ON the_setsubj.acsubject = thr_prsn.acsubject 
		   LEFT JOIN the_setsubjcontact 
				  ON the_setsubjcontact .acsubject = thr_prsn.acsubject 
		   LEFT JOIN thr_prsnjob 
				  ON thr_prsn.acworker = thr_prsnjob.acworker 
		   LEFT JOIN _elura_workers 
				  ON _elura_workers.acregno = thr_prsn.acregno 
	WHERE  thr_prsn.acregno = @Username  
		   AND thr_prsn.acpassword = @Password

GO

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 140 ms, elapsed time = 26421 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tHR_Prsn'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 19 ms.

SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 26440 ms.

Kristen
Test

22859 Posts

Posted - 2014-01-02 : 12:56:58

If your compile & parse time is high you may have too many indexes. Look at whether you have any that you don't need.

Conversely make sure you do have indexes on things that will improve the JOINs. Unless you have billions of rows in your tables there is no way that this single-row retrieve should take 26 seconds!! (unless the server was very busy / your query was blocked). The scan count and logical reads are tiny.

Those facts alone, together with the long execution time, suggest that the server was very busy, which means that the Parse time may be distorted.

Make sure statistics are up to date.

Put "dbo." (or whatever schema you are using) in front of all tables. Otherwise SQL "compiler" has to check if there IS a table/view of that name in the user's own schema, and when it doesn't find one it then goes on to look for the one in "dbo"/whatever schema. Specify the one you want explicitly to avoid the extra step.


SELECT TOP (1)

Why? There is no SORT. Is this just to safeguard against their being 2+ rows? if so I think much better to check @@ROWCOUNT > 1 and raise an error, rather than silently grabbing only one row AT RANDOM from the multiple results.

If there are multiple rows (e.g. one of the JOIN'ed tables might have multiple rows) and you just want "any one" then add an ORDER BY so that the result set is always the same / repeatable.

logpop
Starting Member

16 Posts

Posted - 2014-01-02 : 14:25:34

It takes this long only first time every time, so server was not busy.
when i do it second time it takes only fraction of a second. if i reset sql server (and clear its cache i guess) it takes 26 seconds again.
Table indexes cant be changed, it isnt my database, its from some professional program so im guessing that should be ok.
It takes this long even if tables are empty. Sql parse and compile is the problem here.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-02 : 14:59:44

quote:
its from some professional program so im guessing that should be ok.

That's hilarious!

Try getting several UserName/Passwords ready, reset the server again, then make the calls each time using a different username/password. Is it still only the first call that takes a long time or all of them?

Be One with the Optimizer
TG

logpop
Starting Member

16 Posts

Posted - 2014-01-02 : 17:36:34

Different username/password doesnt slow the query down. It's only the first time after server reset or long time someone not logging in.
Even if there is something wrong with the tables changing them isn't an option. They do have a lot of indexes:
one table has 37 indexes, I join 5 tables, so it must be about 100 indexes alltogether.

Kristen
Test

22859 Posts

Posted - 2014-01-03 : 04:30:46

"It's only the first time after server reset"

Although the compile time is quite slow (appears to be 450ms?) the execute time is enormous (20 seconds). Sounds more likely that the system is rebuilding statistics because they are stale, or loading data into memory, or somesuch.

"or long time someone not logging in"

Database not set to Auto Close is it?

"Even if there is something wrong with the tables changing them isn't an option"

Not much point you asking for advice then, or us spending time trying to help if you won't be able to implement it is there?

"one table has 37 indexes, I join 5 tables, so it must be about 100 indexes alltogether."

Feels like a lot to me. I wonder if any/many of those are unused. There are queries you can run which will tell you ... but if you can't change the database to drop indexes that are never used it would be pointless even running the query to find them.

You could try running SQL Profiler and Performance Monitor to see what is going on when you get slow running. You'll need reproducible circumstances, but sounds like a fresh boot gives you the circumstances, so the only issue will be how often you can do that - e.g. do you have a Test system available that you can do that on, or do you have to schedule downtime on a production system? My guess is that just Stop/Starting the SQL service will give you that effect, so you can save the time over having to make a reboot. But if you are going to do that repeatedly you'll need to block all user / remote systems access, and disable all jobs, so nothing else starts firing off and gets half way through when you next stop/start the services.

If it is Stats rebuild which is taking the time, or something like that, you could schedule that as a routine task during slack time so it isn't required during normal operations. The fact that it also happens when noone has logged in for some time suggests that all the database's cached data has been flushed and its the time it takes to reload enough to perform the query. I suppose Index rebuilds might help, but hopefully you already have that, as well as the Stats rebuilds, as part of your regular maintenance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources