Coffeehouse Thread

53 posts

Dumb SQLCE question.

Back to Forum: Coffeehouse
  • User profile image
    magicalclick

    Not sure how to bing this, so, I am asking here. I have Primary Key of three columns. If I only query against one of the column, will primary key be used?

    Like for example,

    Primary Key = OrderID, LineItemID.

    And I just say, SELET * FROM Items WHERE OrderID = 0001, will this be fast? Or do I need to create a separate index? I kind of think the index with more columns is capable of being used when subset of columns are queried?

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    Maddus Mattus

    That's a good question!

    Normal index rules applies, primairy key is just a unique clustered index, so I think it will use part of the index, but not very efficiently.

    If you query constantly on OrderID you could add a seperate index on that column.

    If you really really want to know, just make a SQL2008 table and inspect the queryplan.

  • User profile image
    magicalclick

    @Maddus Mattus:

    Do you have any suggested tool for SQLCE? I have SQL Server Management Studio 2008, but, it doesn't work with my version of SQLCE Crying

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    Maddus Mattus

    You might try this;

    http://channel9.msdn.com/Shows/Visual-Studio-Toolbox/Visual-Studio-Toolbox-SQL-Server-Compact-Toolbox

    Tools support for SQLCE sux0r to the max0r!

    You need to install all kinds of scheisse to get it to work.

  • User profile image
    vesuvius

    , magicalclick wrote

    @Maddus Mattus:

    Do you have any suggested tool for SQLCE? I have SQL Server Management Studio 2008, but, it doesn't work with my version of SQLCE Crying

    Try http://exportsqlce.codeplex.com/

     

  • User profile image
    figuerres

    , Maddus Mattus wrote

    That's a good question!

    Normal index rules applies, primairy key is just a unique clustered index, so I think it will use part of the index, but not very efficiently.

    If you query constantly on OrderID you could add a seperate index on that column.

    If you really really want to know, just make a SQL2008 table and inspect the queryplan.

     

    that may not be a valid way to test.

     

    depending on the sql ce version the result may be very different.  that i know of there are two very different engines for sqlce  the old one that was used with windows CE 4.xxx

    and the new one that was re-written (as i recall what was said when it came out)

    on top of that sqlce has different sets of rules for how it runs than std sql server.

    things like amount of ram it can use and how to handlle multiple connections and so on.

     

  • User profile image
    Maddus Mattus

    @figuerres: So, just add a index to the column, what's the harm?

  • User profile image
    davewill

    Every db engine I've ever worked with would use the index.  The requirement is that the WHERE clause provides the first column of the index (and the next column of the index if wanted, and the next column of the index if wanted, ...).

    If the WHERE clause were "WHERE LineItemID = 0001" (i.e. OrderID was skipped) then all bets are off.

    NOTE: whether the index is used or not is still affected by many other factors (i.e. is the dataset so small it is faster to tablescan for example).

    @Maddus Mattus: Resource constraints need to be factored in the decision.  Indexes have a spacial cost.

  • User profile image
    spivonious

    I can confirm that Oracle behaves as davewill states above. If you query for the second part of the key, it may or may not (usually not, in my experience) use the index, depending on what it decides.

    If the table is small and you're interested in columns that aren't in the index, Oracle may do a full table scan anyway since it's faster than looking up the row IDs on the index and then going to the table.

  • User profile image
    Maddus Mattus

    @davewill: Not on the client machine, where the client is the only user, he can't possibly be using another app,.. can he?

  • User profile image
    magicalclick

    , davewill wrote

    Every db engine I've ever worked with would use the index.  The requirement is that the WHERE clause provides the first column of the index (and the next column of the index if wanted, and the next column of the index if wanted, ...).

    If the WHERE clause were "WHERE LineItemID = 0001" (i.e. OrderID was skipped) then all bets are off.

    Thank you so much. I was expecting such behavior as well. I just didn't know if this is true. Even though this may not apply to latest SQLCE, but, it is very likely to be the case. At least I can expect this in other environment as well. May have to test it out, but, at least I am comfortable that it is very possible to be true based on your experience. Big Smile

    Also thanks for @figuerres: comment on SQLCE, because mine is newer and it doesn't work with SSMS 2008. It wouldn't be accurate if I used older SQLCE to test when I am actually using newer version.

    @Maddus Mattus:

    That toolbox seems to be what I need. Installing it now. Big Smile

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    magicalclick

    Hi all,

    It does use PK, although it is strange, it says my PK is a non-clustered index, wuuuuutttt...... Big Smile

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    spivonious

    @magicalclick: I don't think SQL CE supports clustered indexes. It doesn't really make sense in a file-based DB anyway, since you don't gain anything by reordering the rows.

  • User profile image
    Maddus Mattus

    @spivonious: why wouldnt it make sense? It makes perfect sense, all I wish for in a db is that it can store it's information logically so I can retrieve it swiftly. That's what a database does! You get huge performance benefits ordering rows on the disk, this is key for any db! Otherwise, I could just use a big excel file.

    I've used SQL CE extensively in the past, if you combine it with Lucene.NET, you wonder why you need the server product!

  • User profile image
    magicalclick

    @spivonious:

    Oh haha, I see. I thought if you reorder rows, you can do hdd seek? Although I suppose sqlce file is sparse by nature.

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified
  • User profile image
    AndyC

    @Maddus Mattus: There are also (potentially) performance concerns with adding indexes. Especially if something gets updated often, as you're significantly adding to the amount of work that needs to be done with each change.

  • User profile image
    JoshRoss

    Why not setup your tables like this:

    create table header(id int not null identity(1,1) primary key, ... )
    
    create table line(id int not null identity(1,1) primary key, headerId int not null, ...)
    
    create index ix0 on line(headerId)

    ?

  • User profile image
    magicalclick

    @JoshRoss:

    because you can only get int.MAX for the id? Also you didn't setup foreign key? It will be messy when you remove an order.

    I care mostly about int.MAX limitation because if I am going things like comment and messages (which I did), I want to make sure each post / conversations can have as many comments / message as possible. My example is with orders, but, actually I am building a social site.

    On the side note, I actually stopped using identity because of backup/restore concerns. I am not an expert on backup/restore, so, using identity is not a safe action for me. Also, I am using SQLCE, so, finding proper tools is not as easy as big daddy SQL Server.

    Leaving WM on 5/2018 if no apps, no dedicated billboards where I drive, no Store name.
    Last modified

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.