Results 1 to 19 of 19

Thread: Can I divide tables for a faster search?

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Exclamation Can I divide tables for a faster search?

    Hello,

    I have 650,000 records in a single table used with SQL Compact 3.5 on a Windows CE 5.0 device. If I scan a bar code that does not exist it takes over a minute to come back and say if was not found. And I have the table indexed on this column. The column is an integer value which is an ISBN for books. Could I actually divide this table up into 10 different tables and then query a specific table based on the ISBN if it falls within a range? This way it would not have to look through so many records. Would this be faster?

    Thanks

    Warren

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Can I divide tables for a faster search?

    No... I don't think it would help. But it also shouldn't take that long, especially if it's indexed. Is it just an index, or is it a clustered index? Is the field unique? If it's unique it might be worth making it the Primary Key, Unique with a clustered index on it. If it's indexed properly it's not looking through all of the records, but should be doing an index scan to find where the record is.

    So given all that... I'm also assuming that you're not just doing a simple select * from myTable where ISBN = 123456788... but that it's more complex than that. If so... look at the indexes on the other tables as well and if you can, index fields that you join on.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    Here is the index I create:

    create nonclustered index inxBookInv on BookInv (ISBN)

    It is not a unique column or PK. I can do either one or both on the field if needed. Can you tell me the best way to index it and make it faster?

    Thanks!

    Warren

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    Also, I read it only supports nonclustered index.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    Btw, I updated the table making it a unique column, primary key and created a new index on it with 300K records. It still took as long.

  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,900

    Re: Can I divide tables for a faster search?

    Can you show us the whole query you're issuing. As TG said, it doesn't sound like the index is the problem and it's likely that some other table or entity you're joining to is at fault.

    Also, what device is this on? A minute is an awfully long time for a database to take to respond but if you're housing it on something with ridiculously low power (which sounds possible since you're using CE) then I guess it could just be a matter of "lack of grunt".
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    I am doing the most basic select of this:

    SELECT ISBN, Title, Author FROM BookINV WHERE ISBN = " & ISBN2

    It is on a Casio 600 scanner. I don't have the exact model in from of me now. It took about 4-5 minutes for the program to open on the device. It would not open before until I adjusted the memory. I have a very short span to adjust the memory under the Systems window. This is with 300K records and I have 650K records to put on there for a total. I just don't see this working at all. Btw, if I do not have an index on the table then the program loads instantly.

    Thanks,

    Warren

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I divide tables for a faster search?

    There are times that SQL should not be used - and this could be one of them.

    The 4 to 5 minute issue scare me further...

    I'm feeling this is a device-related-I/O issue.

    Instead of a table in SQL - how about a file of this data - already sorted - so that you could do you own binary-search on it. Even with 650K records it will still find the data you want in around 19 or 20 I/O operations.

    If you are able to read just a piece of the file to check for your data then I can't imagine it taking more then a few seconds to read 19 or 20 "pages" of data.

    *** 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

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Can I divide tables for a faster search?

    I'm inclined to agree... we're not dealing with a database issue, but rather a device limitation.
    That's a very long time for an app to load up... you're not loading everything when the app starts, are you? You shouldn't be... you should be only loading minimal, if any data at start up.
    Also, can this device communicate via the network? It may be worth exploring not having a local database, and pulling the data down via the network. If it doesn't have Wi-fi capabilities, then .... hmmmm.... seems to me there should be a better way than to have everything on the device.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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

    Re: Can I divide tables for a faster search?

    Btw, if I do not have an index on the table then the program loads instantly.
    I suspect that's the clue, right there. I'm guessing you're creating the index (and possibly creating the database) when the app loads, is that right? You don't want to do that. You should create the database, fully populated and indexed, once, once only, and that should happen before your app runs. It should not be a function of your app.

    In "Full blown sql server" world we'd do this up front using management studio, scripts etc. I don't know what tools exist for doing it with CE I'm afraid.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

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

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I divide tables for a faster search?

    When I used CE on Pocket PC's I wrote my own "sync" code so that I could pull network-SQL data into the PPC. That sync was a slow process - but only happened once.

    Do you have a TEXT file of all this ISBN data that you are importing somehow??

    *** 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

  12. #12

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    I already have the tables created, data loaded and the index already created. Then I copy the SDF file on the device and only try to open it. So I am not doing anything extra at startup. If no index the program starts right up with opening a connection.

    So would this then be faster with a text file sorted. How would be the best way to do a binary search on this that is faster? I am updating the table at times by adding new records so that would be an issue also.

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    I am loading all of the data with a .net app I wrote that connects to a MySQL database and then inserts the data into an empty SDF file. I then index and copy it to the device.

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I divide tables for a faster search?

    I would be curious if some "magic row count" causes the explosive speed slowdown.

    If you want to remain using SQL CE you could test this out by timing access with 1,000 rows, then 10,000 rows and then 100,000 rows. Run lots of searches against these different row counts so you have a good sample.

    If the timing does no go up in direct relation to the "increase in row counts" then you could be witnessing a glass ceiling of sorts that SQL CE on that device has.

    If that is the case then you might have an argument for cutting up the data into distinct tables - keeping the "count" below the max you discover as evil.

    *** 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

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    I was thinking of doing that with the data but someone replied saying that would not speed it up which I do not know why. I would not even use an index then.

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I divide tables for a faster search?

    Ok - the index is required so that a BINARY SEACH will work - which is what an INDEX SEEK is.

    In this case I would highly recommend against a CLUSTERED index - as that increases the SIZE of the INDEX and size increase will yield more INDEX LEVELS.

    So even if you cut if down into "segments" - you still need an INDEX for SEEK speed.

    If you want to go with TEXT files - already sorted - then you would need to develop your own BINARY search code.

    *** 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

  17. #17
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Can I divide tables for a faster search?

    Quote Originally Posted by szlamany View Post
    If you want to go with TEXT files - already sorted - then you would need to develop your own BINARY search code.
    That would probably be even slower...

    What about the hardware the SQL Server database is installed on? What's the hard-drive, CPU, RAM (how much RAM, and how many MHz) speed?

  18. #18

    Thread Starter
    Frenzied Member
    Join Date
    Oct 2000
    Posts
    1,463

    Re: Can I divide tables for a faster search?

    Its using the SQL Compact 3.5 on the device. There is no outside server it is connecting to.

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Can I divide tables for a faster search?

    I've abandoned SQL for my own binary search logic recently - and it certainly is not slower.

    The point of doing it yourself is you can attack the data directly and immediately.

    *** 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

Posting Permissions

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



Click Here to Expand Forum to Full Width