search time ~18 seconds in a 5Gb DB

Question

search time ~18 seconds in a 5Gb DB

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Aaron West SSC Eights! Points: 808 More actions · Answer 1

I think SSMS makes creating full-text indexes easier, through its wizard. You will need to change your queries to use proper full-text search syntax with CONTAINS, eg:

CONTAINS(column,'"term1" AND "term2" AND "term3"')

Full-text indexes won't help with pattern matching ("regular expressions", though LIKE doesn't really support a rich regex syntax so I hesitate to call it that.)

achaudhr Valued Member Points: 57 More actions · Answer 2

Thanks for the suggestions, I am going to try step by step:

All my columns are varchar. This is an application well developed by someone else few years ago. I am trying to maintaing & do some enhancements. You are right, I need to learn a lot, may be with getting help here...

will try other steps & will write if I have questions.

Thanks,

Ac.

achaudhr Valued Member Points: 57 More actions · Answer 3

So I am going step by step:

1. All my columns are varchar.

2. Created full text index on tb_Titles on Bibnumber & TiOrderByValue. no improvemnet.

3. updated statistics - no improvement.

any other suggestions, Thanks,

Archana

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 4

achaudhr (5/12/2008)
Let me know what else should I put here to make you understand better
One example of the query I am using on the view is:
select BibNumber from [vw_TypesOnlinePeriodicalsJoin]
where TiQueryValue like '%[^a-z]federal%'
AND Type='nongov-ebooks'
Order By TiOrderByValue
and view is like with ~800k records
SELECT dbo.tb_OnlinePeriodicals.PID, dbo.tb_OnlinePeriodicals.BibNumber, dbo.tb_Titles.Title, dbo.tb_OnlinePeriodicals.Hypertext,
dbo.tb_OnlinePeriodicals.Hyperlink, dbo.tb_OnlinePeriodicals.RestrictionText, dbo.tb_OnlinePeriodicals.DirectionText,
dbo.tb_OnlinePeriodicals.Frequency, dbo.tb_OnlinePeriodicals.UpdatedDate, dbo.tb_OnlinePeriodicals.RestAccess * 1 AS RestAccess,
dbo.tb_OnlinePeriodicals.PublAccess * 1 AS PublAccess, dbo.tb_OnlinePeriodicals.Multiple * 1 AS Multiple,
dbo.tb_OnlinePeriodicals.InfoGuide * 1 AS InfoGuide, dbo.tb_Titles.TiQueryValue, dbo.tb_Titles.TiOrderByValue, dbo.tb_SubjectLists.SubjectList,
dbo.tb_SubjectLists.SLQueryValue, dbo.tb_Titles.StandardRowNum, dbo.tb_Types.Type
FROM dbo.tb_OnlinePeriodicals INNER JOIN
dbo.tb_Titles ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Titles.BibNumber INNER JOIN
dbo.tb_SubjectLists ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_SubjectLists.BibNumber INNER JOIN
dbo.tb_Types ON dbo.tb_OnlinePeriodicals.BibNumber = dbo.tb_Types.BibNumber

Why are you mulitplying lots of the columns by "1"? Destroys the ability to use an index...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

>>Why are you mulitplying lots of the columns by "1"? Destroys the ability to use an index...

I don't think that is true. It probably just prevents the use of an index seek but should allow an index scan. And it would be really neat if the optimizer was smart enough to simply scale the index values and still allow a seek, although I don't expect this. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Aaron West SSC Eights! Points: 808 More actions · Answer 6

The multiplications (column * 1) are only in the SELECT column list, not in the WHERE clause or the JOINs, so they should have no effect on what indexes (indices) may be used.

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 7

You're right... not enough sleep...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Aaron West (5/27/2008)
The multiplications (column * 1) are only in the SELECT column list, not in the WHERE clause or the JOINs, so they should have no effect on what indexes (indices) may be used.

Don't think this one is correct either. 😉 Since this in in a view, it is certainly conceivable that the view is used in a query and on or more of it's something*1 columns are used in a filter. I think (although it is late HERE too and jeez have I had a hard 2 days) that situation would be a sitation where indexes on the base columns would bubble up in the optimizer as seek/scan opportunities.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

bcronce SSCrazy Points: 2597 More actions · Answer 9

He said that all columns are varchar, so "coloumn * 1" would just convert the varchar to an int/float then multiply by 1, giving the int value. Better off just doing a "CONVERT(INT/FLOAT,)" so you don't get the extra multiply in there.