Odtnhj

I have two tables with the same data structure except that one has a primary key on a column and the other don't.

The thing is, query on the table with primary key run in average 6x to 10x slower than the one without primary key..

Table without PK

CREATE TABLE `ULRCON01` 
(
`CONSTAC` VARCHAR ( 7 ) NOT NULL,
`DATCRED` VARCHAR ( 8 ),
`DATMAJD` VARCHAR ( 8 ),
`MONTANM` DECIMAL ( 19 ),
`CONSTLC` VARCHAR ( 10 ),
`CONSTLL` VARCHAR ( 35 ),
);

Table with PK

CREATE TABLE `ULRCON01` 
(
`CONSTAC` VARCHAR ( 7 ) NOT NULL,
`DATCRED` VARCHAR ( 8 ),
`DATMAJD` VARCHAR ( 8 ),
`MONTANM` DECIMAL ( 19 ),
`CONSTLC` VARCHAR ( 10 ),
`CONSTLL` VARCHAR ( 35 ),
PRIMARY KEY(`CONSTAC`)
);

The query

SELECT CONSTLC FROM ULRCON01 WHERE CONSTAC LIKE 'SIRCA00'

And the results..

// PK on CONSTAC
Request "SELECT CONSTLC FROM ULRCON01 WHERE CONSTAC LIKE 'SIRCA00'" executed in 10ms

// No PK
Request "SELECT CONSTLC FROM ULRCON01 WHERE CONSTAC LIKE 'SIRCA00'" executed in 1ms

I just don't understand.. Why would primary key slow down a simple select where query.. Any ideas ?

Ok, so after more investigation, it cames from the way we are using SQLiteDataReader..
We specified

using (var myReader = sqlCmd.ExecuteReader(CommandBehavior.KeyInfo))

And it's quite costfull (also that's why it runs faster without primary keys..). So now we use

using (var myReader = sqlCmd.ExecuteReader(CommandBehavior.Default))

Which speed up the request to 2ms instead of 10 !