Sorting data is time consuming, a physical law is involved. At best it
is an nlog(n) process. Sorting a result set or sorting the keys to
build an index invokes the nlog(n) timing.
If you cannot tolerate time spent sorting, you need to redesign your
database to avoid sorts, or to maintain reference data in sorted
sequence via an index and use that sequence repeatedly.
This is all IT101, nothing to do with Sqlite.
Manzoor Ilahi wrote:
Thanks shiming
we have tried this method as u suggested , but creating an index on a 5
million records
take a long time, nearly 4 seconds.
And in our case the Database contain 5 Million to 30 or 50 million
records. so if I try to create additional indices on these tables it
takes too much time.
any other suggestions if u have please.
Best regards,
Manzoor Ilahi
//----------------------------------------------------------------------------------------------------------------------------------
You can try creating two additional indexes, one on hvh5m.Column4 and
the other on hvh5m.Column8. They should speed things up.
Best regards,
He Shiming
From: "He Shiming" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: <sqlite-users@sqlite.org>
Subject: Re: [sqlite] SQLite Order BY Date: Mon, 9 Oct 2006 11:59:46
+0800
//----------------------------------------------------------------------------------------------------------------------------------
Dar All,
Can someone guide me, why the ORDER BY in SQLite is too slow. we
observed that if the change in data values is small then ORDER BY
works better . but if the change is big then the performance is very
slow. Even sometime I can not finish a query execution, and wait and
may be sometime need to kill the process.
also another point that i observed is , when ..order by fieldName ,
if fieldName is varchar,the excutiion is faster ,and if fieldName is
float,the execution can't be finished!
Any Idea Please
thanks
Manzoor Ilahi
Queries that we tested and the Schema is given as under.
//----------------------------------------------------------------------------------
select * from hvh5m,itm where hvh5m.Column4=itm.Icol1 order by Column8
this SQl can't be finished
//----------------------------------------------------------------------------------
select * from hvh5m,itm where hvh5m.column4=itm.Icol1 order by
itm.Icol1;
this sql can be finished!
//----------------------------------------------------------------------------------
hvh5m: column1 varchar(8 );
column2 varchar(4);
Column3 varchar(2);
Column4 varchar(4)£»
column5 varchar(7);
column6 varchar(8 );
column7 varchar(1);
Column8 float(10£¬2);
itm£º Icol1 varchar(4);
itemname varchar(20);
Icol3 varchar(1);
Icol4 varchar(1);
Icol5 varchar(1);
Icol6 varchar(1);
Icol7 varchar(1);
Icol8 varchar(1);
Icol 9 varchar(1);
Icol 10 varchar(1);
Icol 11 varchar(1);
Icol 12 varchar(1);
Icol 13 varchar(1);
Icol 14 varchar(1);
Icol 15 varchar(1);
Icol 16 varchar(1);
There is one index on itm.Icol1.
You can try creating two additional indexes, one on hvh5m.Column4 and
the other on hvh5m.Column8. They should speed things up.
Best regards,
He Shiming
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
_________________________________________________________________
Be seen and heard with Windows Live Messenger and Microsoft LifeCams
http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http://www.microsoft.com/hardware/digitalcommunication/default.mspx?locale=en-us&source=hmtagline
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
