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
selecttop19 * from(select ROW_NUMBER()over (orderby ID ) as rowNumber,* from BML.LoanInstallment where total_amount like'%80%') tbl
select ID,ROW_NUMBER() over(orderby ID) RowNum from BML.LoanInstallment
selecttop19 * from(select payment_date,total_amount,BML.LoanInstallment.ID,RowNumber from BML.LoanInstallment
#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 ?
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.
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.
ALTER procedure [dbo].[sp_pubhol]
declare @i int
declare @NewDate date
set @i = 0
IF DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 7 OR DATEPART(dw, DATEADD(day,@i,CONVERT(date,GETDATE()))) = 1
select Holiday_Name from Holiday where Holiday_On = DATEADD(day,@i,CONVERT(date,GETDATE()))
SELECT @NewDate = DATEADD(DAY,@i,CONVERT(date,GETDATE()))
select @i = @i+1
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[ ,how to declare that..pls help me
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