Re: [sqlite] ORDER BY Performance on 30,000 records

2007-12-04 Thread Ofir Neuman 

Thanks, now it takes only few ms  : )

- Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Sunday, December 02, 2007 7:52 PM
Subject: Re: [sqlite] ORDER BY Performance on 30,000 records




On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote:


Hi All,

I have some performance problem when adding ORDER BY to my query,  hope 
you

can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.


Drop the index on ParentID and replace it with this:

   CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID);

Then queries of the form

   SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate;

will be very fast.



Current number of records in table: 40,000

My query is very simple:
SELECT ID FROM Table1 WHERE ParentID = '{---}'  ORDER BY
ModifiedDate

According to the data in my table this query should return 30,000 
records.


While using ORDER BY it takes 3-4 SEC to retrieve the query result, 
without

the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.


D. Richard Hipp
[EMAIL PROTECTED]




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ORDER BY Performance on 30,000 records

2007-12-02 Thread D. Richard Hipp 


On Dec 2, 2007, at 12:01 PM, Ofir Neuman wrote:


Hi All,

I have some performance problem when adding ORDER BY to my query,  
hope you

can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.


Drop the index on ParentID and replace it with this:

   CREATE INDEX idx2 ON table1(ParentID, ModifiedDate, ID);

Then queries of the form

   SELECT id FROM table1 WHERE parentid=? ORDER BY modifieddate;

will be very fast.



Current number of records in table: 40,000

My query is very simple:
SELECT ID FROM Table1 WHERE ParentID = '{---}'  
ORDER BY

ModifiedDate

According to the data in my table this query should return 30,000  
records.


While using ORDER BY it takes 3-4 SEC to retrieve the query result,  
without

the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.


D. Richard Hipp
[EMAIL PROTECTED]




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ORDER BY Performance on 30,000 records

2007-12-02 Thread John Stanton 

Ofir Neuman wrote:

Hi All,

I have some performance problem when adding ORDER BY to my query, hope you
can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.
Current number of records in table: 40,000

My query is very simple:
SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY
ModifiedDate

According to the data in my table this query should return 30,000 records.

While using ORDER BY it takes 3-4 SEC to retrieve the query result, without
the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.

You are not doing anything wrong, it just takes time to sort the result 
set since you have no index on the sort key.  When you indexed the sort 
column you hit an Sqlite restriction that it only uses one index on a query.


You might experiment by using an index combining ParentId and ModifiedDate.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ORDER BY Performance on 30,000 records

2007-12-02 Thread Jonas Sandman 
Sorting the returned 30 000 records maybe takes 3-4 seconds?

/Jonas

On 12/2/07, Ofir Neuman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have some performance problem when adding ORDER BY to my query, hope you
> can help me speed things up.
>
> This is my table:
>
> TABLE1
> {
>  ID TEXT
>  ParentID TEXT
>  ModifiedDate INTEGER
> }
>
> ID is the PK of the table and i also have an index on ParentID.
> Current number of records in table: 40,000
>
> My query is very simple:
> SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY
> ModifiedDate
>
> According to the data in my table this query should return 30,000 records.
>
> While using ORDER BY it takes 3-4 SEC to retrieve the query result, without
> the ORDER BY it take something like 30 ms.
>
> Tried to index also 'ModifiedDate' but it didn't help.
>
> What am i doing wrong?
>
> Thanks,
> Ofir Neuman.
>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] ORDER BY Performance on 30,000 records

2007-12-02 Thread Ofir Neuman 
Hi All,

I have some performance problem when adding ORDER BY to my query, hope you
can help me speed things up.

This is my table:

TABLE1
{
  ID TEXT
  ParentID TEXT
  ModifiedDate INTEGER
}

ID is the PK of the table and i also have an index on ParentID.
Current number of records in table: 40,000

My query is very simple:
SELECT ID FROM Table1 WHERE ParentID = '{---}' ORDER BY
ModifiedDate

According to the data in my table this query should return 30,000 records.

While using ORDER BY it takes 3-4 SEC to retrieve the query result, without
the ORDER BY it take something like 30 ms.

Tried to index also 'ModifiedDate' but it didn't help.

What am i doing wrong?

Thanks,
Ofir Neuman.