Post reply

SLOW Queries... ready to pull my hair out.

  • August 13, 2009 at 4:26 pm

    #203839

    Hello. I am using MS SQL Server to query a database of about 900,000 parts. This is soon going to grow to 5,000,000 parts. I'm concerned as it's already searching very slowly.

    Here's my setup:

    Two tables

    1) Manufacturer Product Info

    * ProductID, Manufacturer, Manufacturer Part #, Product Name, Product Description (will eventually have a full text search on this field... not using it now though), and other manufacturer information)

    2) Supplier Product Information (many to 1 relationship with Manufacturer Product Info

    )

    * FK_ProductID (many to 1)

    * autoIndex (primary Key)

    * availability

    * cost

    * markup

    * SKU

    * searchstring (a concatenation of Product Name, Manufacturer, Manufacturer Part #, and SKU)

    I created a view joining these tables.

    Searches are done against the view through a search string that is split and searched as:

    WHERE searchstring like '%@search1%' AND searchstring like '%@search2%' searchstring like '%@search3%'...

    using prepared statements in ASP.NET

    I have indexes all key fields (including searchstring), but a search for something like 'transformer' can take 6-20 seconds.

    I made my SQL like this:

    SELECT TOP 100 field1, field2, field3 (about 8 fields I need) FROM ...... WHERE.....

    Does anyone know what I can do to speed up my queries?

    Thank you so much for any assistance.

  • August 13, 2009 at 7:47 pm

    #1039096

    Not sure if it will help, but have you looked into full text indexing?

  • August 13, 2009 at 11:53 pm

    #1039160

    Hmmm, can you give us an example of the data in the tables for 1 of the products?

    First thing I would do, query the tables directly to see if there isn't a performance gain(Sometimes views are a lot slower). Does SKU really need to be part of the search string? If not, you could just query against your product table.

    Searching on a varchar field that contains all of that information is always going to take long. It would be better in the long run for you to be able to search on each individual field rather than all of them combined into one field.

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 14, 2009 at 8:46 am

    #1039420

    Thank you both for your replies. I'll look into the full text searching today.

    Here's an example product:

    Supplier Index: 1235893

    SKU: 39KTZ5661

    Cost: 14.35

    Availability: 5

    ManufacturerID: 72

    Manufacturer: System Direct

    Manufacturer Part #: 6192 2.3 200 GRY

    ImageURL: some URL

    DatasheetURL: some URL

    ProductID: 471866

    Search String: System Direct 39KTZ5661 6192 2.3 200 GRY

    Active: 1

    Supplier: 6

    Unfortunately, users have to be able to search the SKU as this is the suppliers part # (rather than the mfg #) and many users go by those.

    Thank you.

  • August 14, 2009 at 8:54 am

    #1039425

    Hmmm... incidentally, I did the concatenation into the SearchString thinking it would be faster than searching 3-4 different varchars. Also, some of the part numbers are thinks like 45-1234565 so in Searchstring, I put them as 45-1234565 451234565 so that the user can find it with our without special characters. Is there a better way to do this?

  • August 14, 2009 at 9:02 am

    #1039432

    another thing you can try to change is this:

    WHERE searchstring like '%@search1%' AND searchstring like '%@search2%' searchstring like '%@search3%'...

    if you search for LIKE '%something%' , then it requires a table scan every time...900K rows, soon to be 5M rows...

    if you can leave it so it STARTS with the search term, ie LIKE 'search1%', then an index can be used. an index scan is better than a tablescan.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 14, 2009 at 9:13 am

    #1039443

    I'll play with that. I can do it for part numbers but not for the part name. For example, if a user searches for 'resistor' and that part name is 'variable resistor' it should still find the part.

    I have another version of this database that seems to run much faster. Are there database logging options, etc... that I should look at that might have affected performance?

  • August 14, 2009 at 9:27 am

    #1039462

    team (8/14/2009)

    I'll play with that. I can do it for part numbers but not for the part name. For example, if a user searches for 'resistor' and that part name is 'variable resistor' it should still find the part.

    I would also suggest you look at Full-Text indexing. This is pretty much what it's designed for.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 17, 2009 at 5:44 am

    #1040104

    Is SKU unique to Product ID, one to one match?

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 17, 2009 at 8:53 am

    #1040245

    I thought so initially, but it turns out that it isn't. For example, Company A may give there Widget at SKU of 1234. Company B may give there Wodget a SKU of 1233. These are different products using the same SKU.

    It looks like I have some studying up to do on Full Text Searching. When I try to search on my view, SQL indicates my view is not capable of full text searching -- I guess the full text indexes on ProductDescription and SearchString don't make it through to the view. If I try to add a full text index to the view, I cannot because SQL doesn't see a unique index. What I've read so far seems to indicate that full text searching in a view doesn't work with all joins.

  • August 17, 2009 at 9:29 am

    #1040274

    I guess the reason why I asked you the last question is because I was hoping it was possible to add the SKU field and searchstring field to your Manufacturer Product Info Table, then, you could index and query just the one table instead of the view. That would save you a lot of time.

    If that's not possible then another alternative is to create another table that contains just your search string field and a product_id, with indexes and then query that.

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 17, 2009 at 9:41 am

    #1040284

    I could try that anyway. If Linking to the second table adds a lot of time, then perhaps I could create a concatenated set of SKUs in the MfgProducts table or better yet, create SKU1, SKU2, SKU3, SKU4 in the MfgProducts table so that these could be searched by the index.

    I'll give it a try and see how it goes. Although... I don't know much about SQL efficiency, would this help? I still have to link the tables in order to get pricing and availability from the supplier products table (the table that contains the SKU currently).

    Thank you.

  • August 18, 2009 at 2:11 am

    #1040660

    You know, people are going to think I'm a freak, but I really love my job, optimisation is one thing I love most about SQL and I really wish I could be there to help you.

    I don't think adding an SKU1, SKU2, SKU3, etc. would be best, I think the one thing I would try and do is build the second table that contains your search string and a link to your mfg product table. Index that and run a search on it. Other than actually being there to see the environment you're working in and how this query of yours fits into your process I can't really help much more. Good Luck! :laugh:

    ----------------------------------------------------------------------------------------------
    Struggling to find the question?
    http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

  • August 19, 2009 at 1:52 am

    #1041314

    Hi,

    when you say that you have created indexes for the key fields, is this over the view or the table?

    I have found that you need to index the view to get decent performance as each time you run a query over it the "data" in the view needs to be materialised.

    Cheers

    Peter

    I work for 1st Consulting, a small ISV based in the UK. We build web and desktop applications using the MSFT stack. Find us at www.1stconsulting.biz

  • August 21, 2009 at 9:01 am

    #1042893

    Guys,

    I am not a specialist but suggesting something different. Can you create another table with only searchstring column in it. I guess it will reduce the cost of whole table scan.

    create table searchstring(searchstring varchar(size))

    insert into searchstring

    select searchstring (a concatenation of Product Name, Manufacturer, Manufacturer Part #, and SKU) from

    Try it.

    Regards,

    Ankit

Viewing 15 posts - 1 through 14 (of 14 total)

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