Need advice on searching text fields

I have a table with 2 million + rows. I need to allow the user to do a quer
y
such as is shown below. I have full text search enabled on this database,
and have built a text search catalog for it. The problem is, that I keep
getting timeouts on the query. Granted, I can increase the timout value to
a
value that works, but I need this to come back fairly quick. I need advice
on how to set up this application. Think of eBay. You can search on any
term. They have to have millions of rows in their database also. How do
they return results so quick? They have to be doing very similar query logi
c?
Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
AND ([Field1] + [Field2] Like '%term2%')Does the LIKE operator utilize the full-text search engine? Perhaps you be
using the CONTAINS and FREETEXT functions instead.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>I have a table with 2 million + rows. I need to allow the user to do a
>query
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value
> to a
> value that works, but I need this to come back fairly quick. I need
> advice
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query
> logic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like
> '%term2%')|||Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
I'll try the other two verbs and see what happens.
"JT" wrote:

> Does the LIKE operator utilize the full-text search engine? Perhaps you be
> using the CONTAINS and FREETEXT functions instead.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:88ECA16B-12D3-4AE9-ABDB-E666A633F381@.microsoft.com...
>
>|||Perhaps you're not taking advantage of the full text search with the
LIKE and the concatenated columns.
Try using FREETEXT instead of LIKE
... where ( FREETEXT (Field1, 'term1') or FREETEXT(Field2, 'term1') )
and ( FREETEXT (Field1, 'term2') or FREETEXT(Field2, 'term2') )
Brian Kitt wrote:
> I have a table with 2 million + rows. I need to allow the user to do a qu
ery
> such as is shown below. I have full text search enabled on this database,
> and have built a text search catalog for it. The problem is, that I keep
> getting timeouts on the query. Granted, I can increase the timout value t
o a
> value that works, but I need this to come back fairly quick. I need advic
e
> on how to set up this application. Think of eBay. You can search on any
> term. They have to have millions of rows in their database also. How do
> they return results so quick? They have to be doing very similar query lo
gic?
> Select * From [Table] Where ([Field1] + [Field2] Like '%term1%')
> AND ([Field1] + [Field2] Like '%term2%')[/colo
r]|||Even with the full-text search functioning, you may not get the crisp
response time you are looking for. After all, eBay has more processing power
at their disposal than 95% of us do.
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
I can assure that T-SQL LIKE does NOT use the same technology as the
Full-Text Search (FTS) predicates of CONTAINS or FREETEXT !!
-- John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
> Oh, I assumed that 'Like' utilized the full text engine. Maybe I'm wrong.
> I'll try the other two verbs and see what happens.
> "JT" wrote:
>|||Brian,
While you may not have the processing power of eBay, you can get good
performance out of FTS with a bit of tuning on both the FT Indexing (Change
Tracking with Update Index in Background) and with using CONTAINSTABLE or
FREETEXTTABLE with the Top_N_Rank parameter and limiting your results to the
top 2000 by RANK, for example:
SELECT TOP 200 T.* FROM TableWithFTColumn as T,
CONTAINSTABLE(TableWIthFTColumn,*,'John'
,300) as CT
WHERE T.key=CT.key AND T.a > 5
ORDER BY CT.rank
Where 300 is the Top_N_by_RANK value. See KB artilce 240833 "FIX: Full-Text
Search Performance Improved via Support for TOP" at
http://support.microsoft.com//defau...kb;EN-US;240833 for more
details. Also, review SQL Server 200 Books Online (BOL) title "Full-text
Search Recommendations" for more tips!
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"JT" <someone@.microsoft.com> wrote in message
news:O2tY0S8sFHA.1940@.TK2MSFTNGP14.phx.gbl...
> Even with the full-text search functioning, you may not get the crisp
> response time you are looking for. After all, eBay has more processing
> power at their disposal than 95% of us do.
> "Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
> news:13DF6525-9595-41EB-B413-844EC57A09F2@.microsoft.com...
>

