Reduce Time For Search Query
Feb 26, 2008
Hi,
I have a task at hand to reduce the time taken for search query to
execute. The query fetches records which will have to sorted by
degrees away from the logged in user. I have a function which
calculates the degrees, but using this in the search query slows the
execution and takes about 10 secs to complete which is unacceptable.
Please advice. Your help is much appreciated
For more details plz see:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021
Thanks
Isfaar
View 6 Replies
ADVERTISEMENT
Apr 29, 2015
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
[Code] ....
View 7 Replies
View Related
Apr 2, 2008
Hello all,
Mine below function takes much time at every execution. It takes 0.18 sec to retrive 984 rows.
Can any one help me, how to reduce execution time?
"Create function [dbo].[Fn_Get_Consensus_Curve_41_Data]
(@p_Location_Code nvarchar(10), @p_Sector_Id int, @p_Match_Date DateTime ,@p_UserID int , @p_CustId int)
RETURNS @Temp_Curve_Submission_Data table
(
Location_Codenvarchar(10),
Sector_Idint,
MatchDatedatetime ,
EntityIdint ,
CustomerIdint,
MaturityDatedatetime ,
Cust_Pricefloat ,
Bid_Pricefloat ,
Offer_Pricefloat ,
Consensus_Mid_Price float ,
Tickernvarchar(20) ,
Cust_Mnemonicnvarchar(50) ,
Currency_Idint
)
as
BEGIN
/*
GO
IF (EXISTS (SELECT name
FROM sysobjects
WHERE (name = N'Fn_Get_Consensus_Curve_41_Data')
AND ((type = 'P') OR (type = 'IF') OR (type = 'TF') OR (type = 'FN'))))
DROP FUNCTION [dbo].Fn_Get_Consensus_Curve_41_Data
GO
*/
declare @p_ENTITYID INT
declare @p_CUSTOMERID INT
Declare @p_Login_Type int
Declare @p_Result_Status int
set @p_Login_Type = (SELECT DBO.GET_USER_LOGIN_TYPE_ID(@p_UserID))
If @p_Login_Type=1 and not (@p_CustId is null or @p_CustId='')
Set @p_Result_Status = 1
Else if @p_Login_Type > 1
Set @p_Result_Status = 2
Else
Set @p_Result_Status = 0
If @p_Result_Status > 0 -- if user is valid and given enough parameters than
Begin
If @p_Result_Status = 1 -- if User is trader and gives customer id
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
CustomerID = @p_CustId And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
AND EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List(@p_Location_Code , @p_Sector_Id , @p_Match_Date ,@p_UserID ))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End
Else If @p_Result_Status = 2 -- if User is higher than trader.. means broker or higher
Begin
Declare Cur_Fetch_Curve_Cust_Data cursor for
Select Distinct Customerid
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code And
--CustomerID <> 0
--CustomerID not in (0, -1, -2, -3, -100, -200)
CustomerId Not In (Select CustomerId From Fn_Get_PricesRR_Not_To_Include_Cust_Id('V'))
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
Declare Cur_Fetch_Curve_Entity_Data cursor for
Select Distinct EntityID
From PricesRR PRR
Where
Convert(Nvarchar,Matchdate,101) = Convert(Nvarchar,@p_Match_Date,101) And
Sector_Id = @p_Sector_Id And
Location_Code = @p_Location_Code
and isnull(PRR.Record_Last_Action,'N') <> 'D'
--and Version = dbo.GET_PRICESRR_MAX_VERSION(@p_Location_Code, @p_Sector_Id, @p_Match_Date, PRR.EntityID, @p_CustId, PRR.Date)
End
delete from @Temp_Curve_Submission_Data
-----------------------
-----------------------
Open Cur_Fetch_Curve_Cust_Data
fetch next from Cur_Fetch_Curve_Cust_Data
into @p_CUSTOMERID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@FETCH_STATUS <> 0 break
BEGIN
-----------------------
-----------------------
Open Cur_Fetch_Curve_Entity_Data
fetch next from Cur_Fetch_Curve_Entity_Data
into @p_ENTITYID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@FETCH_STATUS <> 0 break
-----------------------
Insert Into @Temp_Curve_Submission_Data
(
Location_Code ,
Sector_Id,
MatchDate ,
EntityId ,
CustomerId ,
MaturityDate ,
Cust_Price ,
Bid_Price,
Offer_Price,
Consensus_Mid_Price ,
Ticker ,
Cust_Mnemonic ,
Currency_Id
)
select
@p_Location_CodeLocation_Code,
@p_Sector_IdSector_Id,
X.MatchDateMatch_Date,
X.EntityIdEntity_Id ,
X.CustomerIdCustomer_Id,
X.MaturityDateMaturity_Date,
X.PriceCust_Price,
X.BidValueBid_Price,
X.OfferValueOffer_Price,
DBO.GET_Consensus_MID ('V',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
--DBO.GET_Consensus_MID ('B1',@p_Location_Code , @p_Sector_Id , @p_Match_Date, @p_ENTITYID ,x.MaturityDate) Consensus_Mid_Price,
X.TickerTicker ,
X.MnemonicCust_Mnemonic,
X.Currency_Id
from
(
SELECT
row_number() over (order by maturitydate) Line_No,
a.* ,
b.ticker,
c.mnemonic
from Fn_Get_Tot_Curve_41_Date(@p_Location_Code, @p_Sector_Id, @p_Match_Date, @p_ENTITYID , @p_CUSTOMERID ,@p_UserID ) a,
referenceentity b,
(
select customerid, mnemonic
from customersrr
group by customerid,mnemonic
) c
where
a.customerid = c.customerid and
a.EntityID=b.CMAID
--order by maturitydate
) X
-----------------------
Fetch Next From Cur_Fetch_Curve_Entity_Data
Into @p_ENTITYID
END
CLOSE Cur_Fetch_Curve_Entity_Data
END
-----------------------
-----------------------
Fetch Next From Cur_Fetch_Curve_Cust_Data
Into @p_CUSTOMERID
END
deallocate Cur_Fetch_Curve_Entity_Data
CLOSE Cur_Fetch_Curve_Cust_Data
deallocate Cur_Fetch_Curve_Cust_Data
End
return
end
"
Prashant Hirani
View 1 Replies
View Related
Jun 9, 2006
Hi,
I have a table with column value like '123 345 678 143 648' like that. What I need to do is I have to take each code value and put it as a new record in another table. So, if I say 'Select substring(column_name,1,3) from table' then it is very fast (fraction of second). But since I need to take each code and the # of codes in each record may vary, I am using a while loop to take each code and so I delclared a variable @i and now my select statement is like this: 'Select substring(column_name,@i,3) from table'. Interesting now this select statement is taking almost 2 mins for each iteration.
Why it is like this? Is there any way I can reduce the time taken to execute each iteration?
Thanks.
View 6 Replies
View Related
Dec 6, 1999
Hi guys.
I am having trouble in time issues while backuping my database.
My database size is around 50GB. It is taking around 5hrs.
Is there any way to reduce the 5 hr backup time to 3 or less.
Thanks in advance
MAK
View 1 Replies
View Related
Apr 7, 2015
I wrote a procedure to calculate median:
============================
ALTER proc [dbo].[sp_CalculateMedianTimeInDepartmentMinutes]
@StartDate date
,@EndDate date
as
--== Check if count is even or odd
declare @modulo int
select @modulo = (Select COUNT(*)%2 from ED_data where AdmitDateTime between @StartDate and @EndDate )
--=== Get Median
[Code] ....
My fellow developer is using this code to calcuate a madians in many columns (see below). The problem is that it takes about 2 minutes to execute this code. Is there a way to reduce the time of execution?
I attach also a sample of the view
==============
ALTER PROCEDURE [dbo].[sp_ED_Measures]
@StartDate date,
@EndDate date,
@Hospital varchar(5)
AS
BEGIN
SET NOCOUNT ON;
[Code] ......
View 5 Replies
View Related
Dec 18, 2007
first sorry my english,
when i execute a query for the first time whith full text service from visual studio, show me the error 'server not responding' and when i execute this query for second time works perfectly.
View 1 Replies
View Related
Sep 8, 2015
I have a table with following data
(Id, date ,time)
11 2015/8/1
12:20:00
11
2015/8/1 12:21:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
11:21:00
12 2015/8/1
18:10:20
I need the table with following record only
(Id, date ,time)
11 2015/8/1
12:20:00
11 2015/8/1
18:05:20
12 2015/8/1
11:20:00
12 2015/8/1
18:10:20
View 7 Replies
View Related
Dec 13, 2007
I want to write a quick one time query to create a new table based off an existing table. The idea is to make the new table more efficient by reducing the amount of records...see example below
id1 id2 country
1 2 US
3 4 AU
5 6 US
7 8 US
9 10 PE
11 12 PE
13 14 US
15 16 US
17 18 US
19 20 US
21 22 US
id1 id2 country
1 2 US
3 4 AU
5 8 US
9 12 PE
13 22 US
View 10 Replies
View Related
Nov 13, 2013
I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.
View 9 Replies
View Related
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Feb 28, 2006
i have a table which contains a text field
and basically i have to check if a text or phrase exist in that text
select count(*) from MYtable where TxtField like '%MYPHRASE%'
there are 700,000 records in that table and whenever i query it takes 9 seconds to give me the recordcount.
what i am doing wrong
View 3 Replies
View Related
Feb 28, 2001
Hi,
Dow do I search for a dat time in table like
select * from table where colDate = '1999-09-09+00:00:00.000'. This doesn't work so how to go abt it?
View 2 Replies
View Related
Jun 2, 2008
I had a database of electronic resources which had 28000 records earlies and was working fine. Now we have added a whole bunch to make it 800K records which has increased the search time to 14-22 seconds which is not acceptable. I have all the tables indexed.
Please help me how to solve this problem. Let me know what other information I should put up here to make my problem undestandable.
Thanks in advance,
Archana
View 2 Replies
View Related
Aug 4, 2015
With SASS Database i have created Data mining Structure Using Time series algorithm, while processing the SSAS db, Data mining taking long time to process, so how we can reduce processing time ???
View 2 Replies
View Related
Apr 21, 2015
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
May 26, 2005
Hi,
I have a table which has a few fields, one being "datetime_traded". I need to write a query which returns the row which has the closest time (down to second) given a date/time. I'm using MS SQL.
Here's what I have so far:
Code:
select * from TICK_D
where datetime_traded = (select min( abs(datediff(second,datetime_traded , Convert(datetime,'2005-05-30:09:31:09')) ) ) from TICK_D)
But I get an error - "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.".
Does anyone know how i could do this? Thanks a lot for any help!
View 2 Replies
View Related
May 15, 2008
Hello All,
Below carry takes too much time while execution
Select
'PIT_ID' = CASE WHEN Best_BID_DATA.PIT_ID IS NOT NULL THEN Best_BID_DATA.PIT_ID ELSE Best_OFFER_DATA.PIT_ID END,
Best_Bid_Data.Bid_Customer,
Best_Bid_Data.Bid_Size,
Best_Bid_Data.Bid_Price,
Best_Bid_Data.Bid_Order_Id,
Best_Bid_Data.Bid_Order_Version,
Best_Bid_Data.Bid_ProductId,
Best_Bid_Data.Bid_TraderId,
Best_Bid_Data.Bid_BrokerId,
Best_Bid_Data.Bid_Reference,
Best_Bid_Data.Bid_Indicative,
Best_Bid_Data.Bid_Park,
Best_Offer_Data.Offer_Customer,
Best_Offer_Data.Offer_Size,
Best_Offer_Data.Offer_Price,
Best_Offer_Data.Offer_Order_Id,
Best_Offer_Data.Offer_Order_Version,
Best_Offer_Data.Offer_ProductId,
Best_Offer_Data.Offer_TraderId,
Best_Offer_Data.Offer_BrokerId,
Best_Offer_Data.Offer_Reference,
Best_Offer_Data.Offer_Indicative,
Best_Offer_Data.Offer_Park
from
(
Select PITID PIT_ID, CustomerId Bid_Customer, Size Bid_Size, Price Bid_Price, orderid Bid_Order_Id, Version Bid_Order_Version,
ProductId Bid_ProductId, TraderId Bid_TraderId, BrokerId Bid_BrokerId,
Reference Bid_Reference, Indicative Bid_Indicative, Park Bid_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select max(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'B'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Bid_Data
full outer join
(
Select PITID PIT_ID, CustomerId Offer_Customer, Size Offer_Size, Price Offer_Price, orderid Offer_Order_Id, Version Offer_Order_Version,
ProductId Offer_ProductId, TraderId Offer_TraderId, BrokerId Offer_BrokerId,
Reference Offer_Reference, Indicative Offer_Indicative, Park Offer_Park
From OrderTable C
Where
version = (select max(version) from OrderTable where orderid = c.orderid)
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
and PitId in (select distinct pitid from MarketViewDef Where MktViewId = 4)
and Price =
( Select min(Price) From OrderTable cc
where version = (select max(version) from OrderTable where orderid = cc.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Park <> 1
)
and Orderdate =
( Select min(Orderdate) From OrderTable dd
where version = (select max(version) from OrderTable where orderid = dd.orderid)
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Park <> 1
)
and OrderId = (select top 1 OrderId from OrderTable ff
Where version = (select max(version) from OrderTable where orderid = ff.orderid)
and orderid = ff.orderid
and PitId = c.PitId
and BuySell = 'S'
and Status <> 'D'
and Price = c.Price
and Orderdate = c.Orderdate
and Park <> 1
)
) Best_Offer_Data
ON Best_Bid_Data.Pit_Id = Best_Offer_Data.Pit_Id
Can any one please help me?
Thanks
Prashant
View 2 Replies
View Related
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Sep 11, 2004
I am having hard time figuring out what query I need to write.
What I want to do is, when users enter a word into a textbox, it displays results similar to it. Something similar to what Google does.
This is what I am doing now.
For example I will use "John Smith"
1. I get the first 3 letters of the string "Joh"
2. Get the last letter of the string "h"
3. and this is what I search for "Joh%h%"
This works, but I still have some problems. I want to be able to search like Google, where it recognizes words. Does anybody know how I can do this, or send me a link with an example?
Thank you
View 3 Replies
View Related
Aug 2, 2005
I would like to write query which can1. ignore whether the search keyword is Upper case or lower Case2. deal with tense or verb form related problem eg. comparing --> also search compareAny Idea
View 9 Replies
View Related
Aug 16, 2005
hi all
I have the follwoing search page:
several text boxes in case they want to search for SSN, first name,
last name, zipcode, several dorpdown lists, and a set of radio buttons.
I want to create the SQL statement to display the results
I am checking if any of the text boxes has anything in it, if yes add
"where something = value" to the query, if not then skip... now let's
say it goes through textbox 1 and finds it empty, so it jumps to the
second one it find info in it, how do I deal with the "and" clause
within the query?
any tips are fine.. (im using c#)
thanks
View 7 Replies
View Related
Feb 28, 2006
I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated.
sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 "
sql=sql & "FROM cmmst cm "
sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC"
companyNameBegins is the string passed in by the user
Thanks,
Zoop
View 2 Replies
View Related
Mar 27, 2001
Hello-
I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.
Could someone shed some light on this for me?
Thank you
Robert
******Start Query 1******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From Auctions A Where (
((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' ))
) And (Status In ('A', 'P') Or (Status In ('B', 'C')
And EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
Order By Lot Asc
*****End Query 1*****
******Start Query 2******
Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate From
(Select Lot, Title, UserId, HighBidder, HighBid,
BidTimes, DateIn, EndDate, Price, Category, Options,
Reserve, StartDate,Description From Auctions A Where
(A.Status In ('A', 'P')
Or (A.Status In ('B', 'C') And
A.EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0
And 999999.99)
Or (A.Price Between 0 And 999999.99))
) as X
Where (
( (title LIKE '%STUB%' ))
or ( (description LIKE '%STUB%' ))
)
Order By Lot Asc
******End Query 2******
View 1 Replies
View Related
Sep 6, 2007
Hello All!
I'm building a serch page on website. It basicly searches 2 tables. Right now, the way I have it so the user can enter a value in one of the fields, and not the others. And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.
But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim. I tried to use the AND operator, but then it requires that field to be filled. Here is my code.
ALTER PROCEDURE [dbo].[SrchActiveII]
@FName nvarchar(50)=NULL,
@LName nvarchar(50)=NULL,
@DOB nvarchar(50)=NULL,
@Acct nvarchar(50)=NULL,
@Login nvarchar(50)=NULL,
@Status nvarchar(50)=NULL,
@Rmark nvarchar(255)=NULL,
@Room nvarchar(50)=NULL,
@Age nvarchar(50)=NULL,
@Type nvarchar(50)=NULL,
@Misc bit = NULL
AS
BEGIN
SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks,
Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat,
Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc
FROM Active_Orders INNER JOIN
Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN
Locations ON Active_Orders.Location_ID = Locations.Location_ID
WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR
(Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR
(Active_Orders.DOB = @DOB) OR (DOB IS NULL) OR
(Active_Orders.Account_Number = @Acct) OR (Account_Number IS NULL) OR
(Order_Status.Status = @Status) OR (Status IS NULL) OR
(Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR
(Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR
(Active_Orders.Age = @Age) OR (Age IS NULL) OR
(Active_Orders.Type = @Type) OR ([Type] IS NULL) OR
(Active_Orders.Login = @Login) OR ([Login] IS NULL) OR
(Active_Orders.Misc = @Misc) OR (Misc IS NULL)
END
Thanks!
Rudy
View 7 Replies
View Related
Apr 9, 2007
Hi,
I am making a search query for jobs site. I have a situation that if user type following string in my search box:
Web Developer Designer ISF New York
It should display all jobs conatins any one of given words. following are columns in my jobs table: (Query will be applied on Location, Title, Description, Company fields)
Jobs
JobID
PostedDate
Company
URL
Location
Title
Description
Contact
please help me what will be the query.
Thanks,
Bye.
View 7 Replies
View Related
Mar 16, 2007
Hi,
I'm writing
small search engine for my page. I need SQL query that could do this:
Source:
tblColours
------------
Red
GreenRed
White Red
Yellow
Blue Green
Yellow RedF
Search
string: Red
Required
results:
Red
White Red
Yellow RedF
As you can
see I need all occurrences of word Red and word Red* but I don’t need *Red or
*Red* so I can't use LIKE %Red% :(.
P.S. Sorry
for my English.
Fizikas.
View 1 Replies
View Related
Jun 25, 2007
i have two tables,
Opportunity
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL ,
[OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OpportunityTypeID] [numeric](10, 0) NOT NULL ,
[SLABased] [int] NOT NULL ,
[LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[AccountID] [int] NOT NULL ,
[GeographyID] [int] NOT NULL ,
[VerticalID] [int] NOT NULL ,
[BDMID] [int] NOT NULL ,
[Probability] [int] NOT NULL ,
[PASStatus] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL
and SKILL
[SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[OpportunityID] [numeric](18, 0) NOT NULL ,
[OrderId] [numeric](18, 0) NOT NULL ,
[PracticeID] [int] NULL ,
[SkillID] [int] NOT NULL ,
[NoOfPeople] [int] NOT NULL ,
[Clientinterview] [int] NOT NULL ,
[Location] [int] NOT NULL ,
[JDAttached] [int] NOT NULL ,
[JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NULL ,
[Experience] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL ,
[GeoLocation] [int] NULL
)
i want to make a stored procedure for custom search on these two tables
with the following fields given to the user as an option to make his
choice..
from opportunity table -
OpportunityTypeID,SLABased,AccountID ,
GeographyID,
VerticalID,
BDMID,
Probability
and from skill table
SkillID, Location, GeoLocation
and return all the fields of opportunity table.
Can some make the stored procedure for me..
thanks a lot.
View 3 Replies
View Related
Oct 16, 2007
Hi!In short description I want to make table with Articles and search Query for this table like there is search engine at templatemonster.com with templates and categories. Basicly I have table:- id - cat_name- 1 - Software- 2 - Hardware- 3 - Games- 4 - Internet- 5 - Events etc.And for example Article is in Software, Games and Events category, "1,3,5".Now user select to show articles in Games and Events categories, so "3,4,5"How write this search query? I don't have idea.
View 2 Replies
View Related
Nov 5, 2007
Hi,
I'm wondering about the following:
I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL.
I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.
So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?
Thanks in advance for your advise.
Stewart
View 3 Replies
View Related
Aug 2, 2005
Hi everyone,
I'm trying to implement SQL Server database search. The details are:-1. I have table called EMPLOYEE has FNAME,LNAME etc cols.2. User might look for any employee using either FNAME or LNAME3. I have search box in asp.net where user could enter search stringThe sample data:FNAME LNAMEabc georgedef georgerkis litarose lita
The query i wrote:SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'My problem is:-1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.
When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.
Is this possible anyway in T-SQL or Fulltext search.
Your help is greatly appreciated.
ThanksBob
View 1 Replies
View Related
Dec 6, 2005
i have an sp that does not use full text searching in SQL 2000 i was wondering if someone could point me in the right direction of changing/upgrading this to a full text search? would you recommend upgrading the search sp? the following is the sp i have(it uses a function to count the number of words):CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(100), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(100), Rank INT, ImageALT VARCHAR(100), ArtistName VARCHAR(50), Stock INT, SearchCoverQuality INT, SearchAlbumQuality INT)
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, SearchCoverQuality, SearchAlbumQuality, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, Product.Stock, AlbumSingleDetails.CoverQualityID, AlbumSingleDetails.QualityID, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+dbo.WordCount(@Word1, ArtistName)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+dbo.WordCount(@Word2, ArtistName) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+dbo.WordCount(@Word3, ArtistName) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+dbo.WordCount(@Word4, ArtistName) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)+dbo.WordCount(@Word5, ArtistName) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, ArtistName, Stock, Rank, CASE SearchCoverQuality WHEN 1 THEN '5stars.gif' WHEN 2 THEN '4stars.gif' WHEN 3 THEN '3stars.gif' WHEN 4 THEN '2stars.gif' ELSE '1stars.gif' END AS CoverQuality, CASE SearchAlbumQuality WHEN 1 THEN '5stars.gif' WHEN 2 THEN '4stars.gif' WHEN 3 THEN '3stars.gif' WHEN 4 THEN '2stars.gif' ELSE '1stars.gif' END AS AlbumQualityFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCi was wondering if i can use full text search on many tables at once?thanks in advance!Tuppers
View 1 Replies
View Related
Dec 8, 1999
Hi friends,
Can u let me know how the sql-query, that displays only the first 20 records,and while clicking next button the subsequent records should be diplayed in a web appliaction(search). i want to implement through ADO objects.Because if the search query results in hundreds of records and everything is taken into the memory it will badly affect the performance of the system !right?
Regards
rajesh
View 1 Replies
View Related
