dcsimg
Results 1 to 9 of 9

Thread: Does it work an index?

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Does it work an index?

    Edit my main title should say does it WORTH but I blew it!
    Hi.
    Code:
    SELECT  tblDWMarketFilm.MarketFilmCode, tblDWMarketFilm.MarketFilmName, tblDWMarketTicket.MarketFilmFormat
    FROM tblDWMarketTicket
    INNER JOIN tblDWMarketFilm ON tblDWMarketFilm.MarketFilmCode = tblDWMarketTicket.MarketFilmCode
    WHERE tblDWMarketFilm.MarketFilmName LIKE 'EQ%'
    group by tblDWMarketFilm.MarketFilmCode, tblDWMarketFilm.MarketFilmName, tblDWMarketTicket.MarketFilmFormat
    I already have a primary key on MarketFilmCode and a FK on tblDWMarketFilm.MarketFilmCode = tblDWMarketTicket.MarketFilmCode
    I was wondering if it's worth putting in an index on MarketFilmName.

    Running the query without group by will give index seek on both table, while using the group by will give an index scan on MarketFilmName.
    I am thinking that maybe since the DB only has 1000 rows for now, it creates a "better" scan plan but will use seek if it gets larger?
    Or I am missing something?

    So is it work the extra index on MarketFilmName?
    tblDWMarketFilm has PK on .MarketFilmCode
    tblDWMarketTicket has PK on MarketFilmCode MarketFilmFormat
    Am expecting a load of 1000 rows per week.

    Thanks.
    Slow as hell.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,786

    Re: Does it work an index?

    For the sake of conversation, let us say that it takes X-amount of time to INSERT a new record with no indexes at all.

    One index will make it take 2X-amount of time.

    Add one more index and it's now 3X-amount of time.

    Adding this one additional index you speak of will now make it take 4X the amount of time to insert a new record.

    Since none of your RETRIEVAL seems to JOIN or WHERE on this other field - MarketFilmName - I'm having a hard time understanding what an INDEX will give you? It won't help the GROUP BY - that's a POST-build-the-initial-result-set type of action - index on that source field won't help in that moment.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: Does it work an index?

    Hey.
    Maybe I'm missing something?
    WHERE tblDWMarketFilm.MarketFilmName LIKE 'EQ%' , there is a where on the MarketFilmName.
    I hear the insert index talk. This are insert tables mostly but later on they willl be joined with other huge tables so there is another though of if I have to bloat indexes right now or wait for the bigger joins so I can use some. I'm thinking the later but I haven't decided yet as this is a work in progress.
    Slow as hell.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,634

    Re: Does it work an index?

    Quote Originally Posted by sapator View Post
    Maybe I'm missing something?
    WHERE tblDWMarketFilm.MarketFilmName LIKE 'EQ%' , there is a where on the MarketFilmName.
    si can probably confirm this but I'm fairly certain that indexes are no help when using LIKE for comparison.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: Does it work an index?

    Can't comment on that as I don't know it. They can break an index when they are not sargable though (xx like '%break%') so...Yeah I don't know.
    Slow as hell.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Does it work an index?

    si can probably confirm this but I'm fairly certain that indexes are no help when using LIKE for comparison.
    They are a help but only up to the point of the first wildcard. So they'll help with this: "Somefield like 'Hello%'" but would be useless with this: "Somefield Like '%Hello'"

    In the former case it'll seek the index then possibly apply a post filter within the part that precedes the wildcard. That's assuming the stats don't indicate that a straight forward scan wouldn't be more efficient, which it might because it's a single operation - e.g. if every record in the table began with "Hello" then the initial seek wouldn't actually achieve anything so the engine would recommend a scan.

    I was wondering if it's worth putting in an index on MarketFilmName.
    You would probably get some benefit but it may not be the best solution. 1. As SzLamany says, indexes have a cost on inserts, they also increase database size on disk and in memory. It's often worth paying that cost because we tend to do far less inserts than reads and storage is reasonably cheap, but that doesn't mean you should add them carelessly and without thought. 2. By adding a separate index the engine can only utilise it if it generates a plan that processes the join and the where as separate operations.

    Probably the best solution in this case would actually be to add MarketFilmName as a second field to the index you already have on tblDWMarketFilm.MarketFilmCode. You want whichever is more granular (code I imagine) as the first field in the index. By having it as a second field you give the query engine the opportunity to resolve both the join and the where with a single seek operation.

    Running the query without group by will give index seek on both table, while using the group by will give an index scan on MarketFilmName
    Group Bys aren't resolved by seeks, logically they can't be. I guess it could sort of do a set of seeks but it would be pretty weird. Anyway, its not the seeks and scans in your execution plan that are doing that work. You should see some operation near the top left hand side of the plan that does the grouping but I can't remember what it's called now. I think it'll be called Grouping or Aggregation or something like that.

    I am thinking that maybe since the DB only has 1000 rows for now, it creates a "better" scan plan but will use seek if it gets larger?
    That would be my assumption. The grouping wants the records being fed into it ordered by the fields in the group by. The seek your seeing without the group by probably results in a different ordering, so when you introduce the group by the engine decides that a scan that gives the desired order is cheaper than a seek followed by an sort. As the number of records increases, though, the burden of the join and filter is likely to tip the balance until a seek and sort is favourable. You'd have to scale it up with some test data to be sure though.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: Does it work an index?

    Hi.
    The group gives a Stream Aggregate you are correct.
    I'm not sure on your suggestion on adding the MarketFilmName . Are you suggesting I should rebuild the clustered PK index and add MarketFilmCode - MarketFilmName ?
    The MarketFilmCode is unique of course but ,yes marketfilmname can also be unique now that I'm thinking this out.
    So is that the suggestion?
    Also another question. Is there any difference on size increase if I put the index inside the PK or if I have it as it's own?If you are aware of this.
    thanks.
    Slow as hell.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Does it work an index?

    Are you suggesting I should rebuild the clustered PK
    Ah, I hadn't appreciated it was a clustered index supporting a primary key (although now I re-read you first post you did say so - my bad). That muddies the water a bit.

    First off, separate the index from the primary key in your head. The primary key is the unique identifier for a record. The index is simply the mechanism the database uses to enforce it. Adding an extra field to the index would be a good idea - it would allow the database engine to build an efficient execution plan. But adding an extra field to the primary key is a horrible idea. It's a total violation of database theory. So no, don't add an extra field to the primary key.

    So you now need to make a "best of the rest" decision. As I see it there are three choices:-
    1. You could live with the lack of an index. If you're query is returning in a timely fashion this might be acceptable.
    2. You could add a separate index on MarketFilmName only. This might give you some performance benefits but probably not. It's difficult for the engine to combine multiple indexes in an operation and what will probably happen is that the pk index will get used and the new index will simply be ignored.
    3. You add a new index that includes both MarketFilmCode and MarketFilmName. This will fully support the joining and filtering in your query.

    Personally I would veer towards option 3 but you should consider the costs of doing so. Insert performance will be affected as SzLamany pointed out although that would probably be bearable as long as you're always inserting onto the end of the index (ie because you're generating MarketFilmCode as an id field or numeric series). And it's going to cost disk and memory. You can calculate how much if you like (it's the storage amount of the included datatypes multiplied by the number of records) or you could just whack it on there and check for difference in the table's memory footprint (this is buried somewhere under the properties of the index and the table but I can't remember exactly where)

    BTW, I mentioned inserting onto the end of the index and that raises a question: ARE you always inserting on the end or is MarketFilmCode an alphanumeric that you might insert into the middle of? Inserting into the middle of clustered indexes is a bad idea because it causes page splits which can tank performance. If that's the case, remove the clustered pk index and replace it with a non clustered one - this will massively reduce the impact of inserts. But you're still going to need a clustered index to support reads so create a surrogate key based on an identity field and put a new clustered index on that. The query will then use your non-clustered primary key for joins etc and then feed the results into a Key Lookup to get the actual record based on the clustered index. Without a clustered index your table will be a "heap" which requires a table scan for every query, even when a non-clustered index would otherwise have supported it.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: Does it work an index?

    Hi.
    Thanks for the clarification.
    The MarketFilmCode is on an orderly fashion. For now I'm just truncating and re-inserting data because I'm testing the tables but when it goes live there won't be any deletetions, well except if he have a serious issue on any same film names but probably no.
    I'm leaning toward solution 1 because if we calculate we would have about 300000 rows insertions after 1 year and counting . The tables will always be inserting new data at weekly base and what will happen next is we are going to manually bind the table filmname - filmcode to another table. That would be with clicking one by one the...No, firstly we would do a full select on the films as shown before. After that the insert would be one something like insert into newtable - select filmcode for (the previous post table joins) where id = xxx , so no filmname at this.
    Regards.
    Slow as hell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width