Need advice on Designing a VLDB OLTP Database

Hi,
I am trying to design a sql2005 Database with 4 tables of 35 million
rows. we need to update fields in one of the table by joining with
other three. Also we need to delete the roughly 2 million rows daily
from these tables as new data is added. Please suggest if breaking all
these tables into different databases is better or having them all in
one single database is better?. Also the joining keys are varchar
fields. So any suggestions on indexing?
thanks
KrisPartitioning is your friend.
If your daily data sets are in different partitions, the drop can be
metadata-only. That is very fast. Essentially, you are truncating the
partition. To make this work, you have to have indexes aligned with the
partitioning function. Read all about partitioning in BOL.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
<krishnasingaraju@.gmail.com> wrote in message
news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> Hi,
> I am trying to design a sql2005 Database with 4 tables of 35 million
> rows. we need to update fields in one of the table by joining with
> other three. Also we need to delete the roughly 2 million rows daily
> from these tables as new data is added. Please suggest if breaking all
> these tables into different databases is better or having them all in
> one single database is better?. Also the joining keys are varchar
> fields. So any suggestions on indexing?
> thanks
> Kris
>|||> Partitioning is your friend.
Yes, very well said (I like it). Also reference DPV's (Distributed Partition
Views).
In a DPV you create n databases and link them together with a view. While
I've heard of multiple partitions on one server and that t performed well, a
DPV can be split up onto multiple nodes of an active/active cluster. There
is one important point, which will be in BOL, make sure that the data is
arranged so that at least 80% of the data you need comes from one
partition/server, or performance could actually be worse.
Jay
> If your daily data sets are in different partitions, the drop can be
> metadata-only. That is very fast. Essentially, you are truncating the
> partition. To make this work, you have to have indexes aligned with the
> partitioning function. Read all about partitioning in BOL.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> <krishnasingaraju@.gmail.com> wrote in message
> news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
>> Hi,
>> I am trying to design a sql2005 Database with 4 tables of 35 million
>> rows. we need to update fields in one of the table by joining with
>> other three. Also we need to delete the roughly 2 million rows daily
>> from these tables as new data is added. Please suggest if breaking all
>> these tables into different databases is better or having them all in
>> one single database is better?. Also the joining keys are varchar
>> fields. So any suggestions on indexing?
>> thanks
>> Kris
>|||Given the size info, I'm not sure you really want to even consider DPV.
Linchi
"Jay" wrote:
> > Partitioning is your friend.
> Yes, very well said (I like it). Also reference DPV's (Distributed Partition
> Views).
> In a DPV you create n databases and link them together with a view. While
> I've heard of multiple partitions on one server and that t performed well, a
> DPV can be split up onto multiple nodes of an active/active cluster. There
> is one important point, which will be in BOL, make sure that the data is
> arranged so that at least 80% of the data you need comes from one
> partition/server, or performance could actually be worse.
> Jay
> > If your daily data sets are in different partitions, the drop can be
> > metadata-only. That is very fast. Essentially, you are truncating the
> > partition. To make this work, you have to have indexes aligned with the
> > partitioning function. Read all about partitioning in BOL.
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> >
> >
> > <krishnasingaraju@.gmail.com> wrote in message
> > news:1192490857.778064.244440@.i13g2000prf.googlegroups.com...
> >> Hi,
> >>
> >> I am trying to design a sql2005 Database with 4 tables of 35 million
> >> rows. we need to update fields in one of the table by joining with
> >> other three. Also we need to delete the roughly 2 million rows daily
> >> from these tables as new data is added. Please suggest if breaking all
> >> these tables into different databases is better or having them all in
> >> one single database is better?. Also the joining keys are varchar
> >> fields. So any suggestions on indexing?
> >>
> >> thanks
> >> Kris
> >>
> >
>
>

Need Advice (pulling my hair out)

