Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Performance

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-09 : 13:13:29

Hey all,

I'm having a problem with query performance. This was a lightning fast query when my rows were much less, but now that my listings table is about 103,000 rows its taking 40+ seconds to return rows, which is totally unacceptable.

I've tried full text indexing as well and its not really much faster. I've tried the following two:

SELECT ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, CityState, ActiveListing, UserName
FROM Listings
WHERE (ActiveListing = 1) AND (CONTAINS(ListingSubject, 'systems')) OR (CONTAINS(ListingDescription, 'systems'))
order by ListingDate Desc

SELECT ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, CityState, ActiveListing, UserName
FROM Listings
WHERE (ActiveListing = 1) AND (ListingSubject LIKE '%' + 'systems' + '%') OR (ListingDescription LIKE '%' + 'systems' + '%')
order by ListingDate Desc

Any ideas what i can do to improve performance? I have much more complicated queries than this, but i figure if i can work these out, that will be the same answer.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-09 : 15:44:58

I think the issue is here is, if you are looking in a Phonebook for any last name that contains 'mon', the index will not help you. The index would help if you are looking for a last name that begins with 'Mnem'. An idea is to look at your execution plans, this should give some insight.

Here's an idea, can you add a true/false field like ListingSubjectHasSystems and index that? Maybe this could be a calculated field or set by a Trigger.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-09 : 15:57:27

see thats the thing, "systems" is just a random string i use. That string can be anything. Its a job listings site, so you can imagine the possibilities.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-09 : 16:11:51

OK. Not too sure. 40+ seconds sounds high for 100,000 rows.

One idea and maybe Search engines use this, have a table like Listing Subjects which could contain a list of the words of the ListingSubject column along with the ListingID for each record.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-09 : 16:25:26

yea thats what i thought too, cause if its this bad with 100,000 rows, what about a million?

Also not sure about that suggestion, because i wouldnt know the list of words in the subject, or the listing ID.. this is all user generated content so it is always changing.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-03-09 : 16:40:10

What are datatype of ListingSubject and ListingDescription? Values of the columns is selective?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-09 : 16:51:14

Have you tried this it's a longshot but might work:? sp_updatestats

quote:

Also not sure about that suggestion, because i wouldnt know the list of words in the subject, or the listing ID.. this is all user generated content so it is always changing.

Everytime ListingDescription is added or changed, you could parse all the words out by looking for spaces.

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-09 : 17:03:42

> What are datatype of ListingSubject and ListingDescription? Values of the columns is selective?

ListingSubject is varchar(512)
ListingDescription is varchar(max)

I'd love to not use (max) but i beleive the char limit is 4096 if i remember correctly, and the max length of a record i have in that column is 55000 (mostly storing html)

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-10 : 11:11:01

bump.. anyone?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-10 : 11:55:25

did you try sp_updatestats?

how long if where is: WHERE ListingSubject LIKE '%systems%'

do you have index on ListingSubject?

can you describe what actual execution plan shows?

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-10 : 15:10:37

> did you try sp_updatestats?
Sadly i dont have access to do so.

> how long if where is: WHERE ListingSubject LIKE '%systems%'
22 Seconds to return 624 rows.

> do you have index on ListingSubject?
Yes, a full text index.

> can you describe what actual execution plan shows?
Estimated execution plan shows Clustered Index Scan (Clustered) [Listings].[P_Listings] Cost = 100% ---> Filter Cost = 0% ---> Select Cost = 0%

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-10 : 16:25:38

I'm kind of stuck. Can you ask someone to run sp_updatestats or ask when it was last run? If it has not been run in a while, it can make a world of difference. Or run this, it should tell you:

SELECT
object_name(object_ID),
STATS_DATE (object_id, index_id) AS last_update,
*
FROM
sys.indexes
order by
1

Maybe your server is plain slow. Or network is plain slow.

What time do you get from this:

SELECT ListingID
FROM Listings
WHERE (ListingSubject LIKE '%' + 'systems' + '%')

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2011-03-10 : 17:01:39

I think i got a handle on this actually. I found two problems. the ListingSubject was actually set to (max), so i trimmed it to 128 characters (and put a 128 char limit on the textbox that inserts into that).

I also stopped searching ListingDescription, cause that column is just huge. like over 15000 characters for a few listings. I think thats just too inefficiant to search through.

After i made those changes and added an index to ListingSubject i'm getting results in under a second. I'd really love to be able to search the listingdescription column but even removing the heml into another column limiting it to 5000 chars didnt make it any better.

I also think the server is slow, it has hundreds of DB's on it, and i know im about ready to move to dedicated hosting. I tried this stuff on my desktop and these queries only took 4 seconds. So maybe when i throw my server with 16 cpu's and 96gb of ram for this i can search the listingdescription column fast.

Btw, the stats were updated today.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2011-03-11 : 09:16:16

Thankyou for the update.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources