How should I change this to make it faster? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How should I change this to make it faster?

Author

Topic

GenNS
Starting Member

11 Posts

Posted - 2010-10-26 : 12:41:13

Hi,

Just to get it out of the way, I'm not very experienced with SQL.

I have a web project that has a search function so that people can find a list of stores that sell a particular product that the user searches for.

My query works, in that it returns the desired results, but as the database grows it's gotten painfully slow, and the database will ultimately be much bigger than it is even now.

So I'm hoping somebody can help me by telling me how I should be doing this vs. how I'm doing it now.

The three tables accessed in this query are:
ItemsProds (about 100k records currently)
GroupsProds (about 25k records)
Stores (about 2500 records)

Note that the joins are there because I only want to show stores that have at least one product listed. Also, the other elements in the WHERE clause are there because I only want to show stores whose accounts are active. The other parts of the WHERE clause obviously apply to the user's search, the city and the area.

Here's the current query, which takes the text the user is searching for (@Phrase), the city that the search applies to (@City) and the suburb or city area (@Area) and returns the various fields related to the stores in the list.

SELECT DISTINCT Stores.ID AS storeID, Stores.Name AS storeName, Stores.Logo,
Stores.productID, Stores.datSubStart, Stores.Address1,
Stores.Address2, Stores.City, Stores.State, Stores.Zip,
Stores.Phone, Stores.URL, Stores.bitAccessible, Stores.bitOpenSunday,
Stores.bitGreen, Stores.bitOutdoor, Stores.bitDelivery, Stores.ValetType,
Stores.numLocations, Stores.PriceGroup, Stores.Rating, Stores.Description
FROM (ItemsProds INNER JOIN Stores ON ItemsProds.StoreID = Stores.ID)
INNER JOIN GroupsProds ON ItemsProds.groupID = GroupsProds.ID
WHERE CONTAINS(Stores.*, @Phrase)
AND ItemsProds.Hide=0 AND Stores.bitActive<>0 AND Stores.CityID = @City AND Stores.NeighborhoodID = @Area
ORDER BY Stores.productID DESC, Stores.Name

In the web app, I'm also doing a COUNT version of this first that just returns the count, because I'm paginating the results. So between the count and the actual query to return the results, it's taking like 5 seconds every time I hit the page.

I imagine that, while pretty straightforward, this is not a good way to do this query, so again, any help would be much appreciated.

Thanks in advance!

G

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 16:18:51

do you have indexes on following fields

ItemsProds.StoreID
Stores.ID
ItemsProds.groupID
GroupsProds.ID
Stores.CityID
Stores.NeighborhoodID

make @Phrase nvarchar if it is not already

to name a few

If you don't have the passion to help people, you have no passion

GenNS
Starting Member

11 Posts

Posted - 2010-10-26 : 16:54:44

A couple of those indexes were missing, so thanks for reminding me of that.

However, I added them, and it's still taking about 8 seconds for the page (in the web app) to load. @Phrase was already nvarchar.

Now, if I execute the SP in Management Studio directly, it shows a time of 00:00:02 to return the 85 rows in the resulting set (is that 2 seconds or 2/10ths of a second?).

So I guess I'm not sure if the issue is definitely with the query or if there's some logic in the page that's displaying the results that needs fixing.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-10-26 : 17:00:03

sounds like there might be a bottle neck past the sql server. run a sql profiler and see what is happening under the hood while you are hitting the web page.

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2010-10-27 : 01:18:54

I find CONTAINS is the bottleneck amongst clients that use it, and they find it difficult to scale it - so when the site gets busy then the Full Text Search becomes the limiting factor.

If you are doing a COUNT(*) first you are doubling the amount of work - at the least get the results into a temporary table and use that for the COUNT and to JOIN to the tables for the result / paging.

How many columns from Stores are in the Full Text Index?

Try breaking this down into

DECLARE @dtStart datetime
SET @dtStart = GetDate()
SELECT Stores.ID AS [T_StoreID]
INTO #TEMP
FROM Stores
WHERE CONTAINS(Stores.*, @Phrase)
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())
SELECT [Count]=COUNT(*) FROM #TEMP

and review the time that took, then review the time for:

