Timing for an Insert/Select - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Timing for an Insert/Select

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-05-30 : 14:02:59

We have a Stored Procedure which contains a statment inserting into a temp table from a select involving 5 table. This involves 2.2 million rows (that's another story).

I can time this query. But I want to know the time it takes SQL Server to do the select vs. the time it spends inserting these rows into the temp table. Any idea how I can do this?

I can time the select on its own but since there are 2.2 million rows invovled I am worried that this time will reflect mostly network time or the time to populate the results grid.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-30 : 14:28:15

SET STATISTICS IO ON
SET STATISTICS TIME ON

And then run your stored procedure. That should give you an idea where time/resources are spent.

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

Subscribe to my blog

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-30 : 14:31:39

You can have below code in you proce to check the time taken:


select 'Start Select Operarion '+ cast(GETDATE()as varchar(20))

----Your Select Query --
select 'End   Select Operarion '+ cast(GETDATE()as varchar(20))



select 'Start Insert  Operarion '+ cast(GETDATE()as varchar(20))

----Your Insert  Query --
select 'End  Insert  Operarion '+ cast(GETDATE()as varchar(20))

Vijay is here to learn something from you guys.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-05-30 : 14:44:21

quote:
Originally posted by vijays3

You can have below code in you proce to check the time taken:

select 'Start Select Operarion '+ cast(GETDATE()as varchar(20))

----Your Select Query --
select 'End Select Operarion '+ cast(GETDATE()as varchar(20))

select 'Start Insert Operarion '+ cast(GETDATE()as varchar(20))

----Your Insert Query --
select 'End Insert Operarion '+ cast(GETDATE()as varchar(20))

Vijay is here to learn something from you guys.

But it's a single statement - insert ... select ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-30 : 15:13:32

quote:
Originally posted by vijays3

You can have below code in you proce to check the time taken:

select 'Start Select Operarion '+ cast(GETDATE()as varchar(20))

----Your Select Query --
select 'End Select Operarion '+ cast(GETDATE()as varchar(20))

select 'Start Insert Operarion '+ cast(GETDATE()as varchar(20))

----Your Insert Query --
select 'End Insert Operarion '+ cast(GETDATE()as varchar(20))

Vijay is here to learn something from you guys.

Yeah that won't work for the reason Denis said in his original post.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources