Dave Murray wrote: > Hi Walter, > > You raise a lot of interesting points. I hadn't realised TQuery could > be slower than TTable on a database like Access. I normally use a lot > of TTable components in this type of application but I've been saying > since it was first designed that a database like SQL Server would be > better so I used TQuery to make it easier to change in future. > > My memory must be worse than I thought, I just checked the size of the > database and its about 100Mb. :) Larger than I thought but still much > smaller than your example. Since the app is only used by 1 person at a > time we moved the database from a server to the local machine several > months ago which helped for a while. I'm not sure of the PC spec but > it could be an issue. It's a fairly recent HP box but our IT dept > aren't known for spending a lot of cash so it won't be a top spec machine. > > The app is used in a manufacturing process, printing rolls of labels > with unique serial numbers. Each roll has 1,000 labels and is created > by a while loop that queries for the next serial number, prints a > label and logs it's details in the db then repeats. I suggested faster > methods than checking against every serial number ever printed before > printing every label but my bosses insisted that we do it this way to > be 100% sure we don't print any duplicates. The while loop is the > problem, the time taken to print a roll has approximately doubled in > just over a year and the only change is the size of the table it > queries. Other queries are taking longer too but the effect is > negligible and not important. The app prints several different rolls > of labels and the query has two different where clauses depending on > which part is being produced. I haven't used parameters but build the > query using TQuery.Sql.Add(), it's two forms are: > > select `SerialNo` from LOG > where `LabelPartNo` = '5205-035' > and `Year` = '06' > and `DateCode` = '4Z' > order by `SerialNo` > > select `SerialNo` from LOG > where `RevFamily` = '1' > and `Year` = '06' > and `DateCode` = '4Z' > order by `SerialNo` > > I'm going to improve the indexes (as per another post) and upgrade the > RAM to begin with. I'll keep the pressure on my boss to upgrade Delphi > so I can switch to ADO and then think about SQL Server Express.
I see your problem. You take a linear logical approach to reach a working solution. It won't matter what DB you use if you use such an approach - performance will suck. What you should do is generate a list of 1000 serial numbers and store them in an array AND associative hash. You can make sure the list is unique by checking each new serial number generated against those in the associative hash. Then, run the first SQL query. For each row in the query, check the associative hash to see if the serial number already exists. If it does, locate it in the array and flag it as not being unique. Repeat for the second query. Once the two queries have been processed, the array contains a unique set of serial numbers to both itself and the database. Run a batch insert to add the serial numbers to the 'LOG' table __BUT__ flag them as having not been used. This allows you to have two programs: One that generates valid serial numbers and one that prints the labels. If you use the two program approach, the program that prints the labels simply has to run the appropriate SQL query for _unused_ serial numbers, run an UPDATE query to flag it as used, and then print the serial number. One way to further improve performance with this approach would be to remove the 'order by' and introduce a surrogate primary key using an 'autonumber'. A 32-bit integer outperforms ASCII string comparisons any day. -- Thomas Hruska CubicleSoft President Ph: 517-803-4197 Safe C++ Design Principles (First Edition) Learn how to write memory leak-free, secure, portable, and user-friendly software. Learn more and view a sample chapter: http://www.CubicleSoft.com/SafeCPPDesign/ ----------------------------------------------------- Home page: http://groups.yahoo.com/group/delphi-en/ To unsubscribe: [EMAIL PROTECTED] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/delphi-en/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