DECLARE @dtStart2 datetime
SET @dtStart2 = GetDate()
SELECT DISTINCT Stores.ID AS storeID, Stores.Name AS storeName, Stores.Logo,
Stores.productID, Stores.datSubStart, Stores.Address1,
Stores.Address2, Stores.City, Stores.State, Stores.Zip,
Stores.Phone, Stores.URL, Stores.bitAccessible, Stores.bitOpenSunday,
Stores.bitGreen, Stores.bitOutdoor, Stores.bitDelivery, Stores.ValetType,
Stores.numLocations, Stores.PriceGroup, Stores.Rating, Stores.Description
INTO #TEMP2
FROM (ItemsProds INNER JOIN Stores ON ItemsProds.StoreID = Stores.ID)
INNER JOIN GroupsProds ON ItemsProds.groupID = GroupsProds.ID
INNER JOIN #TEMP ON T_StoreID = Stores.ID
WHERE CONTAINS(Stores.*, @Phrase)
AND ItemsProds.Hide=0 AND Stores.bitActive<>0 AND Stores.CityID = @City AND Stores.NeighborhoodID = @Area
ORDER BY Stores.productID DESC, Stores.Name
SELECT [Elapsed] = DATEDIFF(Millisecond, @dtStart2, GetDate())
SELECT [Count]=COUNT(*) FROM #TEMP2

I expect most of the time is in the first one. (Note that the second query is going to a temporary table, we don't want to time the transmission, and display time, at the front end, just the query time)

The other problem that you have is using the DISTINCT. You are asking SQL to query a bunch of rows, and then sort them, and then throw away the duplicates. Run the second query again but without the DISTINCT. What's the difference in the COUNT?

You are only selecting columns from STORES table, so don't join to any tables that will match multiple rows for a single store. Instead use a WHERE EXISTS on those table(s) if you only want stores that have at least one row in the [ItemsProds] table

GenNS
Starting Member

11 Posts

Posted - 2010-10-27 : 09:42:15

Thanks so much, Kristen!

Ok, so I created an SP based on your message and executed it in the Management Console. I also renamed Elapsed and Count to Elapsed1, Elapsed2, Count1 and Count2 just so I was sure that I was reading the results right.

Here's what it came back with:

For the first part, with the @Phrase search
Elapsed1: 16
Count1: 232

For the second part, with the select of all the Stores fields and the Inner Joins:
Elapsed2: 46
Count2: 0

Also, just FYI, @Phrase is usually something like

FORMSOF(INFLECTIONAL, tent)

or even

FORMSOF(INFLECTIONAL, fishing) AND FORMSOF(INFLECTIONAL, pole)

(not sure if that matters, but I thought I should mention it)

Hmm. I just ran it again and now I got:
Elapsed1: 16
Count1: 232

Elapsed2: 0
Count2: 0

I don't know if Elapsed2 is 0 this time because something was cached in SQL Server or what.

But I also am not sure why Count2 is 0. I would think Count2 should be 123 (123 is the number of rows returned by the original query if I use the same value for @Phrase)

I executed the original, then removed the # from the table names in your suggestion so that I could go in and check the IDs manually and they're correct, so again, I'm not sure why Count2 is 0. I thought maybe it's because in the final INNER JOIN there was no table name before T_StoreID, so I changed it to #TEMP.T_StoreID but that still yields 0.

I did realize that the second INNER JOIN (with GroupsProds) was superfluous, though, and I've taken that one out. (but I ran it that way originally with the same results). I also tried reversing the order of the FROM / INNER JOIN elements but still 0 results in #TEMP2.

GenNS
Starting Member

11 Posts

Posted - 2010-10-27 : 10:23:10

Oh, also, I forgot to mention that I looked up WHERE EXISTS (I haven't used that before) but I wasn't sure how to write it so that I'm checking to see which stores have at least one item, but also satisfying the other parts of the WHERE clause (checking to make sure the store is active, the items aren't all hidden, the city and area are correct).

I think I got it though, and it's SO much faster, wow!

I modified the original to this:

FROM Stores
WHERE CONTAINS(Stores.*, @Phrase)
AND bitActive<>0 AND CityID = @City
AND EXISTS (SELECT ItemsProds.ID FROM ItemsProds WHERE ItemsProds.bitHide=0 AND ItemsProds.StoreID = Stores.ID)

Making that change...removing the INNER JOINs and using EXISTS...has made it literally 8 times faster in real usage.

Do you think it's still worth pursuing your original suggestion of separating out the CONTAINS (full-text) search and then pulling stores from the resulting IDs? It seems like a good idea, but I can't figure out why that join didn't work.

Thanks again!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-27 : 10:27:57

Is the distinct really necessary? If not, remove it. It's an expensive operation.

--
Gail Shaw
SQL Server MVP

GenNS
Starting Member

11 Posts

Posted - 2010-10-27 : 11:46:43

Yeah, I didn't think about that...with the JOINs gone, it's not really doing anything anyway. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources