Geek Answers Handbook

Selecting a large number of rows using a primary key

I need to select a large number of rows from an even larger table, which is entered in the auto-increment identification column. I have primary key values ​​for each of the rows that I am trying to select, but it can be very large.

Often, but not always, the selected rows are contiguous, so I implemented a mechanism that converts the selection into a set of range sentences for all records that are grouped together ([PrimaryKey] BETWEEN 151 AND 217) and a backup method that selects all isolated records with a sentence IN

In the end, I get something like this

SELECT * FROM Table WHERE ([PrimaryKey] BETWEEN 151 AND 217) OR ([PrimaryKey] BETWEEN 314 AND 378) OR ...
OR [PrimaryKey] IN (1,3,7,14,147...)

This is great for cases where I have mostly large ranges, but breaks when the request grows. I just came across a degenerate case when I had a large number of “pairs” of records that generated BETWEEN statements for two records at a time, which took more than 15 minutes, trying to describe the execution plan before I gave up.

The first thing that comes to mind is that I can change the threshold when I start generating ranges, not individual values, than something larger than 2 (maybe 10), but I was wondering if there is a better solution there is.

+5
source share
6 answers

Temp , , temp. , .

+7

. , , .

WHERE, , .

.

, , IN , . . .

, . , UNION WHERE, recorset JOIN, BETWEEN , BETWEEN .

. SQL Server , T-SQL, ; . , BETWEEN.

, , . , .

, , . ; -
- ( )
- ( ?)

JOIN BETWEEN, UNION .., .

+2

ocdecio, tempory, . ORs UNION:

SELECT * FROM Table WHERE [PrimaryKey] BETWEEN 151 AND 217
UNION
SELECT * FROM Table WHERE [PrimaryKey] BETWEEN 314 AND 378
UNION
...
UNION
SELECT * FROM Table WHERE [PrimaryKey] IN (1,3,7,14,147...)
+1

, . , . , SQL .

, XML. , XML, XML. , XML :

<a>
  <b>1</b>
  <b>3</b>
  <b>7</b>
  <b>14</b>
  <b>147</b>
</a>

( "a" "b" ), SQL-. "b" :

declare @x xml
set @x = '<a><b>1</b><b>3</b><b>7</b><b>14</b><b>147</b></a>'
select t.item.value('.', 'int') from @x.nodes('//a/b') as t(item)

, XML , . , . XML, , :

select * from MyTable where ID in 
  (select t.item.value('.', 'int') from @x.nodes('//a/b') as t(item))


;with cte as
  (select ID = t.item.value('.', 'int') from @x.nodes('//a/b') as t(item)) 
select * from MyTable inner join cte on MyTable.ID = cte.ID

, , . , JOIN . , XML :

create procedure MyProc @x xml as
begin
  set nocount on
  ;with cte as
    (select ID = t.item.value('.', 'int') from @x.nodes('//a/b') as t(item)) 
  select * from MyTable inner join cte on Table.ID = cte.ID
end

:

exec MyProc '<a><b>1</b><b>3</b><b>7</b><b>14</b><b>147</b></a>'

, XML- . XML , , SQL, XML. :

create xml schema collection MyInputSchema as
  '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="a">
      <xsd:complexType>
        <xsd:complexContent>
          <xsd:restriction base="xsd:anyType">
            <xsd:sequence>
              <xsd:element name="b" type="xsd:integer" maxOccurs="unbounded" />
            </xsd:sequence>
          </xsd:restriction>
        </xsd:complexContent>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>'

:

create procedure MyProc @x xml(MyInputSchema) as
begin
  set nocount on
  ;with cte as
    (select ID = t.item.value('.', 'int') from @x.nodes('//a/b') as t(item)) 
  select * from MyTable inner join cte on Table.ID = cte.ID
end

XML- 43016 SQL- . 1000 10 10 000 . 72 . , .

. SQL 2005 2008 .

0

? , " " - - , , ? , , , .

0

- , . ? ,

SELECT * FROM Table WHERE [PrimaryKey] in
(
    SELECT PrimaryKey from SomeOtherTable where Condition = 'met'
)

,

SELECT * FROM Table WHERE condition = 'met'

, (, ), , , IN.

By the way - sorry, if I teach someone to suck eggs, it's just that my experience was that sometimes the simplest solutions are skipped.

0
source

More articles:


All Articles