April 27, 2011 at 2:28 pm
I have 7 databases in my SQL server. When I execute the code below, I get very strange results as shown below. What could cause the differences in timing? The two fastest are the two largest. The others are about 10% of the 2 big ones.
declare @start datetime2, @end datetime2, @diff decimal( 10, 4)
SET @start = getdate()
USE solovue_HOMART;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'HOMART'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
SET @start = getdate()
USE solovue_atlas;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'atlas'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
SET @start = getdate()
USE solovue_homestead;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'homestead'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
SET @start = getdate()
USE solovue_country_fresh;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'country_fresh'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
SET @start = getdate()
USE solovue_IBOLILI;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'IBOLILI'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
SET @start = getdate()
USE solovue_SOLOVUE;
truncate table history;
iNSERT INTO [history]
([history_id]
,[table_name]
,[changes]
,[who]
,[when_dt]
,[change_type]
,[pkfield]
,[pk_id]
,[screen_name]
,[locked_by_user_id]
,[locked_dt])
VALUES
( 1 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 2 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 3 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 4 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 5 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 6 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 7 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 8 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
( 9 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null),
(10 ,'test' ,'test1' ,'me' ,'12/24/2010' ,'x' ,'custoemr_id' ,1 ,'unknown' ,0 ,null)
SET @end = getdate()
SET @diff = datediff( ms, @start, @end)
PRINT '*******************'
PRINT 'SOLOVUE'
PRINT 'Time: ' + CAST( @diff as varchar(30))
PRINT '*******************'
Results:
(10 row(s) affected)
*******************
HOMART
Time: 3.0000
*******************
(10 row(s) affected)
*******************
atlas
Time: 0.0000
*******************
(10 row(s) affected)
*******************
homestead
Time: 6620.0000
*******************
(10 row(s) affected)
*******************
country_fresh
Time: 6964.0000
*******************
(10 row(s) affected)
*******************
IBOLILI
Time: 13093.0000
*******************
(10 row(s) affected)
*******************
SOLOVUE
Time: 12183.0000
*******************
As you can see, the above results are # of milliseconds. The smallest database - solovue - takes the longest. Any help would be appreciated. I don't even know where to start looking.
Mike
April 28, 2011 at 3:51 am
A couple of things to check?
Do you have autogrow on?
It is possible that your database files needed to grow.
Another possibility would be the occurence of page splits.
Is the number of indexes the same on the different tables?
Hope this helps.
Marc
April 28, 2011 at 8:58 am
It turns out that it is a problem with mirroring.
Thanks All.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply