SQLite query big table

FdamraFdamra JOMember ✭✭
  • i have big table contains 80,000 record , and i faced strange behavior when i query the data from this table.
  • when i query on columns some work and some not working
  • some cloumns bring date when i filter depend on it
  • and some columns didn't bring data when i filter depend on it.
  • i used linq and normal sqlite query and both have same issue.

any suggestions ?

Tagged:

Answers

  • kentuckerkentucker USMember ✭✭✭✭✭

    Could you please be more specific when asking a question like this? What columns are in the table and what are their data types. Are you joining data from a few tables?

  • FdamraFdamra JOMember ✭✭

    @kentucker said:
    Could you please be more specific when asking a question like this? What columns are in the table and what are their data types. Are you joining data from a few tables?

    when i query these columns (ItemCodeSeq , PID ) it is workinf fine, but when i query use BranchNo it is not getting the result.

    this is the class

    [Table("MOB_Items")]
    public class SQLiteItems
    {
        [PrimaryKey, AutoIncrement, Ignore]
        public int ID { get; set; }
       public int ItemCodeSeq { get; set; }
        public int BranchNo { get; set; }
        public int PID { get; set; }
        public int UnitID { get; set; }
        public string Unit1Code { get; set; }
        public string ItemNameDescription { get; set; }
        public double PriceVal { get; set; }
        public double Tax { get; set; }
        public string PriceNameA { get; set; }
        public string UnitDescription { get; set; }
        public string StoreDescription { get; set; }
        public string FamCode { get; set; }
        public string FamDesc { get; set; }
        public int ClassId { get; set; }
        public string ClassDescription { get; set; }
        public double QtyOnHand { get; set; }
        public string LastUpdateDate { get; set; }
        public bool ckpricenotax { get; set; }
    }
    

    and this is the sqlite table on sqlite:

    CREATE TABLE [MOB_Items] (
    [ID] INTEGER NOT NULL
    , [ItemCodeSeq] bigint NOT NULL
    , [BranchNo] bigint NOT NULL
    , [PID] bigint NOT NULL
    , [Unit1Code] nvarchar(255) NOT NULL
    , [ItemNameDescription] nvarchar(255) NULL
    , [PriceVal] float NULL
    , [Tax] real NULL
    , [PriceNameA] nvarchar(100) NOT NULL
    , [UnitID] smallint NOT NULL
    , [UnitDescription] nvarchar(250) NULL
    , [StoreDescription] nvarchar(255) NULL
    , [FamCode] nvarchar(20) NULL
    , [FamDesc] nvarchar(200) NULL
    , [ClassId] bigint NOT NULL
    , [ClassDescription] nvarchar(250) NULL
    , [QtyOnHand] float NULL
    , [LastUpdateDate] datetime NULL
    , [ckpricenotax] bit NOT NULL
    , CONSTRAINT [sqlite_master_PK_MOB_Items] PRIMARY KEY ([ID])
    );

  • JamesLaveryJamesLavery GBBeta, University ✭✭✭✭✭
    This is more probably a database problem than mobile/sqlite specific.

    What is the distribution of BranchNo in the table? Is it unique (from it name, I doubt it)? If not how many records roughly have a particular BranchNo? You could be returning too many records in the query.

    However - you definitely need an index on BranchNo in order to query it efficiently. This can be done with an [Index] attribute on the property. You probably need an index on ItemCodSeq if you are querying that on its own.

    I'm suspicious about the [Ignore] attribute on I'D. To me this means that Sqlite won't store it in the table - which is at odds with your saying that you can query on it successfully.
  • FdamraFdamra JOMember ✭✭

    @JamesLavery said:
    This is more probably a database problem than mobile/sqlite specific.

    What is the distribution of BranchNo in the table? Is it unique (from it name, I doubt it)? If not how many records roughly have a particular BranchNo? You could be returning too many records in the query.

    However - you definitely need an index on BranchNo in order to query it efficiently. This can be done with an [Index] attribute on the property. You probably need an index on ItemCodSeq if you are querying that on its own.

    I'm suspicious about the [Ignore] attribute on I'D. To me this means that Sqlite won't store it in the table - which is at odds with your saying that you can query on it successfully.

    my friend thanks for reply just to clarify some points

    • i still have strange behavior.
    • other strange issue that when i query the table and get the result i found such as these fields "ClassId and branchNo" have a data but wen i return it back by function, and store it inside the list collection of this class, i found it became zero.
    • (someone advised me to update the packages) but i still not every thing fine
    • last note I don't query on "ID", but i query on "PID" the 4'th column and its working fine.
    • also the ignore attribute i put it on "ID" not on ItemCodSeq

    if you have any suggestion please don't hesitate to post it.

  • JamesLaveryJamesLavery GBBeta, University ✭✭✭✭✭

    Are you saying that when you query the table with you data layer, the fields are there, but when you look at the populated list of data from the query, it's missing?

    I think you need to post some of your code - there must be something strange with how you are populating your list.

    Also, really do think you need to add indexes to these fields - see my amended code below:

    [Table("MOB_Items")]
    public class SQLiteItems
    {
        [PrimaryKey, AutoIncrement, Unique]
        public int ID { get; set; }
        [Indexed]
        public int ItemCodeSeq { get; set; }
        public int BranchNo { get; set; }
        [Indexed]
        public int PID { get; set; }
        public int UnitID { get; set; }
        public string Unit1Code { get; set; }
        public string ItemNameDescription { get; set; }
        public double PriceVal { get; set; }
        public double Tax { get; set; }
        public string PriceNameA { get; set; }
        public string UnitDescription { get; set; }
        public string StoreDescription { get; set; }
        public string FamCode { get; set; }
        public string FamDesc { get; set; }
        public int ClassId { get; set; }
        public string ClassDescription { get; set; }
        public double QtyOnHand { get; set; }
        public string LastUpdateDate { get; set; }
        public bool ckpricenotax { get; set; }
    }
    
Sign In or Register to comment.