ORDER BY issue with Interbase XE SQL Server
-
Dear Experts I have a more or less complex query in my database which returns in average about 50 to 100 rows from a table let's say TBL_A containing about 1 million rows (this with left join to another table containg about 10 million records). The query itself returns the result in about 0.03 seconds which is pretty much nice I think. The amount of data per row is much less than 1KB. But as soon I add an "ORDER BY TBL_A.ID" the query needs 1.5 seconds; Where TBL_A.ID is the indexed primary key, integer. I assume the "query compiler" does try to use the index on TBL_A.ID to perform sorting and thats why the whole PLAN becomes inefficient. Question: Is there a way to tell the SQL Server not to use "TBL_A.ID index" for sorting and therfore force an "in Memory sorting"? Any hint is appreciated, also if it is MS SQL related, I think I can transform it by myself to Interbase. Note: A work around of course would be that I stay without ORDER BY in the query and let the client sort the result, but I don't like to do this. Thank you very much in advance for your hints.
-
Dear Experts I have a more or less complex query in my database which returns in average about 50 to 100 rows from a table let's say TBL_A containing about 1 million rows (this with left join to another table containg about 10 million records). The query itself returns the result in about 0.03 seconds which is pretty much nice I think. The amount of data per row is much less than 1KB. But as soon I add an "ORDER BY TBL_A.ID" the query needs 1.5 seconds; Where TBL_A.ID is the indexed primary key, integer. I assume the "query compiler" does try to use the index on TBL_A.ID to perform sorting and thats why the whole PLAN becomes inefficient. Question: Is there a way to tell the SQL Server not to use "TBL_A.ID index" for sorting and therfore force an "in Memory sorting"? Any hint is appreciated, also if it is MS SQL related, I think I can transform it by myself to Interbase. Note: A work around of course would be that I stay without ORDER BY in the query and let the client sort the result, but I don't like to do this. Thank you very much in advance for your hints.
0x01AA wrote:
The query itself returns the result in about 0.03 seconds which is pretty much nice I think.
If you insert into a new (temp) table and sort those 50-100 rows after the query has executed with a new command, how much time would that take?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
0x01AA wrote:
The query itself returns the result in about 0.03 seconds which is pretty much nice I think.
If you insert into a new (temp) table and sort those 50-100 rows after the query has executed with a new command, how much time would that take?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Thank you very much for your reply. Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL. And unfortunatelly there is nothing like like CTE in Interbase (at least what I know) :^) Thanks again. [Edit] :lol: Visited your Profile and read "I'm a Delphi-convert", so then I assume you know interbase. I'm on the way to become hopefully a "Borland c++" convert. :laugh:
-
Thank you very much for your reply. Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL. And unfortunatelly there is nothing like like CTE in Interbase (at least what I know) :^) Thanks again. [Edit] :lol: Visited your Profile and read "I'm a Delphi-convert", so then I assume you know interbase. I'm on the way to become hopefully a "Borland c++" convert. :laugh:
0x01AA wrote:
Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL.
Wrap it in a transaction. Without committing, there is no temp table; should isolate the operation. Would work best as a stored procedure :) You're welcome ofc :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)