Re: [sqlite] getting only count of records
Wilfried Mestdagh a écrit : Hi Guillaume, if restricting in the where clause on VehicleID, then you should create an index on VehicleID. I' not sure I understeand correct. Does this mean that if I uses 'where' clauses it is not needed to make indexes and it make index itself when needed ? No no ! :) it just means that if you plan to use regularly some columns in your where clauses, then you should create indexes on those columns. By now I have made an index on every 'search thing' I use in where clause. Since I dont find instructions to say to the database which index to use, I assume it uses the right ones itself. But when it autocreate indexes... hmm should be nice thing :) No. SQLite nor any other DBMS autocreates indexes. beware of creating to many indexes ! it can slow down your application ! for example, if, for a table, you always access it by using where clauses like : 1. " where VehicleID='anID' " or 2. " where VehicleID='anId' and VehicleOwnerID='anOwnerID' " then you can create one index based on the two columns VehicleID and VehicleOwnerID. If some columns are to be used always together within your queries, then create one index on those columns. HTH, Best regards, -- Guillaume MAISON - [EMAIL PROTECTED] 83, Cours Victor Hugo 47000 AGEN Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50 e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com
Re[2]: [sqlite] getting only count of records
Hi Guillaume, > if restricting in the where clause on VehicleID, then you should create > an index on VehicleID. I' not sure I understeand correct. Does this mean that if I uses 'where' clauses it is not needed to make indexes and it make index itself when needed ? By now I have made an index on every 'search thing' I use in where clause. Since I dont find instructions to say to the database which index to use, I assume it uses the right ones itself. But when it autocreate indexes... hmm should be nice thing :) Unless of course I misinterprete :( --- Rgds, Wilfried http://www.mestdagh.biz
RE: [sqlite] getting only count of records
>Is this the right way to do or is there a better way ? In addition to the comments others have made, I would suggest you use: 'select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID) 'count(*)' is a special case for the count function that returns "count of records". In terms of performance, this will have no measurable benefit with SQLite. However other database's can often return results substantially quicker (particularly when there is no 'where' clause) than using 'count()'. Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] getting only count of records
Wilfried Mestdagh wrote: > > Hi Arjen, > > AM> No, they will probably not all do the same internally: the result > AM> may be the same, but the "virtual machine" that runs the SQL statements > AM> will very probably do different things - this depends on optimisation > AM> and so on. But for most of us it is mainly the result that counts > AM> (and the time it takes for that result) > > But what should then be the best one in speed ? > As others have replied, the version(s) where you use count() return(s) only one record. And that can be seen from the sQL statement all by itself. I do not think things can be faster than that: only one record that will actually be returned to the calling program. Regards, Arjen
Re: [sqlite] getting only count of records
Wilfried Mestdagh a écrit : AM> No, they will probably not all do the same internally: the result AM> may be the same, but the "virtual machine" that runs the SQL statements AM> will very probably do different things - this depends on optimisation AM> and so on. But for most of us it is mainly the result that counts AM> (and the time it takes for that result) But what should then be the best one in speed ? well, in your SQL query, if you don't mind about VehicleID, there's nothing to care about speed as SQLite will do a full table scan (and will find its way to get the fastest full table scan :) ) if restricting in the where clause on VehicleID, then you should create an index on VehicleID. HTH, -- Guillaume MAISON - [EMAIL PROTECTED] 83, Cours Victor Hugo 47000 AGEN Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50 e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com
Re[2]: [sqlite] getting only count of records
Hi Arjen, AM> No, they will probably not all do the same internally: the result AM> may be the same, but the "virtual machine" that runs the SQL statements AM> will very probably do different things - this depends on optimisation AM> and so on. But for most of us it is mainly the result that counts AM> (and the time it takes for that result) But what should then be the best one in speed ? --- Rgds, Wilfried http://www.mestdagh.biz
Re: [sqlite] getting only count of records
Wilfried Mestdagh wrote: > > Hi Guillaume and Martin, > > Thanks for quick responce. Studying the count() function if I use the > method of Guillaume I dont need the 'where' clause: > > 'select count(VehicleID) from tx' > > But I assume all 3 methods will do internal exact the same. Result is > also 1 field with the count in it. > No, they will probably not all do the same internally: the result may be the same, but the "virtual machine" that runs the SQL statements will very probably do different things - this depends on optimisation and so on. But for most of us it is mainly the result that counts (and the time it takes for that result) Regards, Arjen
Re: [sqlite] getting only count of records
Hi Guillaume and Martin, Thanks for quick responce. Studying the count() function if I use the method of Guillaume I dont need the 'where' clause: 'select count(VehicleID) from tx' But I assume all 3 methods will do internal exact the same. Result is also 1 field with the count in it. --- Mvg, Wilfried http://www.mestdagh.biz
Re: [sqlite] getting only count of records
Wilfried Mestdagh a écrit : Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? Hi Wilfried, a better request would be : select count(VehicleID) as CountVehicle from tx where VehicleID = ' + QuotedStr(VehicleID) then you get the field "CountVehicle" value. HTH, -- Guillaume MAISON - [EMAIL PROTECTED] 83, Cours Victor Hugo 47000 AGEN Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50 e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com
Re: [sqlite] getting only count of records
Hello Wilfried, your query will have as many result - Rows as meet your where-clause. A Better way will be select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID) This will have one result row with the number of records in the first (and only) column. Martin Wilfried Mestdagh schrieb: Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? --- Mvg, Wilfried http://www.mestdagh.biz
[sqlite] getting only count of records
Hello, I want to have only the count of records for a particular 'where clause', need no fields. It works using null as argument: 'select null from tx where VehicleID = ' + QuotedStr(VehicleID) Is this the right way to do or is there a better way ? --- Mvg, Wilfried http://www.mestdagh.biz
