May 9, 2008 at 4:35 pm
I have a database whose size increased from like 28000 records to 800K records ansd searching in this database takes about 18 seconds which is not a pleasure, can anyone guide me on this... 28K database used to take 1-6 seconds.
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
Post #498972
--archana
May 9, 2008 at 6:14 pm
achaudhr (5/9/2008)
I have a database whose size increased from like 28000 records to 800K records ansd searching in this database takes about 18 seconds which is not a pleasure, can anyone guide me on this... 28K database used to take 1-6 seconds.--archana
Without any specifics - it's going to be really hard to help. Help us help you - here's a good guide on what kind of info , and how to supply it, so that we can start to help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 9, 2008 at 7:43 pm
Please let us know what the search specifically is along with the table that is giving you issues.
A 5000 row table can give you issues and a 50billion row table can return results quickly depending on indexing and query structure.
May 12, 2008 at 6:39 am
And you will need to show execution plans so that bottlenecks can be identified.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2008 at 10:58 am
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
May 12, 2008 at 11:11 am
achaudhr (5/12/2008)
Let me know what else should I put here to make you understand betterOne 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
Without knowing anything about the table structures and indexing, plus not having the execution plans to look at either; I'd say from looking at the query above and the view, you have one or more table scans going on due to the
TiQueryValue like '%[^a-z]federal%'
in the where clause. With the leading wildcard, this query is most likely not taking advantage of any indexes you may have on the underlying tables.
😎
May 12, 2008 at 11:30 am
here is the execution plan in attachment
May 12, 2008 at 12:26 pm
Add an index to tb_Titles on TiQueryValue that includes(covers) BibNumber and TiOrderByValue. That should make it run in about 5-6 seconds.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2008 at 12:36 pm
Hi,
Thanks for helping. I tried to create that index but as the titles are lengthy & max is 1109 & it says max length allowed is 900 bytes.......I get this error..
Server: Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 1041 bytes for the index 'tb_Titles1' exceeds the maximum length of 900 bytes.
Warning! The maximum key length is 900 bytes. The index 'tb_Titles1' has maximum length of 1109 bytes. For some combination of large values, the insert/update operation will fail.
The statement has been terminated.
Can I create on 1st 900?
Thanks again
Archana
May 12, 2008 at 12:52 pm
You may want to consider full-text index.
😎
May 12, 2008 at 1:13 pm
Is it going to cause lot of complications/ changes to be made.
I tried to create it said full text searching not enabled then I enabled it via
sp_fulltext_database @action='enable'
then tried again & got the error
Server: Msg 9967, Level 16, State 1, Line 1
A default full-text catalog does not exist in database 'testOnline_Res_v6' or user does not have permission to perform this action.
Please guide.
Thanks,
AC.
May 12, 2008 at 2:25 pm
achaudhr (5/12/2008)
Hi,Thanks for helping. I tried to create that index but as the titles are lengthy & max is 1109 & it says max length allowed is 900 bytes.......I get this error..
Server: Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 1041 bytes for the index 'tb_Titles1' exceeds the maximum length of 900 bytes.
Warning! The maximum key length is 900 bytes. The index 'tb_Titles1' has maximum length of 1109 bytes. For some combination of large values, the insert/update operation will fail.
The statement has been terminated.
Can I create on 1st 900?
Thanks again
Archana
No, and it wouldn't matter anyway. If that column is that large, putting an Index on it will not help much anyway.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2008 at 4:08 pm
Any other suggestions please... If you need more details let me know.
Thanks,
Archana
May 12, 2008 at 4:30 pm
achaudhr (5/12/2008)
Is it going to cause lot of complications/ changes to be made.I tried to create it said full text searching not enabled then I enabled it via
sp_fulltext_database @action='enable'
then tried again & got the error
Server: Msg 9967, Level 16, State 1, Line 1
A default full-text catalog does not exist in database 'testOnline_Res_v6' or user does not have permission to perform this action.
Please guide.
Thanks,
AC.
I'd start with BOL, then if you have any questions ask specifics.
😎
May 13, 2008 at 7:20 am
1) are your columns char or varchar?
2) did you create the index EXACTLY as suggested - i.e. INCLUDE the other 2 columns??
3) Full-text may not help here either - you may need Regular Expression capability. I would try full-text first. As another poster said - use BOL to set that up.
4) I fear you have bloated data structures given that you type field is clearly a large char of some flavor. I would have had my type be denormalized to an int or smallint. MUCH more efficient.
5) My guess is that you have (or soon will have) more problems than this one going on. Time to hire a mentor/perf tuner to fix your problems and teach you how to find/fix them yourself.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply