How to handle this query in a better way??

[JLA]

I'm sorry that JLA have not provided a running ready example of tables to play with.
So since i'm not smart enough, and no time to follow, i passed this, for curiosity, to a computer engineer, that i know very well.
If there is an answer to this, and he will find out the time to look into, i will return here with his answer (if there is one he have to say, to improve the thing). May he will register himself and answer, maybe, since i linked this post to him. Who know!?

Thank You. Test table and test data here

viewtopic.php?p=15618146#p15618146

[axe70]

Ok, JLA, i will find out the time to set it up in short as i can.
Let see before what my nephew say, when and if he will put eyes into ...

[JLA]

Ok, JLA, i will find out the time to set it up in short as i can.
Let see before what my nephew say, when and if he will put eyes into ...

Thank You

[JLA]

Hi Axe70 - just checking to see if you had a chance to look at this?

[axe70]

Hi there! My little nephew is a bad boy that called me last day, and was really busy, so him assured to take a look before or later... the before or later worry me.

I just did a stupid test by the way, and i see into a table like you provided that contain just 5126 rows, a query like your return
Showing rows 0 - 24 (25 total, Query took 0.0005 sec)
but often it return
Showing rows 0 - 24 (25 total, Query took 0.0006 sec)

while a more basic query like this (to be easier i excluded the LIMIT clause on both sinceit is assumed that it will have same effect on both results):
SELECT item_number,field12 FROM testtable1 USE INDEX (combo1) WHERE field1 = '1201'
return
Showing rows 0 - 24 (25 total, Query took 0.0004 sec)

so maybe if you after manipulate the result like this:

$res0 is the resulting array given by the above query:

Code: Select all

$item_number = array_column($res0,'item_number');
$field12 = array_column($res0,'field12');
$ar = array_combine($field12, $item_number);
ksort($ar);
echo '<pre>';
print_r($ar);

so $ar will contain after another php iteration, extracted data needed for the part
WHERE item_number in (our results inserted here)
the thing could be little faster, since resulting array count is just 50 records?

Anyway about this, looking into your first question, i do not have completely understand why you check for:

Code: Select all

SELECT item_number
FROM TESTTABLE1

then again

Code: Select all

SELECT item_number
FROM TESTTABLE1

is it faster for you re-call a query like this, instead to select all needed row values, into the first query?
but maybe i have not well understand this part.

Partitioning the table could be another solution, since there are millions of records?

[EDITED]

[axe70]

have you think on
Partitioning the table solution?
that's maybe the unique assertion that is really good on previous post together with, why:

Code: Select all

SELECT item_number
FROM TESTTABLE1

then again

Code: Select all

SELECT item_number
FROM TESTTABLE1

are you sure that it is faster of retrieve needed values into same query? (assuming i have understand what you try to do and the explain provided by you on first post). You should have test it, so yes should be the answer?

[JLA]

Hi there! My little nephew is a bad boy that called me last day, and was really busy, so him assured to take a look before or later... the before or later worry me.

I just did a stupid test by the way, and i see into a table like you provided that contain just 5126 rows, a query like your return
Showing rows 0 - 24 (25 total, Query took 0.0005 sec)
but often it return
Showing rows 0 - 24 (25 total, Query took 0.0006 sec)

while a more basic query like this (to be easier i excluded the LIMIT clause on both sinceit is assumed that it will have same effect on both results):
SELECT item_number,field12 FROM testtable1 USE INDEX (combo1) WHERE field1 = '1201'
return
Showing rows 0 - 24 (25 total, Query took 0.0004 sec)

so maybe if you after manipulate the result like this:

$res0 is the resulting array given by the above query:

Code: Select all

$item_number = array_column($res0,'item_number');
$field12 = array_column($res0,'field12');
$ar = array_combine($field12, $item_number);
ksort($ar);
echo '<pre>';
print_r($ar);

so $ar will contain after another php iteration, extracted data needed for the part
WHERE item_number in (our results inserted here)
the thing could be little faster, since resulting array count is just 50 records?

Anyway about this, looking into your first question, i do not have completely understand why you check for:

Code: Select all

SELECT item_number
FROM TESTTABLE1

then again

Code: Select all

SELECT item_number
FROM TESTTABLE1

is it faster for you re-call a query like this, instead to select all needed row values, into the first query?
but maybe i have not well understand this part.

Partitioning the table could be another solution, since there are millions of records?

[EDITED]

Hi Axe50
So we are running two queries on our table that has over 700 million rows

The 1st example query goes like this

Code: Select all

SELECT item_number
FROM TESTTABLE1 USE INDEX (combo1)
WHERE field1 = 1201
ORDER BY field9 DESC,field12 DESC
LIMIT 10000050,50

In this query we are only the 1st 50 items numbers (instead of the contents of the whole row) of items where field1 = 1201 then ordering them by field9 DESC and field12 DESC with a LIMIT of 10000050 This query is using a combination index that provides the shortest query execution time versus using any other index that exists on the table. Still it has to read through a large number or rows which causes an unacceptably long query time especially when there are many of these similar types of queries happening at the same time.

Once we have these 50 item numbers then we run this query to pull the full data for those rows. This query is very fast as it uses the primary index and we have the exact rows we want.

Code: Select all

SELECT * FROM TESTTABLE1
WHERE item_number in (our results inserted here)
ORDER BY field9 DESC, field12 DESC;

[axe70]

Hi! Re-looking to your table, asking myself if it is not containing too much indexes:

When auditing SQL Server databases, one of the more common problems I see is that people add too many indexes to their tables. This has a significant performance impact on your SQL Server. First off, data inserts become slower for every update/delete/insert, because we have to maintain all indexes added for that specific table. Too many indexes also have an impact on your read performance. We all know SQL Server is pretty fast when it comes to reading data out of memory, but with a large amounts of indexes, corresponding data also needs to be stored inside your memory. This is what increases the amount of memory required to have the full table in memory.

and if never could be possible for you, to eliminate something.
If not possible, could be that "tables structure" could be optimized: it should maybe be not a single table containing all data, but different tables, with their indexes?

https://kohera.be/blog/sql-server/sql-s ... y-indexes/