SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Timing for an Insert/Select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Constraint Violating Yak Guru

Canada
470 Posts
Posted - 05/30/2012 :  14:02:59  Show Profile  Reply with Quote
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

USA
35007 Posts
Posted - 05/30/2012 :  14:28:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
311 Posts
Posted - 05/30/2012 :  14:31:39  Show Profile  Reply with Quote


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.
Go to Top of Page

denis_the_thief
Constraint Violating Yak Guru

Canada
470 Posts
Posted - 05/30/2012 :  14:44:21  Show Profile  Reply with Quote
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 ...
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 05/30/2012 :  15:13:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.58 seconds. Powered By: Snitz Forums 2000