TSQL Faster than Stored Procedure

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

TSQL Faster than Stored Procedure

Author

Topic

petersrj
Starting Member

20 Posts

Posted - 2011-09-08 : 11:06:06

I have a stored procedure that I am running on two servers. On one server, the code takes .5 seconds and on the other server it takes over 12 seconds. Windows 2008 Server, SQL 2008 Developer on both machines.

The procedure is used in my reporting scheme. I pass a Start Date (SD) an End Date (ED) and a Base Average(BA). The procedure creates a time stamp at every base interval between the Start Date and End Date. Therefore, if I give it the start date and end date and a 1 minute base average, it would return 1440 records (one for every minute of the day).

Here is the TSQL Code:

Declare @CurrTime DateTime
Declare @SD Datetime
Declare @ED Datetime
Declare @BA int

Set @BA = '1'
Set @SD = '9/8/2011 00:00'
Set @ED = '9/8/2011 23:59'

truncate Table Timeframes

Set @CurrTime = @SD

While @CurrTime <= @ED
Begin
Insert TimeFrames VALUES(@CurrTime)
Set @CurrTime = DateAdd(Mi, @BA, @CurrTime)
End

Here is the TSQL for creating the table:

CREATE TABLE [dbo].[TimeFrames](
[TimeFrame] [datetime] NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [IX_TimeFrames] ON [dbo].[TimeFrames]
(
[TimeFrame] 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

I have tried rebuilding the index, I have tried recompiling. I tried putting the TSQL in the calling routine directly and recompiling and it still takes ~12 seconds on the one machine.

Any help is appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 11:16:49

Try updating statistcs with fullscan on slower machine.

Also, look at the execution plans and # of reads on each (set statistics io on) are they the same?

Hardware is same?

Checked for blocking?

User activity is the same on each?

petersrj
Starting Member

20 Posts

Posted - 2011-09-08 : 14:27:55

Russell, thank you for the response. Both machines are here in our shop prior to being tested. At this point, no other connections or activity. In Hardware tests, each machine is with 5% on performance specs; therefore I don't think it is hardware.

I checked for blocks and did not see anything of significance there. I did run with stats on and found that on the slow machine, each insert takes ~8ms while on the faster machine, the insert takes ~0ms. Hence doing 1440 inserts takes ~12 seconds.

I broke it down to a simple insert of a fixed value and it still took ~8ms for each insert.

The servers have sufficient RAM, have RAID array on the server as well.

Again, any help is appreciated.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 14:30:14

Update the statistics with fullscan

petersrj
Starting Member

20 Posts

Posted - 2011-09-08 : 14:50:41

Russell, thank you again. I will do that.

We found a fix and it was to use BEGIN TRAN and COMMIT TRAN surrounding the write to speed it up.

I will try the statistics with fullscan.

Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 15:18:04

Unlikely that is speeding it up unless you had a lot of blocking.

Sorry, I should've looked closer at what you were doing. Updating stats may not speed up the inserts.

mfemenel
Professor Frink

1421 Posts

Posted - 2011-09-08 : 15:36:32

Are the databases configured the same way? Maybe one is full and one is simple recovery?

Mike
"oh, that monkey is going to pay"

petersrj
Starting Member

20 Posts

Posted - 2011-09-09 : 00:17:26

Mike, I will check it in the morning. Once we had problems, we reloaded SQL on the slow machine and restored the database from the faster machine. I am not sure if the recovery mode follows the installation or the backup but will check in the morning.

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 01:28:37

"restored the database from the faster machine"

That would, also, get you any index rebuilds and statistics updates that had happened on the faster machine (but had not happened on the slower one), so that might still be the cause

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-09 : 05:33:39

Erm. How about getting rid of the loop and doing a set based insert?

Current:


Declare @CurrTime DateTime
Declare @SD Datetime
Declare @ED Datetime
Declare @BA int

Set @BA = '1'
Set @SD = '9/8/2011 00:00'
Set @ED = '9/8/2011 23:59'

truncate Table Timeframes

Set @CurrTime = @SD

While @CurrTime <= @ED
Begin
Insert TimeFrames VALUES(@CurrTime)
Set @CurrTime = DateAdd(Mi, @BA, @CurrTime)
End

Here is the TSQL for creating the table:

Could probably easily be rewritten with a calendar table and a number table. You could probably get this down to 1 insert rather than many thousands of little inserts.

Also -- use the iso format for date strings. it'll bite you in the ass some day if you don't.


Set @SD = '9/8/2011 00:00'

Means different things in different places.


Set @SD = '2011-08-09T00:00:00.000'

Is universal and means 9th of August 2011 at 00:00 hours.

Sorry -- I don't have time to present the set based solution this morning. but it should be easy.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-09 : 06:46:31

So I had a few moments.
Here's what I mean:



/* Here is the TSQL for creating the table:

CREATE TABLE #TimeFrames (
	[TimeFrame] [datetime] PRIMARY KEY
) 

CREATE TABLE #Numbers (
	[n] INT PRIMARY KEY
	)

;WITH nums AS ( SELECT 1 AS [n] UNION ALL SELECT [n] + 1 AS [n] FROM nums WHERE nums.[n] < 2000 )
INSERT #Numbers ([n]) SELECT [n] FROM nums OPTION (MAXRECURSION 0)
*/
SET NOCOUNT ON

Declare @CurrTime DateTime
Declare @SD Datetime
Declare @ED Datetime
Declare @BA int
DECLARE @NOW DATETIME

Set @BA = '1'
Set @SD = '9/8/2011 00:00'
Set @ED = '9/8/2011 23:59'


truncate Table #Timeframes

SET @NOW = GETDATE()

Set @CurrTime = @SD

While @CurrTime <= @ED
Begin
Insert #TimeFrames VALUES(@CurrTime)
Set @CurrTime = DateAdd(Mi, @BA, @CurrTime)
End
SELECT DATEDIFF(MILLISECOND, @NOW, GETDATE()) AS [Insert Took]

SELECT COUNT(*) FROM #TimeFrames

TRUNCATE TABLE #TimeFrames

SET @NOW = GETDATE()

INSERT #TimeFrames 
SELECT DATEADD(MINUTE, n.[n], @SD)
FROM #numbers AS n
WHERE n.[n] <= DATEDIFF(MINUTE, @SD, @ED) + 1

SELECT DATEDIFF(MILLISECOND, @NOW, GETDATE()) AS [Insert Took]

SELECT COUNT(*) FROM #TimeFrames

Here I've made a numbers table and tested the time taken to insert your dataset.

Your insert takes over 123 miliseconds on my server. Mine takes no measurable time.

For more information read this:
http://www.sqlservercentral.com/articles/T-SQL/62867/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

petersrj
Starting Member

20 Posts

Posted - 2011-09-09 : 14:31:27

Charlie, thanks for the response and link. It was exceedingly helpful and I will implement it over the weekend. Thanks for all of your help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-09 : 14:35:01

If you modified the stored procedure and that fixed the issue, then it is likely because the execution plan changed. It sounds like you had a bad one in cache and that was resolved due to the sproc edit. Adding the transaction did not fix this, I'm certain of that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

petersrj
Starting Member

20 Posts

Posted - 2011-09-10 : 11:34:46

Tara, thank you for the input. What was interesting was the execution outside of the stored procedure was done as a single write (based on statistics on) but inside of the stored procedure it was done as 1440 writes in this case and took ~8ms per write. I tried to for recompile, I tried putting the actual code in the calling procedure (rather than calling the stored procedure) and this made no difference.

It appears there is an execution plan for this procedure that is quick and one that is slower. If this gets re-cached, how can I ensure that the execution plan is cached properly? Is there a way?

Kristen
Test

22859 Posts

Posted - 2011-09-10 : 14:03:58

Lets say you have two indexes, Date and Customer.

You might have a query where


MyDate BETWEEN @StartDate AND @EndDate
AND MyCustomer BETWEEN @StartCustomer and @EndCustomer

Sometimes your queries will be for a single date, and the date index will be used, sometimes they will be for a single Customer and the Customer Index will be used. If the Customer Index is used and the query plan cached it will be (lets assume) useless for queries which are for a narrow date range and "all customers"

So you could do:


IF DATEDIFF(Day, @StartDate, @EndDate) < 10 -- A range known to use the Date Index
BEGIN
   SELECT ...
   FROM ...
   WHERE 1=1
         AND MyDate BETWEEN @StartDate AND @EndDate
         AND MyCustomer BETWEEN @StartCustomer and @EndCustomer
END
ELSE
IF @StartCustomer = @EndCustomer -- Favour the Customer Index
BEGIN
   SELECT ...
   FROM ...
   WHERE 2=2
         AND MyDate BETWEEN @StartDate AND @EndDate
         AND MyCustomer BETWEEN @StartCustomer and @EndCustomer
END
ELSE
BEGIN
   -- Pot luck!
   SELECT ...
   FROM ...
   WHERE 3=3
         AND MyDate BETWEEN @StartDate AND @EndDate
         AND MyCustomer BETWEEN @StartCustomer and @EndCustomer
END

Kristen
Test

22859 Posts

Posted - 2011-09-10 : 14:08:43

Dunno if this will work though, short of using dynamic SQL ?

Or each of the 3 types of query would have to be separate Sprocs (so their query plan is individually cached)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-10 : 14:11:17

That actually won't help.

The optimiser will optimise them all at first execution of the procedure based on the parameters passed for the first execution. So if the first execution passes a small date range, all three queries in the procedure will be optimised for a small date range and will use the date index.

http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-09-10 : 14:12:12

Dynamic SQL or three stored procs. It's a trick I've had to use with clients' systems often.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-10 : 23:34:40

I always go down the multiple stored procedure route rather than the dynamic SQL route, where practical at least. I hate having to debug dynamic SQL code. I had to debug some dynamic SQL code recently that produced a query that contained 50,000 characters. It was multiple queries strung together with union, but it was impossible to even PRINT it out for me to see it until I got help from Twitter to use some xml trick.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-12 : 04:25:23

Isn't what you describe an instance of parameter sniffing? And if so then the usual trick to mitigate it will work?

Just don't reference the parameters directly. Copy them to local variables inside the stored proc first?

Regardless. Just stop and think about what OP said a few posts back.

quote:

Tara, thank you for the input. What was interesting was the execution outside of the stored procedure was done as a single write (based on statistics on) but inside of the stored procedure it was done as 1440 writes in this case and took ~8ms per write. I tried to for recompile, I tried putting the actual code in the calling procedure (rather than calling the stored procedure) and this made no difference.

1440 INSERTS to produce a statistics table. Something that can easily be turned into 1 INSERT using a number table.

I posted some sample code.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-12 : 04:29:51

quote:
Originally posted by tkizer

I always go down the multiple stored procedure route rather than the dynamic SQL route, where practical at least. I hate having to debug dynamic SQL code. I had to debug some dynamic SQL code recently that produced a query that contained 50,000 characters. It was multiple queries strung together with union, but it was impossible to even PRINT it out for me to see it until I got help from Twitter to use some xml trick.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

There's a hybrid approach that I'm considering for a specific use case.

If the choice is either:

1) Lots and lots of stored procs with similar logic
2) Dynamic sql

Then I'm considering a hybrid approach which is to wrote code that will generate the stored procs and commit that code to a repository.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources