|
|
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 
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%') tbl
this 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
|
|
|
|
|
I think you should try it, and then thank Mycroft 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
thank you
|
|
|
|
|
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
|
|
|
|
|
this where clause is an example my where claue is another clause that my field is not total amount for example for address or anything else
.
|
|
|
|
|
how to use corrupted sql server database into oracle.
|
|
|
|
|
I would call it a 'little vague' question.
To use a corrupted database, you should first try to fix it and then use it either in SQL or Oracle. AFAIK, there is no straight forward way to use a corrupted database in Oracle.
|
|
|
|
|
Find the problem for corrupted database and make it clean then convert the DB to another DB.If you are changing the corrupted database then there is an possibilities to affect in new database also.So clear and Shift.
|
|
|
|
|
thankue for your answer
but i dont thinks problem is in my database
my db is ok
|
|
|
|
|
Then what issue you are getting...
|
|
|
|
|
my table is also lock.
if use this query and you can see that table lock .
and
select * from sys.dm_tran_locks inner join sys.objects on sys.dm_tran_locks.resource_associated_entity_id=sys.objects.[object_id]
|
|
|
|
|
Hi,
I wanna ask if I can put just one table for both prices an cities.
In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price).
After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city.
my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID...
Table Cities: CityID,PriceID,City Name.
Table Prices : PriceID,Price
Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice
Table order OrderID, CostomerID,...
The problem with prices : there are many prices for same product when they ordered (each city has a different price)
|
|
|
|
|
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done:
Have a table called Products: ProductID, ProductCode, ProductName,...
Have a table called Cities: CityID, City Name
Have a table called Prices: PriceID, Price
Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID
Pro: This way your data remains in a normalized form which is considered to be a good design approach.
Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that.
Hope it helped.
|
|
|
|
|
Maintain normalized database whereas you can avoid redundancy and if you are not maintaining more cities for one city then you can go for same table.
|
|
|
|
|
Based on what you say, it sounds like there is one price per city.
If so, I will differ a little from above answer.
I would have designed as:
Have a table called MstProducts: ProductID, ProductCode, ProductName, CityID, ...
Have a table called MstCities: CityID, City Name, Price
This covers 'each city has different price' and is also normalized.
|
|
|
|
|
Thank you guys.
I appreciated.
|
|
|
|
|
ALTER procedure [dbo].[sp_pubhol]
as
begin
declare @i int
declare @NewDate date
set @i = 0
while(@i<3)
begin
IF DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
print 'weekend'
else
print 'weekday'
select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
SELECT @NewDate = DATEADD(DAY,@i,CONVERT(date,GETDATE()))
RETURN @NewDate
select @i = @i+1
end
end
my task is if today is neither weekend nor public holiday ,i have to that date, increment loop...i have retrieve all the 3 working days...can anyone help me pls
if i want declare @NewDate as array[[3] ,how to declare that..pls help me
|
|
|
|
|
Use a table variable not an array.
Declare @Tbl Table(Date datetime)
Insert @tTbl
Select getdate()
So instead of buying a book and studying you are going to bring every problem to the interweb forums for answer. I predict your questions shortly be ignored and no answers will be forth coming!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
alter procedure sp_pubhol as begin select Holiday_Name from Holiday where Holiday_On = CONVERT(date,GETDATE()) end when i execute sp_pubhol , i am facing the following issue... Invalid object name 'Holiday' pls suggest me what should i do
|
|
|
|
|
The error is that it can't find a table called "Holiday". You can specify the database name followed by the table.
I think the syntax is YourDB.Holiday or YourDB..Holiday.
Give it a shot.
|
|
|
|
|
You really need to get a book on databases and SQL, learn the basics before attempting to build a solution. Forum messages and cut and paste is not the way to learn!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i created a table Holiday....even then it is showing error...my doubt is Holiday_On =
CONVERT(date,GETDATE()) can i do like that....
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
