> -----Original Message----- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: 29 December 2003 23:41 > To: Yogesh Vachhani > Cc: [EMAIL PROTECTED] > Subject: Re: [sqlite] Subject: Re: [sqlite] Let us make SQLite more > powerful > > > On Sat, 2003-12-27 at 07:16, Yogesh Vachhani wrote: > > > Why exactly do you think the JOIN operator is the wrong tool for > > > this? > > > > I feel JOIN operations are taxing on the Process as well as on RAM > > and HDD (in form of temporary files) > > All right then. SQLite doesn't produce temporary files for JOIN, and > your query _can_ be expressed relationally as a JOIN, so it should never > be any faster than a JOIN operation- except due to parsing. Can you > verify that parsing is taking "too long" here? >
My understanding is that logically, by their very nature, joins are always likely to be slower than single table queries - I thought that this was a given downside to normalisation or am I incorrect? Maybe the downside is not pronounced in SQLite but I'm sure that you see it in other RDBMS (if you crank up the results set volume enough). > > > > If you think this is easier to read, then consider creating VIEWs > > > to > > > store intermediate queries. > > > > I have never considered this as I am not a power user of SQLite. > > Sorry...! > > VIEWs are quite normal in other SQL providers. That said, you may want > to become more familiar with SQL before you start suggesting extensions > to it- at the very least, be aware that syntactical additions may very > well be fine, but you will face criticism if your reasoning is flawed. > In SQLite, VIEWs are simply an expedient for expressing your query - there is no performance upside to using them opposed to a fully specified SQL command. > > > > > So one can see that one would not lose anything by using SQLite's > > > > extended storage model (if implemented). Even though the > > > LineItems > > > > information is embedded into the Invoices table, one can still > > > write > > > > a simple query to access it as if it were in an independent > > > table. > > > > > > Backwards compatibility gets lost. > > > API changes so programs need to be altered. > > I did say at the start that we should extend the existing > > functionality so that who are comfortable with the existing on will > > keep on using this one and other can try out the other one! > > > > This not lead to lost in BACKWARD compatibility....?! > > If you have a table which has a value that can return multiple values > you MUST alter the API of the callback function or of the fetch > function. Otherwise how is older code supposed to deal with a table > having multiple values in an attribute? > Why? Most systems will return you the whole column value with delimiter character(s) separating the sub-fields. I think the alterations to the API will not be in the callback at all, but WILL be in the query processor and index engine. We are talking about changing the search engine to delineate certain field values when querying. It's easier for the indexer, which simply creates multiple postings for the same record but with different terms (each sub-field). > > > > That's an idea.... > > > anyway, it can already be done with SQLite by supplying your own > > > function. Additionally, you could add lists like this yourself > > > (pick a terminator, and make a function that indexes) > > > > By the way does anyone know whether SQLite supports User Defined > > Functions? If such a thing is possible then all such functionality > > can be implementd out side SQLite in a separate DLL. What do u all > > think? > > Yes, SQLite supports "user-defined" functions. The Wiki has a great deal > of documentation about this... > > > > Generally speaking, I think that trying to store an unknown number > > > of distinct values in one record column is a bad idea, and it > > > violates level 1 normalization. You really should use a separate > > > table for the phone numbers and/or addresses, one record per > > > instance. > > > > It does not violate some of the normalization rules but then how many > > follow this pracitcally. In fact I have noticed that in practicaly > > implementations many times a developer has to avoid normalization > > rules to implement some pecular requirements of their customers > > I do not think you know what you are talking about. "level 1 > normalization" comes from graph theory and describes a manner in which > every graph can be translated into another graph as utilizing no nested > functions - which are called "level 1 normal forms". > > I don't think the responder quite spoke correctly either. The structure > you desire _can_ be folded down (normalized) into the view normal people > have of SQL: You aren't suggesting that anything be made available that > isn't presently available- but you want to write these systems without > learning SQL, or because you believe giving syntactical hints will make > queries run faster. > > I do think that this reasoning is erroneous. Profile, don't speculate, > and get the very basics of relational calculus down before you decide > SQL is too primitive to support the data structures you need (hint: > there _are_ many structures that map very poorly to SQL. the one you > selected however, maps very _nicely_ to SQL). > I fully understand your point about normalisation and fully understanding issues before pronouncing on them. However, I think that your not seeing the full picture of the usefulness of multi-occurance columns with the simple example mentioned previously. Take a trivial names and address scenario - each person can have multiple addresses, phone numbers, emails, fax numbers. In a normalised world where we have a table for each of these, you soon run into a complicated query if you want to find a person who has a particular address, a particular phone number, a particular email etc. Don't we have to do unions of a number of joins? Whereas, in a multi-occurrence schema it's simply select * from names where address='vghg' and phone='776' and email='jhjh' etc. Databases that support this type of stuff also often support a 'WITH' operator. This allows you to find rows where your criteria is matched in the same occurrence for each column. e.g. If we want to find a person whose address is 'jhgjh' and phone number at that address is '87667' then we do; select * from names where address='jhgjh' with phone='87667' Incidentally, unless your application is expecting/handles more than one person sharing the same address/phone/fax etc. then you are not breaking the main tenet of normalisation with multi-occurrence columns i.e. repetition/duplication. Now, I'm not suggesting that SQLite goes down this road, but it is important to understand these features and not just disregard them out of hand because they don't fit the SQLxx standard or seem to break normalisation. Steve --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
