[firebird-support] Performance question
Hello, the situation: state for each element (can be 0 or 1 for Example) = 1001000.. Is it faster to insert this in a varchar-field or is it better to store it in zwenty smallint separate database fields? I would like to analyze it later. (read substring or each field) Thanks in advance Best regards Olaf
AW: [firebird-support] Performance Question
> Hello everybody, > > we plan a web-Application with a firebird database. Now I have two > options to prepare the data for the web-client (HTML.). > > Option A - a view - returns Data from all Users and the client selects > itself > > Option B - a stored procedure with input parameters, the client gets > only the matching records. > > What should be the better option? < A is usually faster and easier to modify to match new requirements. < B is probably safer, provided that the user doesn't have credentials of the database owner. < Personally, I go with B, because I am way more concerned about the security than the speed. But that's just my personal deformation, it may not be valid for your case. < Josef Thank you! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links
Re: [firebird-support] Performance Question
> Hello everybody, > > we plan a web-Application with a firebird database. Now I have two > options to prepare the data for the web-client (HTML…). > > Option A – a view – returns Data from all Users and the client selects > itself > > Option B – a stored procedure with input parameters, the client gets > only the matching records. > > What should be the better option? A is usually faster and easier to modify to match new requirements. B is probably safer, provided that the user doesn't have credentials of the database owner. Personally, I go with B, because I am way more concerned about the security than the speed. But that's just my personal deformation, it may not be valid for your case. Josef ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Performance Question
Hello everybody, we plan a web-Application with a firebird database. Now I have two options to prepare the data for the web-client (HTML.). Option A - a view - returns Data from all Users and the client selects itself Option B - a stored procedure with input parameters, the client gets only the matching records. What should be the better option? Thank you and best regards. Olaf
Re: [firebird-support] performance question
-- Original Message --- From: "Olaf Kluge" > Now I have two ideas. I create a table with all fields (separate for each > information) and an import stored procedure fill this table with information > from the long string. (line_number = substring(:str_in from 1 for 4)) etc. > > String: 1234A1E2WEWE3432334 > > Into Field 1 = 1234, Field 2 = A1, Field3.. > > Or > > I made a table with one field and save only the complete string in it. > In a second table I store the information about the field-names and > the start-byte. If the client-visualisation gives me the start-byte, I > can get this information from the one table and disassemble the string > from the other while executing. This method had the advantage, that I > can simply and short change the configuration if the layout of the > string was changed. > > String: 1234A1E2WEWE3432334 into one field and get the information by > executing the stored procedure. --- End of Original Message --- If the layout of the string was changed after you had already received data, it wouldn't make sense to change the mask globally. You'd either need to timestamp your format overlays, or store the mask ID with each historical record, or ... something. If the problem is that the client is wishy-washy, fine -- have a stored procedure do the splitting of the string into the table upon insert, and put all your configurable magic there. I'd go with the first option, just because it can accommodate changes over time as your input format changes, but the underlying idea of the data doesn't (or varies only a little). If some day you start receiving an extra field in the string, great -- you can add a column that will be NULL for historical data, and adjust the input stored procedure or program to match. Dropped from input data? NULL for new records, keep old data intact. Multiple formats, from different sources? Multiple input procedures, same underlying data-structure for storage. Don't worry about row sizes -- I don't think there'll be much cost from having the fields split up vs. not. Not enough to care about, anyway. Relational database file formats have a good deal of "wiggle room" anyway, they're not packed 100% tight, so slight variations in field-terminators, etc. aren't the end of the world. But individual fields (a traditional relational database) do give you the ability to index certain columns efficiently (technically, you could do so even with your other solution, but please don't, it's unnecessarily complex), easily perform computations on them, or create convenient views that give certain users access only to parts of the underlying data. You can also use your resulting database with off-the-shelf reporting and graphing tools, which won't necessarily have any idea how to use your custom SP approach to gathering data. That compatibility (through standardization) is worth something! -Philip
Re: [firebird-support] performance question
On Mon, Apr 16, 2012 at 10:10 AM, Olaf Kluge wrote: > > Now I have two ideas. I create a table with all fields (separate for each > information) and an import stored procedure fill this table with > information > from the long string. (line_number = substring(:str_in from 1 for 4)) etc. > > String: 1234A1E2WEWE3432334 > > Into Field 1 = 1234, Field 2 = A1, Field3.. > > Or > > I made a table with one field and save only the complete string in it. If you're ever going to search on values of the fields, the first approach is the only one that makes sense. Good luck, Ann [Non-text portions of this message have been removed]
AW: [firebird-support] performance question
Hello Set, Best thanks for your detailed reply. Yes, in my table exists fiest a primary key with a integer value generated by a trigger and a generator. All values can be alphanumeric, so that I must save all data in separate character-fields (approx. 100 columns). I could create a separate table for each column (1 to n). That would be too much effort and I don't have all possible combinations. This could be automated too, but how I said, much effort. I will put all information into separate columns. Thank you. Best regards. [Non-text portions of this message have been removed]
RE: [firebird-support] performance question
>Hello, > >from the plc I get every cycle of production a record with some information. >Each record (a car) includes 300 Bytes with alphanumeric characters. For >example char1 to char4 is the line number, char 5 to >char x the model etc. I store the information in a firebird 2.5 cs db. > >The client (visualization) gives me the information, what fields he want to >see. Either byte 1 to x or fieldname xx or field >number x etc. depending on the realization > >Now I have two ideas. I create a table with all fields (separate for each >information) and an import stored procedure fill this table with information >from the long string. (line_number = >substring(:str_in from 1 for 4)) etc. > >String: 1234A1E2WEWE3432334 > >Into Field 1 = 1234, Field 2 = A1, Field3.. > >Or > >I made a table with one field and save only the complete string in it. In a >second table I store the information about the >field-names and the >start-byte. If the client-visualisation gives me the start-byte, I can get >this information from the one >table and disassemble the string from the other >while executing. This method had the advantage, that I can simply and short >>change the configuration if the layout of the string was changed. > >String: 1234A1E2WEWE3432334 into one field and get the information by >executing the stored procedure. > >What can be better (performance, etc.) First, one thing you probably want even though you don't mention it, is to add an integer field to your table, use that field as your primary key and use a trigger to assign a value to that field. Your second option has several drawbacks. It will normally be slower due to it being difficult to index individual parts of the string, so your queries will typically have to scan the whole table. Another thing that either may not apply or that you may have forgotten to think about, is historical data. Lets say you have already imported the string you mentioned above: String: 1234A1E2WEWE3432334 A while later you import your line number 1, forcing you to change the record structure: 1B2E3WEWE4543445 Now, you have gotten yourself two problems: First, for lines 0001-, you might have to substring from position 7, whereas for lines >=1, that would be position 8. Secondly, if you do WHERE SUBSTRING(EntireString from 1 for 4) BETWEEN '1000' AND '1099' that would include line 1. Both of these are possible to overcome, the latter a bit simpler than the first, but they add complexity for no gain and as such it would be better to avoid them in the first place. The simplest way to do this, is to primarily go for your first suggestion where you both split them as you indicate, but also change their type into integers, dates etc. where appropriate. Those fields that are likely to be searched for (and reasonably selective), should be indexed. Note that there's nothing wrong with a hybrid solution, where you first import into one table like your second solution, and then transfer from this table to another table using your first solution (either through a trigger or through a separate program). If you receive big files, it could also be a possibility to import using an external table, though your description leads me to think that you receive one or a few records at the time and not big files more infrequently. HTH, Set
AW: [firebird-support] performance question
Hi Mark, thank you. I will try the first solution. Best regards. Olaf Your second solution would amount to you creating a database in a database. It also means that on every request Firebird will have to retrieve the string as is, and then split that every time. I assume that using Firebird as a real database and storing the data in separate fields will yield better performance: you only need to split the data on storage time, not on each load. But if you want to know for certain: try both approaches and measure it, but then you could just as well use a flatfile to store this data instead of a database. Mark [Non-text portions of this message have been removed]
Re: [firebird-support] performance question
On Mon, 16 Apr 2012 16:10:21 +0200, "Olaf Kluge" wrote: > Now I have two ideas. I create a table with all fields (separate for each > information) and an import stored procedure fill this table with > information > from the long string. (line_number = substring(:str_in from 1 for 4)) etc. > > String: 1234A1E2WEWE3432334 > > Into Field 1 = 1234, Field 2 = A1, Field3.. > > Or > > I made a table with one field and save only the complete string in it. In a > second table I store the information about the field-names and the > start-byte. If the client-visualisation gives me the start-byte, I can get > this information from the one table and disassemble the string from the > other while executing. This method had the advantage, that I can simply and > short change the configuration if the layout of the string was changed. > > String: 1234A1E2WEWE3432334 into one field and get the information by > executing the stored procedure. > > What can be better (performance, etc.) Your second solution would amount to you creating a database in a database. It also means that on every request Firebird will have to retrieve the string as is, and then split that every time. I assume that using Firebird as a real database and storing the data in separate fields will yield better performance: you only need to split the data on storage time, not on each load. But if you want to know for certain: try both approaches and measure it, but then you could just as well use a flatfile to store this data instead of a database. Mark
[firebird-support] performance question
Hello, from the plc I get every cycle of production a record with some information. Each record (a car) includes 300 Bytes with alphanumeric characters. For example char1 to char4 is the line number, char 5 to char x the model etc. I store the information in a firebird 2.5 cs db. The client (visualization) gives me the information, what fields he want to see. Either byte 1 to x or fieldname xx or field number x etc. depending on the realization Now I have two ideas. I create a table with all fields (separate for each information) and an import stored procedure fill this table with information from the long string. (line_number = substring(:str_in from 1 for 4)) etc. String: 1234A1E2WEWE3432334 Into Field 1 = 1234, Field 2 = A1, Field3.. Or I made a table with one field and save only the complete string in it. In a second table I store the information about the field-names and the start-byte. If the client-visualisation gives me the start-byte, I can get this information from the one table and disassemble the string from the other while executing. This method had the advantage, that I can simply and short change the configuration if the layout of the string was changed. String: 1234A1E2WEWE3432334 into one field and get the information by executing the stored procedure. What can be better (performance, etc.) Thank you very much. Best regards. Olaf [Non-text portions of this message have been removed]
