0
Create table #temp_user (group_id int)
insert #temp_user Exec sp_user_acct XXXX,XX,X

It is working but not a good performance. How can I change it with something below? (CTE)

Create table #temp_user   (group_id int)
;with My_CTE as (select * from #temp_user)
insert into My_CTE Exec sp_user_acct XXXX,XX,X
Select * from My_CTE   
-- it looks like it does the insert but after that:invalid object name  my_cte.
Stu
  • 30,392
  • 6
  • 14
  • 33
BDEZ
  • 27
  • 6
  • 3
    `My_CTE` is not an *object* - you're still inserting into the table the CTE references, so `select * from #temp_user` to see your data. – Stu Mar 16 '22 at 13:45
  • 3
    1) You can't do this, and 2) even if you could it would not help your performance problem in any way (and would probably make them worse). You problems lie either in `sp_user_acct` or in the larger context of how you are invoking it (like, are you calling it in a loop?). – RBarryYoung Mar 16 '22 at 13:48
  • 1
    You can only call the CTE once in my experience, looks like you are updating the temp table via the CTE, not done that before (sort of like using a view), but yea the bottom select wont work. So like @Stu says to get the results – Andrew Mar 16 '22 at 13:49
  • *"You can only call the CTE once in my experience"* you can reference a CTE as many times as you want in the statement in has been defined in, @Andrew . Sometimes that might be 2 or 3 times (though I will admit not very often). – Thom A Mar 16 '22 at 13:50
  • I think you need to do a little more legwork on what exactly is going wrong with performance. Is it calling the stored procedure? Populating the #temp table? Using the #temp table afterward? Break it down and isolate the issue. – Aaron Bertrand Mar 16 '22 at 13:52
  • As the name suggests, a Common Table Expression (CTE) is an expression; not a table. You *can* perform DML statements against a CTE, but these effect the underlying table referenced in the CTEs definition, which is unlikely the way you are intending. – Thom A Mar 16 '22 at 13:52
  • The only time consuming part is the insert part. There are almost 34000 records get inserted to the temp table. I was wondering if I replace it with a CTE, It would be faster as CTE not uses disks which are slower. – BDEZ Mar 16 '22 at 13:57
  • 1
    I think you have a misunderstanding of what a *CTE* actually is - it defines a query expression that is then combined by the compiler into the query that references it - it's not a separate query or magic go faster stripes. – Stu Mar 16 '22 at 14:00
  • 1
    Inserting 34000 rows into a #temp table should be instantaneous. How have you determined that it is the problem? Have you tried inserting the output from a _different_ stored procedure that just returns 34000 rows from somewhere else without all of whatever other logic is in the stored procedure you're currently calling? – Aaron Bertrand Mar 16 '22 at 14:27
  • @Aaron Bertrand Yes that's right the 34000 records should be fast but I was going to do it in a different way even creating and staging table. Now I am focusing the stored procedure that is inserting data to the temp table. – BDEZ Mar 16 '22 at 15:18
  • Why are you using a temp table anyway? You haven't given us any info on what the procedure is doing with it – Charlieface Mar 16 '22 at 16:43

0 Answers0