Reply to Thread

Post a reply to the thread: Searching SQL Server

Your Message

If you are already a member Click here to log in

What is 10 and 5 added together?

 

You may choose an icon for your message from this list

Additional Options

  • Will turn www.example.com into [URL]http://www.example.com[/URL].

Subscription

Topic Review (Newest First)

  • July 7th, 2003, 09:22 PM
    qball
    Would be interesting to see how much faster a view is on an AS400 since it creates a logical file.
    Having used DB2 views on AS400, it was as fast or faster than SQL SELECT. They used resources, but I never felt need to benchmark, as peformance of said views, never issue.

    What was issue, was users of views started referring to computed view columns as real DB columns. As in, coupon_rate was computed field in view from 4 other columns, across 3 tables. So when coupon_rate not right, the user states just change it!

    Also, used views as tbls in many other SQL statements, very scary, when view changes.
  • July 2nd, 2003, 01:50 AM
    Creosote
    Yeah, from what I understand, nothing is compiled in SQL Server like that anymore. I could be wrong, as it didn't specifically name views, but it says the execution plan is cached for all statements. So the second time you ran any statement that did the same thing, should be cached.


    That SP I showed, doesn't need that much more horesepower. The temporary tables are stored in TempDB. One could make table variables that are stored in memory, which is where the horsepower issues would come in.

    But I can see your point on other DBMSs. Would be interesting to see how much faster a view is on an AS400 since it creates a logical file.
  • July 1st, 2003, 10:10 PM
    qball
    Its not the same thing.
    Not saying they are, saying depends on each to determine which is fastest.

    Not exactly sure on SQLServer views, but every DBMS I've used, views performed better than the SQL stmt based upon for a few reasons.

    View is based upon SELECT, and is read-only (usually).
    View normally 'compiled' (so faster), or if isn't can be placed in memory (faster), but can be issues when data is updated.
    Regardless, if view slower than SELECT based upon, why bother with a view, just execute SELECT.

    The SP you show could be faster, but very resource extensive, virtual tables come at price, but if you got the horsepower...

    if I update my example:

    SQL
    SELECT custid from customer;

    view named custid_view
    SELECT custid from customer;

    SP
    execute custid_view;
    return

    THEN:

    view fastest, or don't use
    SQL SELECT close second
    SP the slowest. There is an overhead for the SP as it needs to be invoked into memory and then run, the view and SQL don't have this overhead. But still SP will be a close third.

    If this isn't true in SQLServer, then major issues.

    Another thing to try would be subqueries on the searching, as in:

    SELECT
    ...
    FROM
    ...
    WHERE
    (prod_id IN (SELECT prod_id from tbl WHERE Description LIKE 'sumting')
    OR
    (prod_id IN (SELECT prod_id from tbl WHERE Description LIKE 'sumting else')
    ...

    IF you can remove tbl(s) from main SELECT FROM clause and move to the subquerys, could be faster...
  • July 1st, 2003, 01:45 AM
    Creosote
    How much simpler? You are qualifying a view with specified column names versus an SQL statement and stored procedure with a Select *.

    Its not the same thing.

    If you're asking for the same result set, which you aren't, but if you were, among the three, none would be the fastest. But if I had to guess, maybe the SQL statement, or the SP.

    But if I were to take a WILD guess, I would reckon the view would be a split second slower, since according to my small logic, a view would be like a subquery:

    like

    Code:
    select * from (
    select custid from customers ) as a
    If you want to compare a view, select statement and SP, you're going to have to query for the same results.

    Bottom line is a view, or any other way of accessing his same data won't save any time whatsoever.

    But you know how an SP MIGHT help? If you wanted to query three tables, it MIGHT be faster to query them separately and not join them:

    Code:
    SELECT ID INTO #Result1 FROM Table1 WHERE Description LIKE '%variable%'
    
    SELECT ID INTO #Result2 FROM Table2 WHERE Description LIKE '%variable%'
    
    SELECT ID INTO #Result3 FROM Table3 WHERE Description LIKE '%variable%'
    
    SELECT * FROM #Result1
    UNION ALL
    SELECT * FROM #Result2
    UNION ALL
    SELECT * FROM #Result3
    I don't know though, maybe unioning all the tables, then selecting from that is faster, who knows. But I have yet to hear how a view magically makes a query faster in and of itself.
  • July 1st, 2003, 12:55 AM
    qball
    I don't really know what you mean by your example.
    how much simpler do I need to be?

    Depends on view, SQL statement and storedproc
    give me:

    view;
    SQL statement;
    sp;

    "Which is "regularly" fastest?"
  • June 30th, 2003, 12:02 AM
    Creosote
    Latest versions of MS SQL. Versions < 7 would have SP faster than any other SQL statement. >= 7 would offer no benefits between SP, view, SQL statement.

    I don't really know what you mean by your example. They are 3 different statements.

    A statement would be cached for an execution plan for later use, which seems to me that the same statement used over an over in code as embedded SQL or the same select from a view or the same SP wouldn't matter.
  • June 29th, 2003, 10:19 PM
    qball
    just offering view as a possible option, not solution.

    ...latest two versions of SQL...
    which version would those be? version of SQL, or SQLServer?

    Sure, views are DBMS specific in implementation. If view not 'optimized', just SELECT with aliases, no reason to really use.

    But a view definately won't improve performance over a regularly used SQL statement or sproc.
    Depends on view, SQL statement and storedproc! an example:

    view:
    SELECT username
    FROM customer
    WHERE custid= 1;

    SQL statement:
    SELECT *
    FROM *;

    SP:
    execute view;
    execute SELECT *FROM *;

    Which is "regularly" fastest?
  • June 29th, 2003, 01:08 AM
    Creosote
    views exist to improve performance! storedprocs are functional code, that can use views.
    LOL. A view's execution plan is recompiled every time. I was actually wrong in that the latest two versions of SQL dont precompile stored procs as there are added efficiencies to all SQL statements in the later versions. But a view definately won't improve performance over a regularly used SQL statement or sproc.

    I say, run the index tuning wizard just to make sure there is nothing more you can do there. Or get a new server for only queries containing a copy of your data, or redisgn your database to include a more efficient way of server. After all, somone searching for 'Sony Internal DVD' probably really doesn't want to see a Sony Walkman, some blank DVD's, a Sony PlayStation, Internal Hard Drives, and Jaws DVD movie while searching for that string.
  • June 27th, 2003, 12:32 AM
    qball
    I seriously doubt a view would help if a sproc wouldn't. I think it would be slower than a sproc.
    views exist to improve performance! storedprocs are functional code, that can use views.

    Regardless:

    We are talking about a SQL query that is slow. Lotsa SQL slow....

    If you make query fast. stored proc, view, UNION, all goes away.

    If you don't, no matter, SQL slow...

    Essentially what I'm trying to do with this query is say "Give me all of the product information for any product that matches the search criteria provided."
    leads to search everything.

    if user enters:

    "Sony Internal DVD"

    search for that, not:

    "AND ((p.code LIKE '%Sony%'
    OR p.code LIKE '%Internal%'
    OR p.code LIKE '%DVD%'
    OR p.name LIKE '%Sony%'
    OR p.name LIKE '%Internal%'
    OR p.name LIKE '%DVD%'"

    my bad.

    essentially, SELECT * FROM p; show em all prods, then pick and choose.
  • June 26th, 2003, 09:05 PM
    Creosote
    If SQL slow, storedproc, little help. Creating view, may help, but if SQL slow...
    I seriously doubt a view would help if a sproc wouldn't. I think it would be slower than a sproc.

    If you're querying each word as separate criteria, why not query each separately. Return results from all in the output, looking like one query. Three queries for three words. Might not help a lot, but it might.
  • June 26th, 2003, 10:05 AM
    omalleytrading
    First, THANK YOU to both of you for your generous help -- I really do appreciate it.

    Have you tried convertinig into a sproc?
    Not really. The example I gave is quite tame. If the user enters in something like "Sony Internal DVD", then the query gets to be HUGE...

    AND ((p.code LIKE '%Sony%'
    OR p.code LIKE '%Internal%'
    OR p.code LIKE '%DVD%'
    OR p.name LIKE '%Sony%'
    OR p.name LIKE '%Internal%'
    OR p.name LIKE '%DVD%'

    ... and so on.

    I would have to dynamically create the SQL in the Stored Procedure, which defeats a lot of the performace gains.

    prod without price or manuf, doesn't make much sense.
    Yeah, I can see how that could be confusing. Basically, we have three tables.
    • Products holds the main product info (Name, Description, Weight, Photo, etc.). It also has a unique manufacturer ID.
    • Pricing holds the multiple price levels for the product (Consumer, Government, Educational, etc.)
    • Manufacturers hold the manufacturer names.


    So, to show 1 product you have to select the main product details, the corresponding price, and the name of the Mfr. We do it this way because there's a 1 to many relationship between both Products and Pricing and Products and Mfrs.

    Essentially what I'm trying to do with this query is say "Give me all of the product information for any product that matches the search criteria provided." A customer could enter in a product name, a product code, a mfr part number, a mfr name, etc. So we need to search all of those fields to find where the match occurs. That's why we have so many "LIKE" statements.

    Does that make more sense?
  • June 25th, 2003, 11:36 PM
    qball
    omalleytrading,

    great reply.

    Have you tried convertinig into a sproc?
    If SQL slow, storedproc, little help. Creating view, may help, but if SQL slow...

    you can do whatever, but lets break down.

    You have 3 tbls:

    "FROM CSICproducts=p
    CUApricing=pr
    CSICmanufacturers=m"

    prod without price or manuf, doesn't make much sense.

    what do you want the query to return? exactly?
    what do you want the user to search on?

    show me all products, if price exists, if manuf, exists that have name LIKE %qball%?

    you want the user to find prods, but how?
  • June 25th, 2003, 10:59 PM
    Creosote
    Have you tried convertinig into a sproc?

    Something like:

    Code:
    CREATE PROCEDURE p_QueryProducts
    @Criteria NVARCHAR(50)
    AS
    
    SELECT p.id, p.code, p.name, p.thumbnail, p.mfrpartnum, p.price, m.name AS mfr, '' AS pricing, '' AS type 
    FROM CSICproducts p INNER JOIN 
    CUApricing pr ON p.id = pr.id LEFT OUTER JOIN 
    CSICmanufacturers m ON p.store = m.store AND p.manufacturer = m.id 
    WHERE (p.store = 8) 
    AND (p.active = 'Y') 
    AND (pr.type = '') 
    AND ((p.code LIKE '%' + @Criteria + '%' 
    OR p.name LIKE '%' + @Criteria + '%' 
    OR p.[desc] LIKE '%' + @Criteria + '%'  
    OR m.name LIKE '%' + @Criteria + '%'  
    OR p.mfrpartnum LIKE '%' + @Criteria + '%'  ) ) 
    ORDER BY p.name ASC
  • June 25th, 2003, 10:43 AM
    omalleytrading
    qball - This is a sample query

    SELECT p.id, p.code, p.name, p.thumbnail, p.mfrpartnum, p.price, m.name AS mfr, '' AS pricing, '' AS type
    FROM CSICproducts p INNER JOIN
    CUApricing pr ON p.id = pr.id LEFT OUTER JOIN
    CSICmanufacturers m ON p.store = m.store AND p.manufacturer = m.id
    WHERE (p.store = 8)
    AND (p.active = 'Y')
    AND (pr.type = '')
    AND ((p.code LIKE '%elmo%'
    OR p.name LIKE '%elmo%'
    OR p.[desc] LIKE '%elmo%'
    OR m.name LIKE '%elmo%'
    OR p.mfrpartnum LIKE '%elmo%' ) )
    ORDER BY p.name ASC

    And the Execution plan is attached. I don't quite understand it all that well. I've made sure there are no table scans. Beyond that, I'm not sure what to look for.
  • June 25th, 2003, 12:34 AM
    qball
    I'd always thought they were among the most expensive types of queries, but perhaps I was wrong.
    UNION as expensive as the SELECTs they union.

    a SELECT with multiple tbl JOINs on provisional WHERE clause, can be defeated with UNION., maybe.

    No type of indexing, given your original WHERE clause will help much. Not indexing problem.

    Problem more for anyone who wants to search multiple tables or fields simultaneously in WHERE clause.

    what's the EXPLAIN PLAN tell about the query?
  • June 24th, 2003, 09:26 AM
    omalleytrading
    Thanks for the response guys.

    qball - the UNION is an interesting idea. I'd always thought they were among the most expensive types of queries, but perhaps I was wrong. I'm not sure I can use them in my case due to the way the data is distributed (and what I need returned), but I'll certainly give it some thought.

    Creosote - you're indexing idea is something I've been thinking about lately. If I could index all of the product pages up front, then just search the index table, it might be much faster than trying to search 2, 3 or 4 joined tables.

    There are several commercial packages on the market that will do such a thing, but none that I've found that will give me the flexibility to display the results the way I want them. Now I'm hoping to find some source code out there on the web that I can use as a starting point for my own system.

    It's a bit frustrating. The Full Text features of SQL Server are so close to being able to do everything I need. They just fall a bit short.
  • June 23rd, 2003, 12:50 AM
    Creosote
    I use SQL Server extensively at work, and I kind of understand what you mean.

    There are numerous factors that probably play a role in your performance issue. One is the fact that if you are paying for SQL space, the server itself may not be up to snuff compared with what you would like.

    A second would be the design issue of your database. While not as well-rounded, you might want to implement a keyword table. If the site receives enough hits, you might take a survey behind the seens and save common phrases. With those common phrases, and maybe some similar ones, populate a keyword table and use that to return results from your main table. If the keyword is not found, then query your tables.

    Also, you could implement an option for the user--I've seen some sites where the users checks 'starts with', 'exact match', etc.
    With that, you could more optimize your query instead of always using 2 wildcards in your LIKE clause.

    Since you are querying more than one table, I assume that each table has logical factors in common. Maybe ask the user to break it down for you as well. If one table was Cars and one was Trucks, give the user and option to pick which category, then send off his query for windsheiled wiper blades.

    The fewer joins you have in your query should help speed things up for you.
  • June 20th, 2003, 08:47 PM
    qball
    Are there any SQL Server experts on the board?
    I've developed on SQLServer, but your question is more just SQL.

    This must be a common problem in the e-commerce industry. Anyone have other ideas?
    Problem more for anyone who wants to search multiple tables or fields simultaneously in WHERE clause.

    If you do an EXPLAIN PLAN on the query, you will find full table scans on the LIKEs and most likely cartesian products on the ORs, if EXPLAIN PLAN shows cartesian products?

    What you can try:

    UNION the three search queries:

    SELECT
    blah
    FROM
    t1
    WHERE
    t1.field LIKE '%keyword%'
    UNION
    SELECT
    blah
    ...
    WHERE
    t2.field LIKE '%keyword%'
    UNION
    SELECT
    blah
    ...
    WHERE
    t3.field LIKE '%keyword%';

    The 'blah's need to match in number and datatype, so you need to work on the SELECT of each UNION.

    If the UNION is better, but still performance issue, then you have the classic: want to search a lot of stuff, quickly. There exist ways to optimize searching.
  • June 20th, 2003, 09:26 AM
    omalleytrading

    Searching SQL Server

    Are there any SQL Server experts on the board?

    I've got a problem with a complex search for an e-commerce site I manage. When someone types a keyword or phrase into the search field, I need to be able to search several fields across 2 or 3 tables. Even with good indexing the query can be very resource intensive when the WHERE clause is like:

    Code:
    WHERE t.field1 LIKE '%keyword%'
    OR t.field2 LIKE '%keyword%'
    OR u.field1 LIKE '%keyword%'
    ...you get the idea.

    Unfortunately, I can't use the built in FULL TEXT INDEXING service of SQL Server because many of the fields contain things like product codes with dashes, periods, slashes, etc. FTI chokes on those, which prevents you from doing something useful like searching by product code.

    This must be a common problem in the e-commerce industry. Anyone have other ideas?

    Thanks in advance!

Posting Permissions

  • You may post new threads
  • You may post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Copyright 2014 All Enthusiast, Inc