a query by less performnace
-
hi to all im face with low performance by below query in sql server 2008 any one can help me. my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R). any one can help me thanks for any help my code:
select top 19 * from(select ROW_NUMBER()over (order by ID ) as rowNumber,* from BML.LoanInstallment where total_amount like '%80%') tbl
-
hi to all im face with low performance by below query in sql server 2008 any one can help me. my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R). any one can help me thanks for any help my code:
select top 19 * from(select ROW_NUMBER()over (order by ID ) as rowNumber,* from BML.LoanInstallment where total_amount like '%80%') tbl
mehdi.sabet wrote:
my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R).
..caused by the ROW_NUMBER function. Do you need it? Is it "just" a way of numbering the records? If yes, select into a temp-table and add the ROW_NUMBER there.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
hi to all im face with low performance by below query in sql server 2008 any one can help me. my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R). any one can help me thanks for any help my code:
select top 19 * from(select ROW_NUMBER()over (order by ID ) as rowNumber,* from BML.LoanInstallment where total_amount like '%80%') tbl
Additional to Eddies suggestion: Why is total amount stored as text or why are you doing a text operation on a numeric field '%80%' seems wrong!
Never underestimate the power of human stupidity RAH
-
mehdi.sabet wrote:
my query is goon work when top has larg number but when top has less row (for example top 19) this query has low performance for table loaninstallment with amount of records(10000000 R).
..caused by the ROW_NUMBER function. Do you need it? Is it "just" a way of numbering the records? If yes, select into a temp-table and add the ROW_NUMBER there.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
thankue for answer i change my query to this:
create table #tblTemp(
ID int,
RowNumber int
)
insert into #tblTemp
select ID,ROW_NUMBER() over(order by ID) RowNum from BML.LoanInstallment
select top 19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
inner join
#tblTemp on #tblTemp.ID = BML.LoanInstallment.ID
where total_amount like '%80%') tblthis run completed a large of second i think the reason of this is my where clause that i use like in this clause and if i remove it my query is ok and response in reasonable time. what you thinks ? thanks
-
thankue for answer i change my query to this:
create table #tblTemp(
ID int,
RowNumber int
)
insert into #tblTemp
select ID,ROW_NUMBER() over(order by ID) RowNum from BML.LoanInstallment
select top 19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
inner join
#tblTemp on #tblTemp.ID = BML.LoanInstallment.ID
where total_amount like '%80%') tblthis run completed a large of second i think the reason of this is my where clause that i use like in this clause and if i remove it my query is ok and response in reasonable time. what you thinks ? thanks
-
Additional to Eddies suggestion: Why is total amount stored as text or why are you doing a text operation on a numeric field '%80%' seems wrong!
Never underestimate the power of human stupidity RAH
-