replace loop with other query to increase performance

Question

4.00/5 (1 vote)

See more:

iam working on sql query
i have @Table_1,@Table_2 Variable
i fill @Table_1 then i need to fill @Table_2 depend on value in @Table_1
for eaxmple

SQL

@Table_1
(
ID bigint,
CategoryID bigint
CategoryType tinyint
)

@Table_2
(
QUantity float
TotalSales float
totalcost float
)

&& i have i table valued function (Fn_Table) that take the paremeter from @TAble_! to fill @Table2
like the following code
**************************

SQL

declare @Pks int
declare @Pk int
declare @CategoryID bigint

select @Pks=count(ID) from @Table_1
select @pK=1
WHILE(@Pks>0)
 begin
   select @CategoryID=CategoryID from @Table_1 where ID=pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  select @pks=@pks-1
  select @pk=@pk+1
 end

********************************
the query take long time & i think this is because ot the loop
so could anybody help me to get the same result without using loop

[edit]Code blocks added - OriginalGriff[/edit]

Posted 6-Jul-12 20:52pm

md_refay

Updated 6-Jul-12 21:01pm

OriginalGriff

v2

Add a Solution

Comments

AshishChaudha 7-Jul-12 3:11am

could your show us you valued function fn_table???

[no name] 7-Jul-12 3:50am

don't care about what happend in function fn_table because it's complicated & do many things

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vasim889 · Answer 1 · 2012-07-06T23:50:00

Solution 1

SQL

declare @Pks int
declare @Pk int
declare @CategoryID bigint
 
set @Pks=count(ID) from @Table_1
set @pK=1
WHILE(@Pks>0)
 begin
   set @CategoryID=CategoryID from @Table_1 where ID=@pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  set @pks=@pks-1
  set @pk=@pk+1
 end

Posted 6-Jul-12 23:50pm

Vasim889

Comments

[no name] 7-Jul-12 6:25am

where is the solution
i think this is the same code i put in the question

Vasim889 7-Jul-12 7:30am

where is the exit loop in your sp. so process continously running b'cz not set the values to parameter .remove the select keywords to SET .it's working try tis one.

[no name] 7-Jul-12 7:40am

it 'll not make any differnet if u say
set @pks=@pks-1
or
select @pks=@pks-1
it's the same
my question is i need to remove the loop & replace it with any more performance query