I am the only DBA for a company of about 200 employees which makes about 100 million a year; I have 8 SQL servers some with over 70 databases on them that feed our web sites and educational web sites. I also have a few databases that are between 75 and 120 Gig that is for our circulation system. I am the Systems admin for all of the systems that run of my SQL server, so that makes our circ system, tradeshow system, accounting system, web sites there are about 90 of them or so. I also do programming for our IT department and some web site stuff as well as for our tradeshow dept. I am the answer man for our sales people and programmers, I also have the knowledge to build by own servers and run a network. So that’s over 200 databases plus all the other *** that I do. Oh and by the way I'm supposed to help out and cover for our Tec support guys, witch there are 2 of them by the way. I can never take any more than 3 days off at a time; I have a month’s vacation. I do all this for under $55,000 a year. I am never included in any decisions on software that runs on SQL nor am I consulted on any thing having to do with SQL. I work for a director who knows nothing about SQL server. There are 2 JR data guys and they do just data queries and deal with the day to day circ requests. These guys both have an office not to mention the tech guys have an office and guess what I am in a cube. I asked my boss for a laptop with a Verizon air card so when I’m not at work if something happens that needs my attention I do have to drive to work or go home and remote in, here is the best part he looked at me and asked if I was high. I am on call 24/7 365. I have over 10 years experience working with SQL. I’m I crazy for staying? I have been with this company for almost 8 years; I am the one who started them out on SQL server. I have converted every thing over to SQL 98.5% of all the company data I am in charge of. What would you do?

In a nutshell...

You're in a toxic environment that will not improve. You've heard of the saying "A prophet is never welcomed in his own home country", right? This applies to you in this situation because, in your own words, you have 10 years of experience but have been with the same company for 8 of those 10 years. You are at a stage in your current employment situation where you are being taken for granted. And the salary you mentioned...I'd have to know what area of the country you're working in to know what is reasonable, but $55k with 10 years of experience is damned-near insulting. To put it in perspective, I too have 10 years of experience. I have changed companies every 2-3 years and now earn more than twice the figure you quoted (in Austin, Texas).

Do yourself a favor: Go to http://www.careerbuilder.com and be prepared for an eye-opening experience. Upload your resume and be prepared to field a ton of phone calls. A person with 10 years of experience is always a hot catch for a company, and you can rest assured you'll find one that will make you happy.

To answer your final question: I would leave. Pure and simple.

|||

In the US, $50K is marginally entry level for DBA.

It is really time to do some 'soul-searching', and plan your future.

Just like employers conduct 'annual reviews', employees should also do their own 'annual review'. It may be the 'right' position for you, only you know all of the details.

|||Thanks for your response. I live in Phoenix Az and I found out that DBA's with my experience level range from 75 to 92 k a year. I am so outta here. Careerbuilder and monster have lots of possibilities.|||My employers annual review was poor. According to salary.com entry level DBA's range from 60k to 68,500. Needles to say I'm getting used and insulted. Just one other thing with my company for myself and my family to get med insurance with the company is 685.00 a month.

Need a faster paging in a wesite search result page

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?

set ANSI_NULLS OFFset QUOTED_IDENTIFIER OFFGOALTER PROCEDURE [dbo].[mainSearch] @.startRowIndexint,@.maximumRowsint,@.rowCountint out,@.countedRowint,@.QUERY nvarchar(400)ASSELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM (SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where (_DELETIONSTATUS=0))AS RANKEDADDSWHERE ROWRANK > @.startRowIndex AND ROWRANK <= @.startRowIndex + @.maximumRows -1if(@.countedRow < 1)SET @.rowCount =(SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@.QUERY)as KEY_TBLON ADTBL._ID = KEY_TBL.[KEY]Where _DELETIONSTATUS=0)else SET @.rowCount = @.countedRowRETURN

Do you have any idea about the Full Text Indexing?

|||

I don't know if you designed the table but have you also checked to ensure that all the proper indexes have been added ? You also might want to look at the query execution plan to see which part is consuming most of the execution time such as a hash or merge join etc.